[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Row Reference in Formula from Cell Entry?

Posted on 2011-05-10
Medium Priority
410 Views
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?
0
Question by:fbmartinez
• 7
• 6

LVL 43

Expert Comment

ID: 35733047
You can use indirect function

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

Sheet1!A1:A5
0

LVL 43

Expert Comment

ID: 35733061
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

LVL 1

Author Comment

ID: 35733149
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

LVL 43

Expert Comment

ID: 35733332
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

LVL 1

Author Comment

ID: 35733442
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

LVL 1

Author Comment

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

LVL 1

Author Comment

ID: 35733481
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

LVL 43

Accepted Solution

Saqib Husain, Syed earned 2000 total points
ID: 35733508
The formula works in the file provided. Check this file.
Copy-of-Xl0000020.xls
0

LVL 43

Expert Comment

ID: 35733571
To find the last row number you can use the formula

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

in F5
0

LVL 1

Author Comment

ID: 35733591
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

LVL 43

Expert Comment

ID: 35733647
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

LVL 1

Author Closing Comment

ID: 35733657
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

LVL 43

Expert Comment

ID: 35733742
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
###### Suggested Courses
Course of the Month20 days, 8 hours left to enroll