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
fbmartinezAsked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
The formula works in the file provided. Check this file.
Copy-of-Xl0000020.xls
0
 
Saqib Husain, SyedEngineerCommented:
You can use indirect function

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

instead of

Sheet1!A1:A5
0
 
Saqib Husain, SyedEngineerCommented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

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

Thanks for your continued help!

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
 
Saqib Husain, SyedEngineerCommented:
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
 
fbmartinezAuthor 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
 
fbmartinezAuthor Commented:
The formula did not work, by the way. It returns the same data across the board.
0
 
fbmartinezAuthor 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
 
Saqib Husain, SyedEngineerCommented:
To find the last row number you can use the formula

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

in F5
0
 
fbmartinezAuthor 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
 
Saqib Husain, SyedEngineerCommented:
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
 
fbmartinezAuthor 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
 
Saqib Husain, SyedEngineerCommented:
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.

All Courses

From novice to tech pro — start learning today.