+ Reply to Thread
Results 1 to 7 of 7

Nested INDIRECT Command Not Populating Correctly

  1. 02-17-2014, 10:21 PM #1
    lordfa9
    lordfa9 is offline
    Forum Contributor
    Join Date
    05-07-2007
    Location
    Singapore
    MS-Off Ver
    2006/2016
    Posts
    170

    Nested INDIRECT Command Not Populating Correctly

    Please refer to my screenshot.

    The formula in question is:
    =N$4*VLOOKUP($B5,'BOM MATRIX'!$A$2:$AJ$500,INDIRECT("N3"),FALSE)
    which is currently keyed into cell N5

    what I'm trying to do is to populate rows sideways so for example the formula in cell O5 should be:
    =O$4*VLOOKUP($B5,'BOM MATRIX'!$A$2:$AJ$500,INDIRECT("O3"),FALSE)

    problem is whenever I copypaste. The first part (N$4-> O$4) populates correctly while INDIRECT("N3") remains the same even though it should be INDIRECT("O3") instead

    Is there anything I can do to fix this?
    Attached Images Attached Images
    Reply With QuoteRegister To Reply
  2. 02-17-2014, 10:33 PM #2
    vlady
    vlady is offline
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Nested INDIRECT Command Not Populating Correctly

    maybe something like this

    =N$4*VLOOKUP($B5,$A$2:$AJ$500,INDIRECT(CHAR(95+COLUMN()+1)&"3"),FALSE) -> fill sideways
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir
    Reply With QuoteRegister To Reply
  3. 02-17-2014, 11:20 PM #3
    lordfa9
    lordfa9 is offline
    Forum Contributor
    Join Date
    05-07-2007
    Location
    Singapore
    MS-Off Ver
    2006/2016
    Posts
    170

    Re: Nested INDIRECT Command Not Populating Correctly

    Quote Originally Posted by vlady View Post
    maybe something like this

    =N$4*VLOOKUP($B5,$A$2:$AJ$500,INDIRECT(CHAR(95+COLUMN()+1)&"3"),FALSE) -> fill sideways
    you missed out the sheet name, but I got it to work

    Problem now is when I went from Z$4 to AA$4 it started returning an error result. Does this have to do with the CHAR command?
    Reply With QuoteRegister To Reply
  4. 02-17-2014, 11:25 PM #4
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Nested INDIRECT Command Not Populating Correctly

    I don't see why you're using INDIRECT.

    In your screencap those cells just contain numbers which I assume are supposed to be the column_index_num's.

    See if this does what you want:

    =N$4*VLOOKUP($B5,'BOM MATRIX'!$A$2:$AJ$500,N3,0)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  5. 02-17-2014, 11:27 PM #5
    lordfa9
    lordfa9 is offline
    Forum Contributor
    Join Date
    05-07-2007
    Location
    Singapore
    MS-Off Ver
    2006/2016
    Posts
    170

    Re: Nested INDIRECT Command Not Populating Correctly

    Quote Originally Posted by Tony Valko View Post
    I don't see why you're using INDIRECT.

    In your screencap those cells just contain numbers which I assume are supposed to be the column_index_num's.

    See if this does what you want:

    =N$4*VLOOKUP($B5,'BOM MATRIX'!$A$2:$AJ$500,N3,0)
    O_O that works! and yes it is the column_index_num's.

    UPDATE: having trouble populating, jury's still out
    UPDATE: Turns out I forgot to put $ for N3 :P
    Last edited by lordfa9; 02-17-2014 at 11:34 PM.
    Reply With QuoteRegister To Reply
  6. 02-17-2014, 11:30 PM #6
    vlady
    vlady is offline
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Nested INDIRECT Command Not Populating Correctly

    lol, i was thinking something when i saw the indirect there.

    Nice!
    Reply With QuoteRegister To Reply
  7. 02-17-2014, 11:40 PM #7
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Nested INDIRECT Command Not Populating Correctly

    Good deal. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Simple nested IF statement not working correctly
    By bassemsaad in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-04-2013, 08:53 AM
  2. why doesn't this INDIRECT resolve correctly?
    By hank_t in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-27-2012, 04:06 PM
  3. VBA range not populating correctly
    By Squirrel in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-12-2011, 11:41 AM
  4. Command button not populating correctly
    By taichi56 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-18-2009, 10:32 AM
  5. Pivot Table Not Populating Correctly
    By Kumara_faith in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-04-2008, 08:23 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1