Potential Use Indirect to create dynamic named range

[greghealey]

I have built a spreadsheet where I export date from a database and paste into Sheet1. I then run a number of formula and graphs etc.

The issue is that the column position change, although the column header obviously doesn't. e.g. Price could be in Column A then next time I export in column B.

I can use the indirect function to search for "Price" see what column then reference in the cell formula but the issue is that when you reference multiple columns like this over multiple rows it becomes very slow workbook.

I was thinking instead to create a dynamically named range, i.e. Formulas>Define Name etc.

I used this formula but I could not get it to work, does anyone have any other ideas or work around?

=INDIRECT("'Sheet1'!"&SUBSTITUTE(ADDRESS(1,MATCH("Price",Sheet1!1:1,0),4),1,"")&"1:"&SUBSTITUTE(ADDRESS(1,MATCH("Price",Sheet1!1:1,0),4),1,"")&"10000"

i.e. if i create the above and call it "Task", then I can simply do =sum(Task) etc.

 

[mrhstn]

You could use INDEX to avoid the problem with your formula being volatile

=SUM(INDEX(Sheet1!A:D, , MATCH("Price", Sheet1!1:1, 0)):INDEX(Sheet1!A:D, MATCH(--REPT(9, 255), Sheet1!A:A), MATCH("Price", Sheet1!1:1, 0)))

HTH

 

[greghealey]

My issue is not the volatility, the issue is that if you do index/match over 10,000 rows and 20 columns before you actually do any of the formulas i.e. simply to get the right column as the starting point excel is not going to cope. Hence why I was looking at initially defining the range.