Named range based on text in column header rather than cell reference?

[scampie]

Is it possible to create a named range based on the text in the column header, rather than a range? I've seen ways of doing it with a macro but I was hoping to find a way to do it autonomously so when my csv downloads occasionally add random columns without informing me, It doesn't take me an hour to sort the mess out!

 

[jasonb75]

If the text in the column header will always be the same then you could do it with an index / match formula to define the range with the header text as the match criteria.

If the name of the named range needs to change with variations to the headers then that would require vba.

 

[scampie]

If the text in the column header will always be the same then you could do it with an index / match formula to define the range with the header text as the match criteria.

If the name of the named range needs to change with variations to the headers then that would require vba.

Thank you. Yes, they will/should remain the same. I'm using downloaded sales data from a few different online market places. I like to handle my own data and don't use third party software. They do however like to change the placement of the columns quite frequently which is a ball ache. I shall try googling those terms you have suggested. Hopefully I can come up with something, thanks. If you know of any posts I can take a look at, that would be fab x

 

[jasonb75]

Assuming that you have a column heading of 'scampie' in row 1, the formula to define the range for rows 2 to 100 would be

=INDEX($2:$100,0,MATCH("scampie",$1:$1,0))

If you need a variable last row then it's not quite as simple, but can still be done, although it would be preferable if there was a fixed column for getting the last row rather than trying to find it from the moving column as part of the definition.

 

[scampie]

Assuming that you have a column heading of 'scampie' in row 1, the formula to define the range for rows 2 to 100 would be

=INDEX($2:$100,0,MATCH("scampie",$1:$1,0))

If you need a variable last row then it's not quite as simple, but can still be done, although it would be preferable if there was a fixed column for getting the last row rather than trying to find it from the moving column as part of the definition.

 

[scampie]

I use a set number of rows per sales channel. Each sales channel has their own sheet which I then output to another coordinated sheet. So i would set each sheet to max sales per day, say 200. Blank cells will not cause an issue will they?

 

[jasonb75]

Blank cells would not be a problem with the formula that I suggested for setting the range.

If you have any formulas in the worksheet where blanks are a problem with the named range set by a cell reference then they would not be fixed by this but it wouldn't create any additional problems.