I have a master calculation file which pulls data from a monthly data file. I
would like to use indirect addressing so I anoly change one cell each month
to point to the correct spreadsheet. I can use CONCATENATE to create
something that looks like the correct command, but I cannot make it an
executable statement to actually pull the data - it is text. Any ideas?

could you provide some example?


On 11 Gru, 21:55, ChEBass wrote:
I have a master calculation file which pulls data from a monthly data file. I
would like to use indirect addressing so I anoly change one cell each month
to point to the correct spreadsheet. I can use CONCATENATE to create
something that looks like the correct command, but I cannot make it an
executable statement to actually pull the data - it is text. Any ideas?

These are cells from the spreadsheet as it is:
='[hourly steam December 2008.xls]Summary'!c34
='[hourly steam December 2008.xls]Summary'!c35
='[hourly steam December 2008.xls]Summary'!c36
....

I would like to set it up so I only put the month name in one cell and all
the links change.



"Jarek Kujawa" wrote:

could you provide some example?


On 11 Gru, 21:55, ChEBass wrote:
I have a master calculation file which pulls data from a monthly data file. I
would like to use indirect addressing so I anoly change one cell each month
to point to the correct spreadsheet. I can use CONCATENATE to create
something that looks like the correct command, but I cannot make it an
executable statement to actually pull the data - it is text. Any ideas?

After seeing your example...

=INDIRECT("'[hourly steam "&B2&" 2008.xls]Summary'!"&c34)

where B2 contains the month you want.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"ChEBass" wrote:

These are cells from the spreadsheet as it is:
='[hourly steam December 2008.xls]Summary'!c34
='[hourly steam December 2008.xls]Summary'!c35
='[hourly steam December 2008.xls]Summary'!c36
...

I would like to set it up so I only put the month name in one cell and all
the links change.



"Jarek Kujawa" wrote:

could you provide some example?


On 11 Gru, 21:55, ChEBass wrote:
I have a master calculation file which pulls data from a monthly data file. I
would like to use indirect addressing so I anoly change one cell each month
to point to the correct spreadsheet. I can use CONCATENATE to create
something that looks like the correct command, but I cannot make it an
executable statement to actually pull the data - it is text. Any ideas?

It didn't work but I played with the theme a little and got something that
does work.
first I created the path with CONCATENATE
=CONCATENATE("'[hourly steam "&B2&" 2008.xls]Summary'!C34")
then I reference that cell with INDIRECT and it pulls the data. Not quite as
clean as I would like but it works.

Thanks for the help.

"Luke M" wrote:

After seeing your example...

=INDIRECT("'[hourly steam "&B2&" 2008.xls]Summary'!"&c34)

where B2 contains the month you want.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"ChEBass" wrote:

These are cells from the spreadsheet as it is:
='[hourly steam December 2008.xls]Summary'!c34
='[hourly steam December 2008.xls]Summary'!c35
='[hourly steam December 2008.xls]Summary'!c36
...

I would like to set it up so I only put the month name in one cell and all
the links change.



"Jarek Kujawa" wrote:

could you provide some example?


On 11 Gru, 21:55, ChEBass wrote:
I have a master calculation file which pulls data from a monthly data file. I
would like to use indirect addressing so I anoly change one cell each month
to point to the correct spreadsheet. I can use CONCATENATE to create
something that looks like the correct command, but I cannot make it an
executable statement to actually pull the data - it is text. Any ideas?

I think you want the INDIRECT formula.

something like:
=INDIRECT('[Book1.xls]&B2&"$C$2")

would return the value of cell C2 from Book1.xls, whatever sheet you have
the name of in cell B2.

Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"ChEBass" wrote:

I have a master calculation file which pulls data from a monthly data file. I
would like to use indirect addressing so I anoly change one cell each month
to point to the correct spreadsheet. I can use CONCATENATE to create
something that looks like the correct command, but I cannot make it an
executable statement to actually pull the data - it is text. Any ideas?