Column Reference to External Source As a Variable

[Guest]

Can anyone help me convert the column referenced in the formula below into a variable that the user can define?

More specifically, I have several columns that I need to read from an external workbook (Short_Billy.xls). Each column to the right of column C represents an additional day out in a 14 day projection from today (whose data is held in column C).

In cell I5 of my active workbook (Inventory.xls), I would like the user to be able to enter a value representing the number of days out they would like to see the projection for (0=today=Column C, 1=Tomorrow=Column D, etc.). In cell I6, I now have the following formula without the variable I'm looking for =SUM([SHORT_BILLY.xls]HADDOCK!$C$15:$C$15).

I have several, non-contiguous rows that I must read from so I'm thinking an array is NOT the way to go. Rather, I suspect my solution hinges on either the INDIRECT or OFFSET Functions, but I can't seem to get the syntax right. Any help would be extremely appreciated.

Thank you,
Fred

 

[Bob Phillips]

Fred,

Haven't tested it fully I am afraid, but try this

=SUM(INDIRECT("[SHORT_BILLY.xls]HADDOCK!$C$15:$"&CHAR(I5+64+3)&"$15"))

There will be a problem if you go to more than 23 days.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Can anyone help me convert the column referenced in the formula below into

a variable that the user can define?

More specifically, I have several columns that I need to read from an

external workbook (Short_Billy.xls). Each column to the right of column C
represents an additional day out in a 14 day projection from today (whose
data is held in column C).

In cell I5 of my active workbook (Inventory.xls), I would like the user

to be able to enter a value representing the number of days out they would
like to see the projection for (0=today=Column C, 1=Tomorrow=Column D,
etc.). In cell I6, I now have the following formula without the variable
I'm looking for =SUM([SHORT_BILLY.xls]HADDOCK!$C$15:$C$15).

I have several, non-contiguous rows that I must read from so I'm thinking

an array is NOT the way to go. Rather, I suspect my solution hinges on
either the INDIRECT or OFFSET Functions, but I can't seem to get the syntax
right. Any help would be extremely appreciated.

 

[Peo Sjoblom]

Note that the referenced workbook has to be open or else you'll get a ref
error for indirect
or a value error for offset

=SUM(OFFSET([SHORT_BILLY.xls]HADDOCK!$C$15,,,,I6))

--

Regards,

Peo Sjoblom

Can anyone help me convert the column referenced in the formula below into

a variable that the user can define?

More specifically, I have several columns that I need to read from an

external workbook (Short_Billy.xls). Each column to the right of column C
represents an additional day out in a 14 day projection from today (whose
data is held in column C).

In cell I5 of my active workbook (Inventory.xls), I would like the user

to be able to enter a value representing the number of days out they would
like to see the projection for (0=today=Column C, 1=Tomorrow=Column D,
etc.). In cell I6, I now have the following formula without the variable
I'm looking for =SUM([SHORT_BILLY.xls]HADDOCK!$C$15:$C$15).

I have several, non-contiguous rows that I must read from so I'm thinking

an array is NOT the way to go. Rather, I suspect my solution hinges on
either the INDIRECT or OFFSET Functions, but I can't seem to get the syntax
right. Any help would be extremely appreciated.

 

[Guest]

Dear Bob & Peo,

Thank you very much for your suggestions. Thanks to you both, I have solved at least this problem. I sincerely appreciate it.

Best Regards,
Fred