Extracting cell data to identify a named range

I have a header row in a series of 25 tables. These are named Year 1, Year 2, etc. I also have formulas in each column below these headers that refer to a named range such as RentIncrease1, RentIncrease2, etc.

Is it possible to have the same formula in every cell that can use the header data, such as =C16*(1+RentIncreaseX) where "X" is actually the rightmost 2 characters from the header row? I know I can use Left and Right functions in formulas but can I concatenate "RentIncrease" and the right 2 characters from the header row to come up with RentIncrease1 or RentIncrease2?

I'm trying to prevent having a dozen rows with custom formulas for each row.

If I'm understanding your problem correctly, I think you could use INDIRECT, which converts what's in a cell to a reference. For example, if cell A1 contains 'C12' (without inverted commas) and you put =INDIRECT(A1) into B1, B1 will display the contents of cell C12.

Otherwise, please consider uploading a sample file (with confidential information removed) which shows what you want to achieve - to upload, click 'Go Advanced' under the reply box and use the paperclip icon.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

Thanks for asking...attached in one of the many tables that I'm trying to make easier to update.

NamedRange Shortcut Example.xlsx

Wow, that's actually pretty good. Although I included the "table" where the values and row titles are shown, the actual values are contained in a single row on another page and the 10 cells are each a named range, there is no column or row header as this VLookup function uses. My fault for not stating that initially. I think the limitation is going to come in that there may be no way to cobble together bits of cell data to create the named range reference for use in a formula.

Originally Posted by JimDandy

<snip>can I concatenate "RentIncrease" and the right 2 characters from the header row to come up with RentIncrease1 or RentIncrease2?

If what you want to do is replace any formula which contains 'RentIncreaseX' with something which puts the X in automatically, try these:

In column D, instead of =L7*(1+RentIncrease1) try this:

Formula:

Please Login or Register  to view this content.

and in column E onwards, instead of =D21*(1+RentIncrease2) (and similar), try this:

Formula:

Please Login or Register  to view this content.

Is that something you can use with your file layout?

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

Aardigspook, that was the exact solution I needed, thanks!

Thanks too for the other options.

You're welcome, glad I could help - and thanks for the rep.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.