MS Excel Formula Help?

Question

I am using this formula to get a particular data from old book [=SUM(('G:\[november2007.xlsx]Sheet1'!W13))]. But every month need to change”month name” to get data from previous month. So Instead of this, I applied this in “CHOOSE FORMULA” as (=choose(a1,”=SUM(('G:\[december2007.xlsx]Sheet1'!W13))”), (=choose(a1,”=SUM(('G:\[january2008.xlsx]Sheet1'!W13))”), ………….. and so on for all 12 months. When I applied this formula, it will not working. How can I rectify the mistake or is there any alternative formula. Please help me. Thank you

Answer 1

Assume your workbook is made based on previous month workbook
To solve your problem when you start a new workbook every new month is, say in Decemeber 2007
1. open November2007.xlsx, save it as December2007.xlsx
2. ctrl+a to select the whole worksheet
3. ctrl+h to get replace window, enter [October2007.xlsx] in Find what: box and [November2007.xlsx] in Replace with: box, hit replace all button.
4. save the workbook again
Hope this helps.

Answer 2

Try this trick:

1. MANUALLY make a copy of the latest file and rename it to LastMonth2007.xlsx.

2. Use "LastMonth..." instead in your formula.

Then, repeat #1 each month, before running your next update.

Answer 3

try the indirect function
=IF(OR(A2="",A3="",A4=""),"",INDIRECT("'[" & A2 & ".xls]" & A3 & "'!" & A4))
place the workbook name in A2
sheet name in A3
cell address in A4
here's more info on indirect
http://www.contextures.com/xlFunctions05.html#RefWkbk