Use text from a cell as part of a reference or formula

[claysand]

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?

 

[Rick Rothstein]

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.

Give this a try...

=A5*INDIRECT(A1&6)

 

[claysand]

Unreal. Perfect. Had no idea about this. Thanks!

 

[claysand]

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:

=INDIRECT("'[Budget.xlsx]Sheet1'!$C"&B1)

Handy little formula! Thanks again for the help!

 

[claysand]

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?

 

[VoG]

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

 

[claysand]

Thanks. Not sure if that'll exactly do it for me, but helpful to see.

Thanks again.