+ Reply to Thread
Results 1 to 2 of 2

Use indirect cell in a formula

  1. #1
    Forum Contributor
    Join Date
    06-08-2012
    Location
    Panama City, FL
    MS-Off Ver
    Excel 2016
    Posts
    175

    Use indirect cell in a formula

    Hello friends, please help with this problem I'm having.

    I have two spreadsheets: One.xlsx and Two.xlsx

    I am creating a formula in One to retrieve data from Two. As shown below, the formula in One is pointing to Two, BUT, in this instance the formula doesn't KNOW to go to row 11, it only knows the range B to CC. The row 11
    actually comes from a cell in [One.xlsx]Sheet1'!BF17 which contains the formula: =MATCH(B17,'[Two.xlsx]Sheet1'!$B:$B,0)


    This formula is in One.xlsx

    =INDEX('[Two.xlsx]Sheet1'!$B11:$CC11,E$13-1)

    So, how do I replace these two 11's with that MATCH that is calculated in One.xlsx which will result in 11?

    I hope this makes sense to you and I thank anyone in advance for trying to help me.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,458

    Re: Use indirect cell in a formula

    Why did not you try to use an INDEX(RANGE,row_index, column_index) to get the intersecting value?
    RANGE='[Two.xlsx]Sheet1'!B1:CC10000
    row_index = MATCH(B17,'[Two.xlsx]Sheet1'!$B:$B,0)
    column_index = E$13-1

    and formula should be:

    Please Login or Register  to view this content.
    Quang PT

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. INDIRECT -- need cell range to change to the INDIRECT method
    By skydivetom in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-04-2018, 11:18 AM
  2. Need Help with Indirect Formula (not empty cell value)
    By daga.sachin120887 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-13-2017, 04:15 PM
  3. indirect a formula off another cell
    By ammartino44 in forum Excel General
    Replies: 2
    Last Post: 05-06-2015, 08:44 PM
  4. INDIRECT for cell that contains formula?
    By Dahlia in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-27-2014, 02:56 AM
  5. Indirect Formula: Reference cell
    By leviathan86 in forum Excel General
    Replies: 8
    Last Post: 11-29-2013, 02:40 PM
  6. Sum of indirect sheet names with multiple cells (SUM, INDIRECT,SHEETNAME in cell)
    By a1b2c3d4e5f6g7h8 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-12-2013, 08:42 AM
  7. Indirect Row & cell formula
    By George Andrews in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-24-2005, 06:06 PM

Tags for this Thread

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