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?

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.

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.

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))))

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...

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!

@ 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.