Row Reference in Formula from Cell Entry?

If I wanted to use 3 cell entries to set the page reference, along with starting row and ending row in the following formula, how would I do it?

The columns for now are static on all sheets

Example Formula

=INDEX('Question 12'!A4:A7,MATCH(MAX('Question 12'!D4:D7),'Question 12'!D4:D7,0))

So, that I can use say
Cell A1 = Question 12
Cell B1 = 4
Cell C1 = 7

And have the formula reference those to build itself?
LVL 1
Who is Participating?

EngineerCommented:
The formula works in the file provided. Check this file.
Copy-of-Xl0000020.xls
0

EngineerCommented:
You can use indirect function

indirect("sheet1" & "!a1:a5")

Sheet1!A1:A5
0

EngineerCommented:
if

A1 has sheet1
A2 has sheet2
A3 has sheet3

then indirect(A1 & "!A1:A5") can be used

If you upload a file we can demonstrate it here
0

Author Commented:
Here's a sample file.

What I'd like to do is use the data in columns D, E, and F, to create the formula in column B on the SUMMARY sheet.

There are 100+ sheets and they all vary in the row they end - they all start on Row 4 though. I'd like the simplest way to refer to the last row, and I thought about a macro or something, but want to keep it simple, so I might go through and manually add this info on the real summary sheet for each dependent sheet.
Example-Book.xlsx
0

EngineerCommented:
Paste this formula in B5 and copy down

=INDEX(INDIRECT("'"&A5&"'!A4:A"&COUNTA(INDIRECT("'"&A5&"'!A4:A20"))-2+3),MATCH(MAX(INDIRECT("'"&A5&"'!D4:D"&COUNTA(INDIRECT("'"&A5&"'!D4:D20"))-2+3)),INDIRECT("'"&A5&"'!D4:D"&COUNTA(INDIRECT("'"&A5&"'!D4:D20"))-2+3),0))
0

Author Commented:
I guess the next best thing would be a Macro to run through and put the formula into each row by building it?

One last question to this one...

How can I determine the last row in a range if I always know that the last row in that range has the same value using a formula?

For example:

Column A
Test 1
Test 2
Test 3
Test 4
NOT DATA I WANT

Column A
Test 1
Test 2
Test 3
NOT DATA I WANT

If I have 2 sheets with the above, is there a way for me to use a formula in Excel to basically always return the row before NOT DATA I WANT?
0

Author Commented:
The formula did not work, by the way. It returns the same data across the board.
0

Author Commented:
I think I misguided you...

What I want is for the formula to be built based on Columns D, E, and F

So that it uses the data in D6 = Question 38, E6 = 4, F6 = 8 to build the reference within the formula.
0

EngineerCommented:
To find the last row number you can use the formula

=COUNTA(INDIRECT("'"&A5&"'!A4:A20"))+1

in F5
0

Author Commented:
I think I misguided you...

What I want is for the formula to be built based on Columns D, E, and F

So that it uses the data in D6 = Question 38, E6 = 4, F6 = 8 to build the reference within the formula.

If you can adjust the formula, please, then this should be great for today :)
0

EngineerCommented:
With my previous formula for last row number in F5 you can use this simplified formula in B5

=INDEX(INDIRECT("'"&A5&"'!A4:A"&F5),MATCH(MAX(INDIRECT("'"&A5&"'!D4:D"&F5)),INDIRECT("'"&A5&"'!D4:D"&F5),0))
0

Author Commented:
Excellent job! Keep up the great support! Are we allowed to communicated outside of this realm? Can I email you directly in the future? Or I'll just post questions and hope you reply! Thanks again...
0

EngineerCommented:
You are welcome to contact me. You can see my profile.

But you would be much better off, asking such questions, on this forum where you can get responses round the clock, and when I am not looking at these questions, and from better experts.

Thanks for the points.

Saqib
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.