Forum Discussion
Named Range Column Changed
I have a sheet called Total which takes data from 8 monthly sheets. The formula in each cell is =SUMIFS(SeptScore, SeptCat,"Pictorial",SeptName,$A3). It pulls their scores each month and totaling the scores if they have more than one score.
I have created Named Ranges in the 8 monthly sheets (Sept, Oct..). I defined a Name Range - SeptName which is H1:H200, SeptCat which is A1:A200 and SeptScore which is O1:O200. There are a set of these Range Names for each month - OctScore, NovScore, etc.
The problem is that the provider of the data changed the columns on me. So where SeptScore used to be column O it is now column R. So I have to go and re-reference all my Named Ranges. And this will happen in the future.
Is there an easier way to name my ranges that will take care of column references changing? Hopefully this makes sense! Thank you for any assistance.
Using Excel for Mac 2019.
Activate the September sheet
Change the definition of SeptName to
=INDEX($A$2:$Z$100, 0, MATCH("Author's Name", $A$1:$Z$1, 0))
That of SeptCat to
=INDEX($A$2:$Z$100, 0, MATCH("Competition Title", $A$1:$Z$1, 0))
And of SeptScore to
=INDEX($A$2:$Z$100, 0, MATCH("Score", $A$1:$Z$1, 0))
Similar for the other sheets (activate each in turn).
4 Replies
Do the sheets have descriptive headers in row 1, for example "Cat", "Name" and "Score"?
Row 1 in each sheet shows column headers - Cat is "Competition Title", Name is "Author's Name" and Score is "Score". There are many other columns but those are the only that I get data from.
Charlene
Activate the September sheet
Change the definition of SeptName to
=INDEX($A$2:$Z$100, 0, MATCH("Author's Name", $A$1:$Z$1, 0))
That of SeptCat to
=INDEX($A$2:$Z$100, 0, MATCH("Competition Title", $A$1:$Z$1, 0))
And of SeptScore to
=INDEX($A$2:$Z$100, 0, MATCH("Score", $A$1:$Z$1, 0))
Similar for the other sheets (activate each in turn).
