Pulling a value from another worksheet based on worksheet name in a cell

Jay19

New Member
Joined
Jan 21, 2014
Messages
12
Hi all

I'm trying to pull the value in cell J62 of another worksheet. The worksheet name is in B40 of the same worksheet where I have my formula. Essentially I am trying to pull the worksheet name and add it to the cell number to create a formula that pulls that value in another sheet. Here's the formula I'm trying but its not working:

=CELL("contents","'"&"B40"&"'!"&$J$62))

B40 contains the workbook name: "Fox Trails"

So I want the formula to evaluate as
=CELL("contents",'Fox Trails'!$J$62)
and return the value I have listed in J62 of the Fox Trails worksheet.

I don't have to use the cell function, I just thought that was easiest. I also tried
="'"&B40&"'!"&"$J$62"
but that just gives me the text 'Fox Trails'!$J$62

<tbody>
</tbody>

Thanks
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Put that inside INDIRECT

=INDIRECT("'"&B40&"'!"&"$J$62")


And actually, it's a little overboard with the concatenation...
Could be just
=INDIRECT("'"&B40&"'!$J$62")
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top