Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Picking up data from workbooks which may not exist yet
Hello
I've got a series of exisiting workbooks: Rota2008.07.21.xls, Rota2008.07.28 and there are some which WILL exist: Rota2008.08.04, Rota2008.08.11, etc. As is probably apparent, they're rotas named according to the week-commencing date. I want to pull in data from these workbooks on to a worksheet in another workbook which will show 2 of them, headed: 'Rota Current Week' and 'Rota Next Week'. So I know I'm going to have to have a cell somewhere saying 'If today is = Rotayyyy.mm.dd but less than Rotayyyy.mm.dd+6, get the data from that workbook and use it for 'Current Rota' and another similar one for 'Rota Next Week', but how do I refer and how do I get the data from the individual cells to come across? The individual Rotayyyy.mm.dd workbooks will always be closed. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Picking up data from workbooks which may not exist yet
I think you will have to re-think how you are going to do this.
Normally, you would use the INDIRECT function to build up a cell or range reference as a string and this can be accepted by other functions, so that is how you would reference the other sheets/files. However, INDIRECT can only work with open files. Hope this helps. Pete On Jul 30, 9:50*pm, robzrob wrote: Hello I've got a series of exisiting workbooks: Rota2008.07.21.xls, Rota2008.07.28 and there are some which WILL exist: Rota2008.08.04, Rota2008.08.11, etc. *As is probably apparent, they're rotas named according to the week-commencing date. *I want to pull in data from these workbooks on to a worksheet in another workbook which will show 2 of them, headed: 'Rota Current Week' and 'Rota Next Week'. *So I know I'm going to have to have a cell somewhere saying 'If today is = Rotayyyy.mm.dd but less than Rotayyyy.mm.dd+6, get the data from that workbook and use it for 'Current Rota' and another similar one for 'Rota Next Week', but how do I refer and how do I get the data from the individual cells to come across? *The individual Rotayyyy.mm.dd workbooks will always be closed. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Picking up data from workbooks which may not exist yet
On Jul 30, 10:40*pm, Pete_UK wrote:
I think you will have to re-think how you are going to do this. Normally, you would use the INDIRECT function to build up a cell or range reference as a string and this can be accepted by other functions, so that is how you would reference the other sheets/files. However, INDIRECT can only work with open files. Hope this helps. Pete On Jul 30, 9:50*pm, robzrob wrote: Hello I've got a series of exisiting workbooks: Rota2008.07.21.xls, Rota2008.07.28 and there are some which WILL exist: Rota2008.08.04, Rota2008.08.11, etc. *As is probably apparent, they're rotas named according to the week-commencing date. *I want to pull in data from these workbooks on to a worksheet in another workbook which will show 2 of them, headed: 'Rota Current Week' and 'Rota Next Week'. *So I know I'm going to have to have a cell somewhere saying 'If today is = Rotayyyy.mm.dd but less than Rotayyyy.mm.dd+6, get the data from that workbook and use it for 'Current Rota' and another similar one for 'Rota Next Week', but how do I refer and how do I get the data from the individual cells to come across? *The individual Rotayyyy.mm.dd workbooks will always be closed.- Hide quoted text - - Show quoted text - Hello Pete Thought somebody might say that. Would the answer be at the end of a road something like this: Set up the worksheet using the INDIRECT function, then VBA code thus: Open the 2 files I'm interested in (this week and next week) whenever the particular worksheet in my new workbook is selected, update, close the 2 files. What do you think? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Picking up data from workbooks which may not exist yet
That sounds as if it will work, though you will have to fix the values
before closing the 2 workbooks. A similar approach would be to open each workbook in turn, then move the sheet into the file containing the macro thereby automatically closing the rota workbook (unchanged), and then the macro could continue by establishing the formulae for you. The new sheets could be renamed This_week and Last_week (or something similar), so the formulae will always refer to the same (internal) sheet names. You could also think about downloading the free add-in morefunc - this has an INDIRECT.EXT function which is meant to allow you to get data from closed files. I don't have it so I can't comment on it, but I've seen lots of recommendations in other posts. Hope this helps. Pete On Jul 30, 10:53*pm, robzrob wrote: Hello Pete Thought somebody might say that. *Would the answer be at the end of a road something like this: Set up the worksheet using the INDIRECT function, then VBA code thus: Open the 2 files I'm interested in (this week and next week) whenever the particular worksheet in my new workbook is selected, update, close the 2 files. *What do you think? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Picking Data from another sheet | Excel Discussion (Misc queries) | |||
How to I create picking another sheet data with 2 different cell | Excel Worksheet Functions | |||
Picking the list from data | Excel Worksheet Functions | |||
What's command for find sheets exist in workbooks? | Excel Worksheet Functions | |||
Picking data | Excel Worksheet Functions |