Lookup across multiple sheets 1

[nat101]

Hi;

In a wb with multiple sheets, all having a number in a given cell (j2), I would like to get the sheetname (or return the value of another cell) of the sheet where (j2 > x).

Thank you in advance;
-nat

 

[WildeWebPublishing]

=INDEX(Sheet2!A:A,MATCH(A1,Sheet2!A:A,0))

I am barking up the right tree, but I forget the =if<> formula.. Hope this halps a little..

 

[nat101]

I don't understand where to substitue the START and END sheet in the formula you posted.

thanks
-nat

 

[SkipVought]

Hi,

1. On your worksheet, build a table of sheet names in your workbook
2. past formula in cells adjacent to sheet names

=INDIRECT(A2&"!J2")

This will return the values in J2 on each sheet.
3. formula returning sheet name...

=IF(B2>X,A2,"")

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[nat101]

Thank you much. Right on, especially that I already had a column of all the sheet names.
One (un)related caveat though. Since this workbook has more than 500 sheets (using >350 meg ram!!) and since j2 on each sheet is a derived value (of a derived value...), there is a noticeable 'wait' (and page ripple) after any change on the sheet in any unrelated cell. Now, I know that I can turn auto-recalc off for the sheet, but preferbly I would like the INDIRECT function to execute only if x which is really cell M1 contains a value. So, when I put something in M1, let it ripple thru all the indirect functions, otherwise it should act as if all the cells with the indirect were null.

Thanks again
-nat

 

[SkipVought]

Well, you could make Calculate, Manual and hit F9 after changing M1.

If you wanted that to be &quot;automatic&quot; you could right-click the sheet tab (on the M1 sheet), select view code and paste this code...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rng As Range
    Set rng = Application.Intersect(Target, Range("M1"))
    If Not rng Is Nothing Then ActiveSheet.Calculate
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

Oops,

should have included setting tng to Nothing

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rng As Range
    Set rng = Application.Intersect(Target, Range("M1"))
    If Not rng Is Nothing Then ActiveSheet.Calculate
    Set rng = Nothing
End Sub
[code]


Skip,
Skip@TheOfficeExperts.com
[URL unfurl="true"]www.TheOfficeExperts.com[/URL]

 

[nat101]

My question is really how Excel evaluates an IF function that returns false. Does it evaluate the 'true' portion anyhow? If it only evals the true portion when true, than I can wrap your INDIRECT function to return as part of the TRUE function of an IF M1 > 0.

Thanks again.
-nat

 

[nat101]

we both posted simultaneously..

I want calc to be automatic for everything else, EXCEPT for these two new columns that depend on M1.

ta
-nat