Referencing Worksheet Page

[Jeremy35]

Is there a way to reference a cell value for the worksheet number in the formula below? My worksheets are named 1-10 with worksheet '1' being used in the example. With the formula in cell B2 I would like it to reference the value (1-10) of cell A1 for the worksheet name.

Formula = MATCH(P3,'1'!$AP$4:$AP$2500,0)

Ex:
A1 = 1 ; Formula = MATCH(P3,'1'!$AP$4:$AP$2500,0)
A1 = 2 ; Formula = MATCH(P3,'2'!$AP$4:$AP$2500,0)
A1 = 3 ; Formula = MATCH(P3,'3'!$AP$4:$AP$2500,0)

I have tried using Text() and NumberValue() within the apostrophes, but neither accomplished what I am after.

Any help would be greatly appreciated!

J.

[Special-K]

Try

=MATCH(P3,INDIRECT(A1&"!$AP$4:$AP$2500"),0)

[mcmahobt]

I'm not quite sure how your data is setup, but it looks like you may want to look into INDIRECT for what you're trying to accomplish.

[Jeremy35]

Thanks for the responses! Unfortunately I am still unable to get it to work. When using the indirect function as suggested the cell returns the error #REF. The description for the Indirect() function does sound like it should do what I am after, but I have not been able to put together the right format yet.

I have attached an example of what I am after. In the example there is a "Main" worksheet as well as three others named "1", "2", and "3". On the "Main" page I would like to be able to select the worksheet name (1,2,3) in the blue cells and then have the value from that worksheet populate in the corresponding green cells. I have also shaded the cells containing this data green for clarity. I need the formula to take the color in the "Main" list, find it in the selected (input) worksheet and return the appropriate value. Updating of course when the worksheet selection is changed.

Example: Ex 3.xlsx

Thanks for the help guys/gals,
J.

[Jeremy35]

Just noticed it is something to do with how I am using the function and not necessarily the suggested formula. I simply typed in "INDIRECT(A1)" and still recieved the error #REF. Cell A1 contains the value I want, but is derived as the result of its own formula contained in that cell. Could it be a formatting issue? I tried formating A1 to text, but still no luck.

Thanks,
J.

[FDibbins]

Formatting (generally) only changes the cosmetic of a cell, not its contents. Also, formulas work on the "answer of a formula" or what is displayed in a cell, not what the formula is.

Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[Jeremy35]

Thanks for the info. On the post just before my last one there is an example sheet and description. I attached it as : EX 3.xlsx

Hopefully it is clean/clear enough.

Thanks again!
J.

[FDibbins]

oops sorry, missed that.

Try this, copied down...
=VLOOKUP(C3,INDIRECT("'"&D3&"'!$C$3:$D$12"),2,0)

Note that INDIRECT() is used to convert a cell's contents into something excel can use as a reference in a formula. So apart from the actual cell you are referencing, all the other stuff needs to be "textified"

If you were to refence sheet "1" in the normal manner, you would use this...
=VLOOKUP(C3,'1'!$C$3:$D$12,2,0)

But because you need to be able to specify the sheet name, the lookup reference range needs to be "fiddled with"
'1'!$C$3:$D$12
we need to reference the cell containing 1, which is D3, but thats not all there is to that sheet reference. Note the ' either side. Because of that, we need to "textify" those by putting them inside quotes...
"'" (hard to see, but that is a " followed by a ' followed by another " with no spaces included)
Likewise, the range now also needs to be "textified", using the same approach...
"$C$3:$D$12"
Because this is now text, and we need to include '! we can put that in there too...
"'!$C$3:$D$12"

Finally, we need to link all that together, which we do, using &
=VLOOKUP(C3,INDIRECT( "'" & D3 & "'!$C$3:$D$12" ),2,0)
I added spaces to show, but you can leave them out

Hope that helps?

[Jeremy35]

I am nesting this inside a LOOKUP() function/formula for returning the data, but the MATCH() function has to return the correct value first which is where I am having issues.
Even just using INDIRECT(A1) returns #REF no matter whether it is a number or text.

[Jeremy35]

ok...progress...apparently A1 needs to be in quotes inside the function as well. INDIRECT("A1")

[FDibbins]

Did you try the vlookup? My explanation should also enable you to work out what the INDIRECT() needs to look like, if there are multiple reference to other sheets, you will probably need to use multiple INDIRECT()'s

INDIRECT() needs a reference to work with, not a number or just any old text. See the example below, I put text A1 in cell I1, Cell A1 contains the word Month...

	I
1	A1
2	Month

I2=INDIRECT(I1)

[Jeremy35]

FDibbins, Thanks for the help. Still haven't been able to get anything to work. Tried many more combos last night as well as many this morning. Gonna keep trying, but may have to move on and just find a work around. Seems like this should be pretty straight forward. Back to the drawing board as they say...

Thanks again,
J.

[mcmahobt]

Try this in E3 and drag down to fill:

Please Login or Register  to view this content.

[Jeremy35]

Guiness-Brilliant!.jpg

THANK YOU MCMAHOBT! Worked perfectly! I took a few minutes to break down what the formula was "saying" so I could understand what references to change in order to fit where I needed it and viola, numbers in place of errors! Very happy to now be able to continue on without having to use a work around that would continue to bother me.

Thank you again FDibbins and mcmahobt for the help! For a novice Excel guy this forum is a great resource/tool for learning and improving my skills.

[FDibbins]

Love the pic, although some may not be able to view it. Happy to help and thanks for the feedback