+ Reply to Thread
Results 1 to 10 of 10

Using ROW function inside of both an Indirect and Index function returns #VALUE

  1. #1
    Registered User
    Join Date
    09-10-2012
    Location
    Guildford, UK
    MS-Off Ver
    Excel 2003
    Posts
    23

    Using ROW function inside of both an Indirect and Index function returns #VALUE

    Hi there.

    Im trying to use the Row function, but it's causing me some problems. My formula is pretty massive, so it'd just be cruel to post it up; but perhaps someone can help identify the problem at hand.


    __


    What I am trying to do is perform the equivalent of a lookup that has multiple criteria. In my case, it compares the value of one cell with all values in column F of a different worksheet, and then the value of another cell with all the values in column Z of that same other worksheet, and then return the value in column G of the row where both comparisons resulted in the same value.

    __

    Here is my rather scary code (please bare with me on this one):

    Please Login or Register  to view this content.
    __

    In this code E1 is a cell which contains string with the name of the sheet that is being referenced.
    I have highlighted the 'seemingly problematic areas' in red. When I try to evaluate the formula step-by-step, the following happens:

    When I evaluate the formula the program gets to the following stage (INDIRECT("Z" & ROW())). At this point, rather than return a numeric value of the row like it normally should, it returns the value inside of some curly-brackets. For example if it was in cell G9 it would turn into (INDIRECT("Z" & {9})). This is creating problems because when the program tries to evaluate the next step it returns the #VALUE error. Normally if i type out =INDIRECT("Z" & ROW()) there is no issue (and the ROW function doesnt evaluate into a value surrounded by curly brackets).

    I assume there is a probem because my INDIRECT function is inside of a INDEX function.

    If I don't use the Row function and just hard-code in a value, my formula works fine. Except I would rather have the formula automatically detect the row number (as there are hundreds of rows and columns that need to include this formula), and I will have to manually edit each one. Not to mention I am making a dashboard for a client, which if they need to add / delete rows, I will be in trouble.

    Can anyone help? I've heard INDIRECT functions can't use dynamic ranges, but then how do I get around my problem?

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Using ROW function inside of both an Indirect and Index function returns #VALUE

    so you want to lookup on 2 criteria in two different columns and get the result from col g?
    eg find xxxx in col e and yyyy in column g and where they coincide return from same row in g?
    post a workbook showing what you mean
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    09-10-2012
    Location
    Guildford, UK
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Using ROW function inside of both an Indirect and Index function returns #VALUE

    OK, so In the worksheet called 'Example-Derived-Score-Sheet (2)' I want to be able to populate columns G-R with scores from the other worksheet 'QA-Score-MP-Maps'. Depending on what value is typed out into cell E2 (derived feature) it should update the sheet to relfect the appropriate score.

    Therefore what I need to do, is have each cell in the range G8 - R107 reference the table from the other worksheet. To do this I want it to make 2 comparisons.

    The first lookup style comparison, is to compare the value in the Z column (of the same row) with a range of cells in column F of the other sheet (specifically F8:F(last cell with a value in column)).

    The second comparison, takes the value in column F (of the same row) and compares that with the Z column of the other worksheet.

    After this, a single Row should be identified in the other worksheet, and the value in the G column of that worksheet should then be inserted into the G column of the primary 'example-derived-score' worksheet.

    ______

    In the example workbook you can see two different but similar formulas in cells G8 & G9. G8 returns the correct value (you can type out Level Flow or Authenticity in E2 to see it update accordingly). However G9 returns an error.

    The only difference between these two formulas is that in G8 I have hardcoded in the references to the 8th row by typing out
    INDIRECT("Z" & "8")
    and
    INDIRECT("F" & "8")

    whereas, in G9 these are replaced with something more akin to what I want:
    INDIRECT("Z" & ROW() )
    INDIRECT("F" & ROW() )

    ____

    When evaluating it, it comes up with the problem outlined in the previous post.
    ____

    Is there a way I can get it to use the Row() function? Hard-coding the rows in will not be a very good solution.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-10-2012
    Location
    Guildford, UK
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Using ROW function inside of both an Indirect and Index function returns #VALUE

    Also I did hold down Ctrl+Shift when pressing return for both. But when editting the formula for posting purposes i just forgot to. But the same problem still exists.

  5. #5
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Using ROW function inside of both an Indirect and Index function returns #VALUE

    seems over complicated but try rows() instead of row() and there are spaces all over that formula also e1 needs to be absolute
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by martindwilson; 09-10-2012 at 02:02 PM.

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Using ROW function inside of both an Indirect and Index function returns #VALUE

    It all seems a bit overcomplicated to me too, can't you use a formula like this?

    =LOOKUP(2,1/('QA-Score-MP-Maps'!$Z$8:$Z$100=$F8)/('QA-Score-MP-Maps'!$F$8:$F$100=$Z8),'QA-Score-MP-Maps'!$G$8:$G$100)

    ...with an INDIRECT formula in place of the sheet name if you want that to be dynamic

    As for ROW function, yes the problem is that it returns an array, hence the curly braces, in some formulas that doesn't work, so Martin's ROWS option should work as a replacement
    Audere est facere

  7. #7
    Registered User
    Join Date
    09-10-2012
    Location
    Guildford, UK
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Using ROW function inside of both an Indirect and Index function returns #VALUE

    Fair play about E1 being absolute, however unfortunately the rest of the suggestions dont fix the issue at hand. The same error is created, and when in G9, and feeds back incorrect data when inside G8.

    I do apologise about how complicated the code appears: I'll try and run through it to make it seem a bit easier to take in.

    start of If and Index
    =IF((INDEX

    name of the worksheet where the index is making it's comparisons
    (INDIRECT("'" & E1 & "'!" &

    identify first cell in index range (column letter + 8 ----'as 8 is always the first row')
    CHAR(COLUMN() + 64) & "8:" & CHAR(COLUMN() +64) &

    On the refernced worksheet examine how many rows there are that should be referenced, and identify the last cell in index range (column letter & row 55 (56-1))
    ((LOOKUP(9.99999999999999E+307,INDIRECT("'" & E1 & "'!A8:A300"))) -1)),

    begin Match Function
    MATCH(1,

    Identify the first and last cell in the other worksheet's F column and set the first range for the Match
    (INDIRECT("'" & E1 & "'!F8:F" & ((LOOKUP(9.99999999999999E+307,INDIRECT("'" & E1 & "'!A8:A300"))) -1) )

    Ask whether any of those cells in the F column are equal to the Z cell in the main worksheet that shares a row with Formula cell
    = INDIRECT("Z" & ROW()))
    NB. The issue is with this ROW() function

    Do a multiplication as two columns are being compared
    *

    Similarly to before. Identify the first and last cell in column Z of the other workbook that should be part of the comparison
    (INDIRECT("'" & E1 & "'!Z8:Z" & ((LOOKUP(9.99999999999999E+307,INDIRECT("'" & E1 & "'!A8:A300"))) -1) )

    Ask if any of these cells are the same as the current worksheet's F cell that shares a row with the Formula cell
    = (INDIRECT("F" & ROW() )))

    Conclude MATCH and Index
    , 0)))

    In the event that the formula = 0, then mark the formula cell as blank, or otherwise...
    = 0, "",

    It should equal the result of the previous formula (formula copy and pasted into else section of if statement)
    (INDEX(INDIRECT("'" & E1 & "'!" & CHAR(COLUMN() + 64) & "8:" & CHAR(COLUMN() +64) & ((LOOKUP(9.99999999999999E+307,INDIRECT("'" & E1 & "'!A8:A300"))) -1)), MATCH(1, (INDIRECT("'" & E1 & "'!F8:F" & ((LOOKUP(9.99999999999999E+307,INDIRECT("'" & E1 & "'!A8:A300"))) -1) ) = INDIRECT("Z" & ROW())) * (INDIRECT("'" & E1 & "'!Z8:Z" & ((LOOKUP(9.99999999999999E+307,INDIRECT("'" & E1 & "'!A8:A300"))) -1) ) = (INDIRECT("F" & ROW() ))), 0))))

  8. #8
    Registered User
    Join Date
    09-10-2012
    Location
    Guildford, UK
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Using ROW function inside of both an Indirect and Index function returns #VALUE

    The reason why it has to be a bit more complicated is that the number of refernced rows in the other worksheet could vary. In the example file ive only got one other worksheet being refernced, but in the there will be many more later whih have varying lengths.

    Let me see if I can adjust your new suggestion though to make it work...

  9. #9
    Registered User
    Join Date
    09-10-2012
    Location
    Guildford, UK
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Using ROW function inside of both an Indirect and Index function returns #VALUE

    AH WONDERFUL!

    You're proposed solution, at a basic level does work. I can add in all the little extras now to make it work dynamically.

    Thanks a ton for the help!

  10. #10
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Using ROW function inside of both an Indirect and Index function returns #VALUE

    @ xandermacleod

    Welcome to the forum.

    Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
    Thanks.

    Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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