Results 1 to 8 of 8

INDIRECT sheet references within LOOKUP function

Threaded View

  1. #1
    Registered User
    Join Date
    09-28-2016
    Location
    Germany
    MS-Off Ver
    Excel for Mac Version 15.17
    Posts
    4

    INDIRECT sheet references within LOOKUP function

    Let's say I have Sheet 'A', in which I want to gather information within one cell from another Sheet 'B' to Sheet 'Z'. I want the reference Sheet name to change dynamic, according to a cell value (text) I enter in Sheet A. The row from where the data needs to be picked up can differ, so I added a Value in a cell of the reference Sheet, that will not change. This is in the same row as the value I want to pickup.
    I've used LOOKUP on functions where my reference sheet does not change and this works fine for me. Working with variable reference sheet name, is just killing me right now. I need the sheet name 'Jul2016'! to be dynamic according to the value in B5 of my Sheet A.

    =LOOKUP("MonthKPIs";'Jul2016'!A:A;'Jul2016'!J:J)

    This is, what my function looks like at the moment. There seems to be something going wrong...

    =LOOKUP("MonthKPIs";INDIRECT("'"&B5&"'!&A:A");INDIRECT("'"&B5&"'!&J:J)

    MonthKPIs is the static cell value on all of my delivery sheets. So I want Excel to find this value on the sheet.

    The cell B5 on my destination sheet should define the name of the delivery sheet, where I know the reference value "MonthKPIs" is in column A. So I want Excel to find MonthKPIs on the Sheet that is defined be cell B5.

    The Value to return is in the same delivery sheet, but this time in column J. So I want Excel to return the value in column J of row "MonthsKPIs" on the sheet with the name from cell B5.

    Does this make sense to anyone? I'm not even 100% sure INDIRECT is the right thing to use...
    Last edited by PeterScho; 09-28-2016 at 09:40 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] INDIRECT Function NOT capturing all of the references
    By Murphy15 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 02-09-2017, 10:00 PM
  2. [SOLVED] Changing Cell References inside INDIRECT function.
    By Zox Tomana in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-08-2016, 12:36 AM
  3. Using Structured Table References and Indirect Function
    By tjeasy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-02-2016, 07:07 PM
  4. Replies: 5
    Last Post: 01-30-2015, 06:39 PM
  5. [SOLVED] Changing cell references in INDIRECT function
    By GavJ in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-25-2013, 11:38 PM
  6. Replies: 1
    Last Post: 02-18-2012, 10:31 AM
  7. Lookup function with different cell references
    By Nett in forum Excel General
    Replies: 3
    Last Post: 07-13-2009, 10:01 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1