Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing a range in a closed file
Hi.
I have a user who would like to be able to sum a range in a closed file. That in itself is possible, but it's a fiscal application, and he would like to be able to tell it how many months to sum, in a YTD manner... 1 for Jan, 2 for Feb, etc. In order to directly sum the 12 month range (in the closed file), the formula below works: =SUM('H:\PROJECTS\OVERHEAD_BUDGETS\Excel_Sheets\[ENG 2004 BUDGET.XLS]120'!$J3:U3) In order to try to make the number of columns flexible (j3:j3 for Jan, j3:k3 for Feb, etc.), I did this: =SUM(OFFSET('[ENG 2004 BUDGET.XLS]120'!$J3,0,0,1,VLOOKUP (rgMonth,tblMonth,2,0))) where rgMonth receives the month name, and tblMonth lets it calc a lookup for the number of columns to sum. That works, when the external file is open. However, if the external file is not open, the receiving file stops to ask if you want to update links (at this time, you can see that the correct numbers appear on the screen), but when you tell it not to update the links, it goes to #VALUE. I just discovered that if I use a third file, which links to the second file, which links to the original file, this third file will keep the number when told not to update links. But that's awfully roundabout. It appears to be the 'offset' function that is causing the cells to go to #VALUE, even when told not to update the links. Can anyone suggest a formula (it will go into many cells) that would be able to retain the linked data, without updating the links? Thanks. Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing a range of cells based on criteria in another range | Excel Worksheet Functions | |||
Help - Does value exist in range of CLOSED workbook? | Excel Worksheet Functions | |||
Excel is not asking to save a changed file when the file is closed | Excel Discussion (Misc queries) | |||
VBA - on a button event, open another closed file, post changes, close file | Excel Programming | |||
Problem with named Range in ADO extract from Closed Excel File | Excel Programming |