Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167

    Unanswered: Look up a value by a concatenated referrence?

    I have a spreadsheet with one summary worksheet and multiple worksheets, one for each month of history. I would essentially like to lookup a value in one of the monthly worksheets depending upon a month and year cell that can be populated in the summary sheet.

    To simplify, imagine I have each monthly worksheet tab numbered according to the month (1 for January, 2 for Feb, ...etc). On my summary sheet, there is a cell in which I can enter the month number I want, say I enter 1. I would like to lookup a cell B5 from the month I specified (1 or January). I've tried concatenating a dynamic referrence so that it looks up the value on the sheet number I specified in the control cell but it seems like this only yields a text version of the variable. Is there another lookup type function to do this?.

    Thanks,

    Joshua

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Joshua,

    Let's say in Summary!B2 you have a number from 1 to 12.
    Let's also say that you have worksheets in the same workbook called January, February etc...

    To get the value from cell A1 on the relevant month worksheet, you could use a formula such as this:
    Code:
    =INDIRECT("'"&TEXT(DATE(2010,B2,1),"mmmm")&"'!A1")
    Note that INDIRECT() is volatile.

    Hope that helps...
    Last edited by Colin Legg; 09-17-10 at 12:23.

  3. #3
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    Colin,

    Cool, thanks. That seems simple enough.

    Joshua

Posting Permissions

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