Dynamic Column Selection in Formula

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

Hi

I have a simple function as below which selects a range from last years
Workbook:

=SUM('C:\Documents and
Settings\username\Desktop\[Workbook05.xls]Invoices'!B4:I4)

Each month the Second column is updated: i.e. next month I4 will become J4.

Instead of updating the formula I want to be able to update another Cell
with the text "I" to "J". E.G. cell A1 contains "I", refer to this cell in
the formula B4:I4 rather than hardcoding it.

Any advice would be much appreciated.

Thanks

B
 
Try
=SUM(INDIRECT("'C:\Documents and
Settings\username\Desktop\[Workbook05.xls]Invoices'!B4:"&A1&"4")

Note the I in I4 is replaced by a reference to cell A1
Do be aware that the file referred to must be open
best wishes
 
Hi Bernard

Thank you for your post.

Thats ideal except is there a method of doing this without requireing the
other book to be open?

Thanks
B
 
Back
Top