Dynamic WorkSheet Reference

[lehminh]

Hi All,

I've been searching for the last 3 hours and had no luck. I am trying to dynamically return the largest value from another worksheet 2 range E2 to W2 that has the title in cell D1 or worksheet 1.

=MAX((INDIRECT("'"&D1&"'!"&"$E2:$W2")))

But the "$E2:$W2" makes it not dynamic, i.e. dragging the formula down rows, the result are the same. 2 doesn't auto increment up.

Basically, and if I move it out of the "" it looks at the range from E2:W2 from Worksheet 1 (not 2).

Please help.

 

[tygrrboi]

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}span.s1 {color: #006107}span.s2 {color: #0057d6}</style>Try this


<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}span.s1 {color: #006107}span.s2 {color: #ab30d6}span.s3 {color: #a54a29}span.s4 {color: #0057d6}</style>
=MAX(INDIRECT(ADDRESS(ROWS($A$1:A2),5,3,,$D$1)&":"&ADDRESS(ROWS($A$1:A2),23,3)))

or

=MAX(INDIRECT("'" & $D$1 & "'!$E" & ROWS($A$1:A2) & ":$W" & ROWS($A$1:A2)))

 

[lehminh]

Same issue, it still looks at the wrong workbook.

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}span.s1 {color: #006107}span.s2 {color: #0057d6}</style><style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}span.s1 {color: #006107}span.s2 {color: #ab30d6}span.s3 {color: #a54a29}span.s4 {color: #0057d6}</style>

 

[himperson1]

if you're looking to make it autofill 2,3,4, etc i think you'll need to create that sequence of numbers elsewhere and then refer to it.
insert a new column somewhere and in the first cell, enter =row() to give you an automatic 1,2,etc based on the row number. hide it if you'd like.

=MAX((INDIRECT("'"&D1&"'!"&"$E"&"(wherever your 2 is):$W"&"(wherever your 2 is)")))

 

[tygrrboi]

if you're looking to make it autofill 2,3,4, etc i think you'll need to create that sequence of numbers elsewhere and then refer to it.

That is exactly with ROWS($A$1:A2) does. counts the rows listed. A1:A2 is 2 rows. Drag it down and it becomes A1:A3... 3 rows.


This is a bit safer than just using ROW() because if your data ever moves up or down, the number won't change.




The formula I provided you will not look at any other workbook than the one you have provided for it in D1.

Do you have the correct workbook name in D1?


Also, I assumed that when you drag the formula down to look at rows E3:W3, E4:W4 etc you still want to use the sheet listed in D1... If you want it to look at D2 for E3:W3 and D3 for E4:W4 then just remove the two $ signs, before D and before 1.