Excel - referencing ext sheet using text in cell
20 October 2007, 12:53 PM
#1
Scooby Regular
Thread Starter
Join Date: Feb 2004
Posts: 5,848
Likes: 0
Received 0 Likes
on
0 Posts
Excel - referencing ext sheet using text in cell
Probably simple but got me stumped, can anyone help?
Have a workbook with several worksheets (sheet1, sheet2, sheet3 etc)
Want a summary sheet to do some calcs but want to reference the sheet names from cells on the summary sheet. (Because of formats and calcs, don't want to do pivot).
So usually the reference would be something like:
But instead of:
I want to use a text value from a cell on the summary sheet but can't get that to work despite trying a few things like:
where C2 contains the name of the sheet I want to reference.
Can it be done?
Have a workbook with several worksheets (sheet1, sheet2, sheet3 etc)
Want a summary sheet to do some calcs but want to reference the sheet names from cells on the summary sheet. (Because of formats and calcs, don't want to do pivot).
So usually the reference would be something like:
Code:
='Sheet1'!A1
'Sheet1'!
='TEXT(C2)'!A1
Can it be done?
22 October 2007, 09:40 AM
#2
Moderator
iTrader: (5)
Join Date: Nov 2001
Location: Not all those who wander are lost
Posts: 17,863
Received 0 Likes
on
0 Posts
Hello mate,
=INDIRECT(C2&"!A1")
Should do it
Basically it concatenates what's in C2 (eg. the text "Sheet2"), and concatenates it to the string "!A1". You'll then end up with this string: "Sheet2!A1"
HTH
=INDIRECT(C2&"!A1")
Should do it
Basically it concatenates what's in C2 (eg. the text "Sheet2"), and concatenates it to the string "!A1". You'll then end up with this string: "Sheet2!A1"HTH
22 October 2007, 09:21 PM
#3
Scooby Regular
Thread Starter
Join Date: Feb 2004
Posts: 5,848
Likes: 0
Received 0 Likes
on
0 Posts
Absolute genius, certainly helps!
Thanks very much, works a treat!!!
Adding new sheets and an extra line on the summary page is now a piece of cake.
Now fully deployed in formulae like this
=COUNTIF(INDIRECT("'"&A4&"'!C:W"),"X")
TVM, consider me very grateful
Thanks very much, works a treat!!!
Adding new sheets and an extra line on the summary page is now a piece of cake.
Now fully deployed in formulae like this
=COUNTIF(INDIRECT("'"&A4&"'!C:W"),"X")
TVM, consider me very grateful
Thread
Thread Starter
Forum
Replies
Last Post
Mattybr5@MB Developments
Full Cars Breaking For Spares
28
28 December 2015 11:07 PM
Mattybr5@MB Developments
Full Cars Breaking For Spares
12
18 November 2015 07:03 AM
alcazar
Computer & Technology Related
2
29 September 2015 07:18 PM