Use Worksheet Name in a Formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I need to try and automate the updating of a spreadsheet as much as possible
and would like to do the following... Does anyone know if this is possible?

Basically I have a workbook called 'Scorecard.xls' - it has worksheets named
after markets (e.g. UK, Austria etc) and I need to pull data from another
worksheet named 'Results.xls' which also has equally named market worksheets
(e.g. UK, Austria etc).

So, for example I have the following formula in a cell on the UK worksheet
in 'Scorecard.xls'.

='C:\Files\[Results.xls]UK'!$R$30

I need to replicate this for many markets and would love to just duplicate
the worksheet and change the market name in cell A1 (e.g. it would read UK)
and it would use this to point to the correct market worksheet in the
Results.xls workbook.

Any ideas how I can do this?

Thanks,
 
Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi,
|
| I need to try and automate the updating of a spreadsheet as much as possible
| and would like to do the following... Does anyone know if this is possible?
|
| Basically I have a workbook called 'Scorecard.xls' - it has worksheets named
| after markets (e.g. UK, Austria etc) and I need to pull data from another
| worksheet named 'Results.xls' which also has equally named market worksheets
| (e.g. UK, Austria etc).
|
| So, for example I have the following formula in a cell on the UK worksheet
| in 'Scorecard.xls'.
|
| ='C:\Files\[Results.xls]UK'!$R$30
|
| I need to replicate this for many markets and would love to just duplicate
| the worksheet and change the market name in cell A1 (e.g. it would read UK)
| and it would use this to point to the correct market worksheet in the
| Results.xls workbook.
|
| Any ideas how I can do this?
|
| Thanks,
 
Ok - I have taken a look, but i'm still not sure how I use this within my
current formula?

Can you advise where I can position this function within this formula to
replace the UK with the contents of cell A1?

='C:\Files\[Results.xls]UK'!$R$30

Thanks!
 
"='C:\Files\[Results.xls]" & A1 & "'!$R$30"

Louise said:
Ok - I have taken a look, but i'm still not sure how I use this within my
current formula?

Can you advise where I can position this function within this formula to
replace the UK with the contents of cell A1?

='C:\Files\[Results.xls]UK'!$R$30

Thanks!


Niek Otten said:
Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi,
|
| I need to try and automate the updating of a spreadsheet as much as possible
| and would like to do the following... Does anyone know if this is possible?
|
| Basically I have a workbook called 'Scorecard.xls' - it has worksheets named
| after markets (e.g. UK, Austria etc) and I need to pull data from another
| worksheet named 'Results.xls' which also has equally named market worksheets
| (e.g. UK, Austria etc).
|
| So, for example I have the following formula in a cell on the UK worksheet
| in 'Scorecard.xls'.
|
| ='C:\Files\[Results.xls]UK'!$R$30
|
| I need to replicate this for many markets and would love to just duplicate
| the worksheet and change the market name in cell A1 (e.g. it would read UK)
| and it would use this to point to the correct market worksheet in the
| Results.xls workbook.
|
| Any ideas how I can do this?
|
| Thanks,
Click to expand...
Click to expand...
 
=INDIRECT("C:\Files\[Results]"&A1&"!$R$30")

Since you don't have spaces in your file name (Good!) you don't need the apostrophes

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Ok - I have taken a look, but i'm still not sure how I use this within my
| current formula?
|
| Can you advise where I can position this function within this formula to
| replace the UK with the contents of cell A1?
|
| ='C:\Files\[Results.xls]UK'!$R$30
|
| Thanks!
|
|
| "Niek Otten" wrote:
|
| > Look in HELP for the INDIRECT() function
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > | Hi,
| > |
| > | I need to try and automate the updating of a spreadsheet as much as possible
| > | and would like to do the following... Does anyone know if this is possible?
| > |
| > | Basically I have a workbook called 'Scorecard.xls' - it has worksheets named
| > | after markets (e.g. UK, Austria etc) and I need to pull data from another
| > | worksheet named 'Results.xls' which also has equally named market worksheets
| > | (e.g. UK, Austria etc).
| > |
| > | So, for example I have the following formula in a cell on the UK worksheet
| > | in 'Scorecard.xls'.
| > |
| > | ='C:\Files\[Results.xls]UK'!$R$30
| > |
| > | I need to replicate this for many markets and would love to just duplicate
| > | the worksheet and change the market name in cell A1 (e.g. it would read UK)
| > | and it would use this to point to the correct market worksheet in the
| > | Results.xls workbook.
| > |
| > | Any ideas how I can do this?
| > |
| > | Thanks,
| >
| >
| >
 
Sorry to ask, but if I did have spaces in my filename where would I be
inserting apostrophes?

Thanks :)
 
Where you have them now (I think, I always avoid spaces in File names and Sheet names).

Just try!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Sorry to ask, but if I did have spaces in my filename where would I be
| inserting apostrophes?
|
| Thanks :)
|
| "Niek Otten" wrote:
|
| > =INDIRECT("C:\Files\[Results]"&A1&"!$R$30")
| >
| > Since you don't have spaces in your file name (Good!) you don't need the apostrophes
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| >
| > | Ok - I have taken a look, but i'm still not sure how I use this within my
| > | current formula?
| > |
| > | Can you advise where I can position this function within this formula to
| > | replace the UK with the contents of cell A1?
| > |
| > | ='C:\Files\[Results.xls]UK'!$R$30
| > |
| > | Thanks!
| > |
| > |
| > | "Niek Otten" wrote:
| > |
| > | > Look in HELP for the INDIRECT() function
| > | >
| > | > --
| > | > Kind regards,
| > | >
| > | > Niek Otten
| > | > Microsoft MVP - Excel
| > | >
| > | > | Hi,
| > | > |
| > | > | I need to try and automate the updating of a spreadsheet as much as possible
| > | > | and would like to do the following... Does anyone know if this is possible?
| > | > |
| > | > | Basically I have a workbook called 'Scorecard.xls' - it has worksheets named
| > | > | after markets (e.g. UK, Austria etc) and I need to pull data from another
| > | > | worksheet named 'Results.xls' which also has equally named market worksheets
| > | > | (e.g. UK, Austria etc).
| > | > |
| > | > | So, for example I have the following formula in a cell on the UK worksheet
| > | > | in 'Scorecard.xls'.
| > | > |
| > | > | ='C:\Files\[Results.xls]UK'!$R$30
| > | > |
| > | > | I need to replicate this for many markets and would love to just duplicate
| > | > | the worksheet and change the market name in cell A1 (e.g. it would read UK)
| > | > | and it would use this to point to the correct market worksheet in the
| > | > | Results.xls workbook.
| > | > |
| > | > | Any ideas how I can do this?
| > | > |
| > | > | Thanks,
| > | >
| > | >
| > | >
| >
| >
| >
 
The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.
Hi,

I need to try and automate the updating of a spreadsheet as much as possible
and would like to do the following... Does anyone know if this is possible?

Basically I have a workbook called 'Scorecard.xls' - it has worksheets named
after markets (e.g. UK, Austria etc) and I need to pull data from another
worksheet named 'Results.xls' which also has equally named market worksheets
(e.g. UK, Austria etc).

So, for example I have the following formula in a cell on the UK worksheet
in 'Scorecard.xls'.

='C:\Files\[Results.xls]UK'!$R$30

I need to replicate this for many markets and would love to just duplicate
the worksheet and change the market name in cell A1 (e.g. it would read UK)
and it would use this to point to the correct market worksheet in the
Results.xls workbook.

Any ideas how I can do this?

Thanks,
Click to expand...
 
Back
Top