Register
Log In
Unanswered
Home
New Member
April 30, 2015
Offline
Hello,
Is it possible to insert a cell reference within the sheet name of a formula?
Example,
Workbook 1
Sheets Canada US Mexico etc.
Workbook 2
Columns Canada US Mexico, etc.
Would like to be able to reference the column names to a cell in workbook 2 from the corresponding tab in workbook 1.
='[Workbook 1]Canada'!$J20, can I replace the sheet name with a cell reference? I have many countries to pull over, and I would rather not reference them all individually. Hope I am being clear. Is there another way?
Thanks
Dashboards
June 25, 2016
Offline
Hi Julie
Not too sure if I understood you but see if this is what you are looking for. In Workbook2 enter
=INDIRECT("[Workbook1.xlsx]"&C1&"!A1")
where C1 contains the word Canada.
This formula will refer to the cell A1 of the worksheet named Canada in file Workbook1.
Sunny
New Member
April 30, 2015
Offline
SunnyKow said
Hi JulieNot too sure if I understood you but see if this is what you are looking for. In Workbook2 enter
=INDIRECT("[Workbook1.xlsx]"&C1&"!A1")
where C1 contains the word Canada.
This formula will refer to the cell A1 of the worksheet named Canada in file Workbook1.
Sunny
Hello, Thank you for your suggestion. I believe It is exactly what I am looking for, however, it is returning #REF. Any suggestions?
Dashboards
June 25, 2016
Offline
Hi Julie
Make sure that the linked file HFM Hotel Revenue per Country B17.xlsx is open simultaneously.
When working with linked files, it is better to make sure that all related linked files are open simultaneously.
I normally do not recommend linking files as the problem associated with it can sometime out weight its benefit.
Sunny
Most Users Ever Online: 57
Currently Online:
1 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Susan Booze
Rita Hrez
David Innes
preeti bhulabhai
Charles Klasek
Patrick Isedu
Emma Hayward
Vamseedhar G
NA NA
Max Neumann
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
