Results 1 to 7 of 7

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. #1
    New Member
    Join Date
    Nov 2012
    Posts
    9

    Default 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. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    15,680

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

    Quote Originally Posted by claysand View Post
    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)
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? Try one of these MrExcel HTML Maker, Excel jeanie or Borders-Copy-Paste

  3. #3
    New Member
    Join Date
    Nov 2012
    Posts
    9

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

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

  4. #4
    New Member
    Join Date
    Nov 2012
    Posts
    9

    Default 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. #5
    New Member
    Join Date
    Nov 2012
    Posts
    9

    Default 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. #6
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,099

    Default 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
    HTH, Peter
    Please test any code on a copy of your workbook.

  7. #7
    New Member
    Join Date
    Nov 2012
    Posts
    9

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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com