0

I am using a bunch of named ranges in a workbook. Here is the history of how it has changed to make it more dynamic for when they ask me to modify it.

What I want to do is search the A column from 1 to 100 for the label in the A column (that I code into the named range formula) and where it finds it, the named range will know is the row. I know I will still need to update the named range if we ever change the name of a row but I would only need to update one named range as opposed to 75-100 named ranges as I do now whenever we make a change.

Here is the current Named Range that I am using:

=INDIRECT("Input!$C$23:"LastColumn&"$23")

LastColumn is a named range that contains the letter of the last column.

Here is some history on why I do it this way:

When I first created it I referenced the cells directly in formulas (no named range) and had to change all the formulas whenever I added another row, column, etc. Then I switched it to Named References. This fixed it so whenever we added a row, I did not need to change the references at all. However, I still had to change all the references whenever we changed the amount of columns. What I did was make the columns variable, so if we change the amount of columns I don't have to make any changes to the named ranges. Unfortunately, this had the unintended consequences of making it so whenever I add, remove, or move a row I need to update all the references. Now, I have an idea that will make both the rows and the columns variable. So, I can make any change without having to update any references.

I needed to do it this way because most of the time the columns will be empty and I do not know when they will have data in them.

djblois
  • 963
  • 1
  • 17
  • 52
  • 1
    TL;DR. Please read [ask]. Create a simple MCVE, ask a simple question. – Amit Aug 09 '15 at 21:33
  • @Amit, the question is simple - the rest is the reasoning of why I need to do this. I made it easier though to distinguish between the question and the history. – djblois Aug 09 '15 at 21:45
  • What row ALWAYS contains the correct amount of non-blank entries and does it contain numbers or text? Example: a header row would always have entries to the extents of the data matrix while the data rows may or may not contain blanks in the last column. –  Aug 09 '15 at 22:26
  • @Jeeped none. I have no problem with the columns. I need to make the rows dynamic now. The columns already are dynamic. – djblois Aug 09 '15 at 22:30
  • Far be it from me to suggest a better method that would accomplish both tasks in a more efficient manner. Good luck with your project! –  Aug 09 '15 at 22:39
  • @Jeeped, sorry I was not complaining. I thought you were confused with my question. Thank you for trying to help and hope you did not take it offensively because that is not how I meant it. I do not know if I can modify the way I do the columns because no row will always have the correct amount of non-blank entries. – djblois Aug 09 '15 at 22:42

1 Answers1

0

Abandon the volatile INDIRECT function and use the INDEX function for the termination of the range with MATCH locating the last column. The row can be determined by a MATCH on column A to "Label".

As I understand it, you want to locate "Label" in column A and have its location define the row in =INDIRECT("Input!$C$23:"LastColumn&"$23") where 23 currently defines the row.

Abandoning INDIRECT, the Input!$C$23 reference can be changed to:

=INDEX(Input!$C:$C, MATCH("Label", Input!$A:$A, 0))

So all that is left is to find the last column. There must be something on that worksheet that can be used to locate the last column regardless of column deletions or additions but I will just use the last value in the row that contains "Label" in column A.

'this finds the last text value in row 23
=MATCH("zzz", Input!23:23)
'this finds the last number or date value in row 23
=MATCH(1e99, Input!23:23)
'since I do not know whether *the row* (e.g. 23:23) contains text, numbers, dates 
'or a combination of any of those, I will have to double up the formula and add error control
=MAX(IFERROR(MATCH("zzz", Input!23:23), 0), IFERROR(MATCH(1e99, Input!23:23), 0))

You can stitch two INDEX functions together with a colon just as if you were typing C23:Z23.

=INDEX(Input!$C:$C, MATCH("Label", Input!$A:$A, 0)):INDEX(Input!$A:$ZZ,MATCH("Label",Input!$A:$A,0),MAX(IFERROR(MATCH(1E+99, INDEX(Input!$A:$ZZ,MATCH("Label",Input!$A:$A,0),0)),0),IFERROR(MATCH("zzz",INDEX(Input!$A:$ZZ,MATCH("Label",Input!$A:$A,0),0)),0)))

Yes, it looks complicated but a lot of that comes from not knowing whether the row in question contains text or numbers and it does less work than a volatile INDIRECT (which recalculates whenever anything in the entire workbook changes) while remaining up-to-date despite row and column deletion/insertions.

BTW, when using text-as-cell-references within INDIRECT there is no need to use the $ absolute reference indicator. An address-as-text is a text string; it will not change no matter what you do to it short of retyping it. The above method does require absolute cell referencing as it is using actual cell and cell range references.