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

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? ...

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

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?

2. Re: Use text from a cell as part of a reference or formula

Originally Posted by claysand
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)

4. Re: Use text from a cell as part of a reference or formula

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!

5. Re: Use text from a cell as part of a reference or formula

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?

6. Re: Use text from a cell as part of a reference or formula

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

7. Re: Use text from a cell as part of a reference or formula

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

Thanks again.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•