| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Worksheet Name in a Formula
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, |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use Worksheet Name in a Formula
Look in HELP for the INDIRECT() function
-- Kind regards, Niek Otten Microsoft MVP - Excel "Louise" wrote in message ... | 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, |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use Worksheet Name in a Formula
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 "Louise" wrote in message ... | 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, |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use Worksheet Name in a Formula
"='C:\Files\[Results.xls]" & A1 & "'!$R$30"
"Louise" wrote: 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 "Louise" wrote in message ... | 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, |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use Worksheet Name in a Formula
=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 "Louise" wrote in message ... | 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 | | "Louise" wrote in message ... | | 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, | | | |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use Worksheet Name in a Formula
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 "Louise" wrote in message ... | 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 | | "Louise" wrote in message ... | | 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, | | | |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use Worksheet Name in a Formula
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 "Louise" wrote in message ... | 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 | | | "Louise" wrote in message ... | | 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 | | | | "Louise" wrote in message ... | | | 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, | | | | | | | | | |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use Worksheet Name in a Formula
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. Louise wrote: 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, -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Worksheet formula incorrect after copying to another worksheet | Excel Worksheet Functions | |||
| Formula for advance worksheet name to next worksheet name | Excel Worksheet Functions | |||
| How do I take a cell in one worksheet and add it into the formula in a different worksheet in the same workbook? | Excel Worksheet Functions | |||
| Fill a column in a worksheet from a formula worksheet based on the date | Excel Programming | |||
| formula/function to copy from worksheet to worksheet | Excel Programming | |||
Linear Mode
