Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula referring to a dynamic range in a different workbook
I've got two workbooks.
One has ranges which will grow as time passes. Because of this, I've set up dynamic ranges. The other one is a dashboard, which needs to VLOOKUP the contents of the dynamic ranges in the other workbook. It works if both workbooks are open, but if the one containing the raw data is closed, the dashboard simply shows #VALUE! Any ideas how I can get around this? (Excel 2002) Cheers, Tom. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula referring to a dynamic range in a different workbook
That's one of the shortcomings of VLOOKUP() using a table in another
workbook; both books have to be open to get updated data. I'm not certain there's a good work-around for it. One way (which almost defeats the effort you've gone to in setting up the dynamic range in that other workbook) would be to have a sheet in the main workbook that has direct cell links to the cells in that other workbook and set our VLOOKUP() formulas to check those cells. But this takes either manual intervention to update the links to make sure that all of the used cells in the named range are linked to in the first workbook, or it takes an overkill setup where you set the links to the cells in the table along with more links below/to right of the table to allow for growth. Potentially you could set up some code in the workbook that has your dynamic range in it to update those links in the primary book as data is added to the dynamic range (it would have to open the other workbook and set up added links). Another alternative would be to have the dashboard containing workbook check to see if the workbook with the dynamic range in it is open, and if not, then open it up so that the dashboard works properly. It could have code in the _BeforeClose event to also close that other workbook when it was closed. Someone else may have a better solution to this than what I've suggested. "mr tom" wrote: I've got two workbooks. One has ranges which will grow as time passes. Because of this, I've set up dynamic ranges. The other one is a dashboard, which needs to VLOOKUP the contents of the dynamic ranges in the other workbook. It works if both workbooks are open, but if the one containing the raw data is closed, the dashboard simply shows #VALUE! Any ideas how I can get around this? (Excel 2002) Cheers, Tom. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula referring to a dynamic range in a different workbook
Thank you.
I'll not tick 'answered' just yet because I really hope somebody has a more attractive answer, but thank you all the same! Tom. "JLatham" wrote: That's one of the shortcomings of VLOOKUP() using a table in another workbook; both books have to be open to get updated data. I'm not certain there's a good work-around for it. One way (which almost defeats the effort you've gone to in setting up the dynamic range in that other workbook) would be to have a sheet in the main workbook that has direct cell links to the cells in that other workbook and set our VLOOKUP() formulas to check those cells. But this takes either manual intervention to update the links to make sure that all of the used cells in the named range are linked to in the first workbook, or it takes an overkill setup where you set the links to the cells in the table along with more links below/to right of the table to allow for growth. Potentially you could set up some code in the workbook that has your dynamic range in it to update those links in the primary book as data is added to the dynamic range (it would have to open the other workbook and set up added links). Another alternative would be to have the dashboard containing workbook check to see if the workbook with the dynamic range in it is open, and if not, then open it up so that the dashboard works properly. It could have code in the _BeforeClose event to also close that other workbook when it was closed. Someone else may have a better solution to this than what I've suggested. "mr tom" wrote: I've got two workbooks. One has ranges which will grow as time passes. Because of this, I've set up dynamic ranges. The other one is a dashboard, which needs to VLOOKUP the contents of the dynamic ranges in the other workbook. It works if both workbooks are open, but if the one containing the raw data is closed, the dashboard simply shows #VALUE! Any ideas how I can get around this? (Excel 2002) Cheers, Tom. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula referring to a dynamic range in a different workbook
How about a defined name in the destination workbook that refers to the
source workbook. -- Don Guillett SalesAid Software "mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message ... I've got two workbooks. One has ranges which will grow as time passes. Because of this, I've set up dynamic ranges. The other one is a dashboard, which needs to VLOOKUP the contents of the dynamic ranges in the other workbook. It works if both workbooks are open, but if the one containing the raw data is closed, the dashboard simply shows #VALUE! Any ideas how I can get around this? (Excel 2002) Cheers, Tom. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula referring to a dynamic range in a different workbook
Wow.
That's a very good idea. I'll give it a try. Cheers, Tom. "Don Guillett" wrote: How about a defined name in the destination workbook that refers to the source workbook. -- Don Guillett SalesAid Software "mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message ... I've got two workbooks. One has ranges which will grow as time passes. Because of this, I've set up dynamic ranges. The other one is a dashboard, which needs to VLOOKUP the contents of the dynamic ranges in the other workbook. It works if both workbooks are open, but if the one containing the raw data is closed, the dashboard simply shows #VALUE! Any ideas how I can get around this? (Excel 2002) Cheers, Tom. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula referring to a dynamic range in a different workbook
Sorry. Works when the source sheet is open but not closed.
I suspect it's because with full paths included, the refs go beyond the 255 character limit for a range name, so I tried it with the source sheet open and then closed it in the hope that the paths would update. Will see if I can arrange some space closer to the root to allow shorter path lengths... "Don Guillett" wrote: How about a defined name in the destination workbook that refers to the source workbook. -- Don Guillett SalesAid Software "mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message ... I've got two workbooks. One has ranges which will grow as time passes. Because of this, I've set up dynamic ranges. The other one is a dashboard, which needs to VLOOKUP the contents of the dynamic ranges in the other workbook. It works if both workbooks are open, but if the one containing the raw data is closed, the dashboard simply shows #VALUE! Any ideas how I can get around this? (Excel 2002) Cheers, Tom. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula referring to a dynamic range in a different workbook
Nope.
Defining the named range as an external sheet has the same effect. Good try, though! mr tom. "Don Guillett" wrote: How about a defined name in the destination workbook that refers to the source workbook. -- Don Guillett SalesAid Software "mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message ... I've got two workbooks. One has ranges which will grow as time passes. Because of this, I've set up dynamic ranges. The other one is a dashboard, which needs to VLOOKUP the contents of the dynamic ranges in the other workbook. It works if both workbooks are open, but if the one containing the raw data is closed, the dashboard simply shows #VALUE! Any ideas how I can get around this? (Excel 2002) Cheers, Tom. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
array formula with a dynamic range. | Excel Worksheet Functions | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Referring to function in another workbook | Excel Worksheet Functions | |||
Setting dynamic range in a formula | Excel Worksheet Functions | |||
formula to set up dynamic range in names | Excel Worksheet Functions |