This is a discussion on Use text from a cell as part of a reference or formula within the Excel Questions forums, part of the Question Forums category; Is it possible to use the content (even the text) of one cell for part of the reference of another? ...
Is it possible to use the content (even the text) of one cell for part of the reference of another?
For example, I have several sheets filled with formulas like =A5*B6. I have lot's of these formulas and I would like to set it up so I could change a variable in all of them by only changing one thing.
So, if I have A5*B6, A5*B7, A5*B8, etc. but want to change it across the whole sheet from Bs to Cs. Would it be possible to say A5*(=A1)6 where A1="B", so it would result in A5*B6, or A1="C" and it would change all the formulas to A5*C6.
So, I'd like to use the text or value from one cell as a part of a reference of another.
Is this possible?
Unreal. Perfect. Had no idea about this. Thanks!
And just to add further help to anyone who might be looking for similar things- I actually needed this to have a constant column and variable row, and to reference another sheet (called Budget.xlsx). Here's what I ended up using:
Handy little formula! Thanks again for the help!
OK- another update and question - I just discovered this referencing only works with the worksheet being referenced is open. That kind of kills is.
Any way to do this so it would behave like a normal cross-workbook cell reference that just updates when the workbook is opened?
Sorry, INDIRECT won't work with closed workbooks.
You could use INDIRECT.EXT part of the MOREFUNC add-in. See Daily Dose of Excel » Blog Archive » INDIRECT and closed workbooks
Please test any code on a copy of your workbook.
Thanks. Not sure if that'll exactly do it for me, but helpful to see.