I have a cell refering to another workbook. example: April.xls
I want to change all cells with that refrence but without changing each one
individually, so i created a cell which i input the current month. How do i
link it to all other cells so instead of getting information from april.xls
it will get information from july.xls

examle formula:
=[April.xls]Sierra'!H7

how do i enter in this formula the cell no. instead of april?

If current month is in A1:

=INDIRECT("["&A1&"]Sierra!H7")

HTH
--
AP

"Jared" a écrit dans le message de news:
...
I have a cell refering to another workbook. example: April.xls
I want to change all cells with that refrence but without changing each
one
individually, so i created a cell which i input the current month. How do
i
link it to all other cells so instead of getting information from
april.xls
it will get information from july.xls

examle formula:
=[April.xls]Sierra'!H7

how do i enter in this formula the cell no. instead of april?

I did try this function but it gave me an error

maybe because it is a different workbook?

Waiting for a different approach

thanks

"Ardus Petus" wrote:

If current month is in A1:

=INDIRECT("["&A1&"]Sierra!H7")

HTH
--
AP

"Jared" a écrit dans le message de news:
...
I have a cell refering to another workbook. example: April.xls
I want to change all cells with that refrence but without changing each
one
individually, so i created a cell which i input the current month. How do
i
link it to all other cells so instead of getting information from
april.xls
it will get information from july.xls

examle formula:
=[April.xls]Sierra'!H7

how do i enter in this formula the cell no. instead of april?

select all cells that contain formula you want to change.
press Ctrl+H
Find what: April
Replace with: July
then press Replace all


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=539193

It would be only good for one sheet, but doing it for over 7 sheets becomes a
hassle for every month.

All i wanted to do is change the contents of one cell which would be linked
to all the other cells.


"starguy" wrote:


select all cells that contain formula you want to change.
press Ctrl+H
Find what: April
Replace with: July
then press Replace all


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=539193

you can change it for all 7 sheets at a time.
Find what: April
Replace with: July
click "Options " tab. it will expand window
select
within: workbook
then press Replace all

does it work..?


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=539193

It actually does work.
This a good temporary solution.

When i have novice users filling out this sheet i wouldn't want them to get
all mixed up with this kind of process. I will have to limit there option
which probably will include this one.

I will still be looking for a link formula to a specific cell which only by
changing the contents of it will cause the link to change to a different
workbook.

And so you understand:
If the cell contents = "April" then the links will be to
[April.xls]Sierra!A6
and when i change the cell to "May" then all the links will change to
[May.xls]Sierra!A6


but thanks anyway

"starguy" wrote:


you can change it for all 7 sheets at a time.
Find what: April
Replace with: July
click "Options " tab. it will expand window
select
within: workbook
then press Replace all

does it work..?


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=539193

Ctrl+H
in Find what: [April.xls]Sierra!A6
in Replace with: =INDIRECT("["&A1&"]Sierra!A6")
click Options
within: workbook
press Replace All

here in INDIRECT formula A1 is the cell that contains workbook name.
this will work only when you have opened the workbook which you have
referenced to.
suppose you enter July in A1 and press enter, this will give #REF error
if you have not opened workbook naming July.
first open workbook "July" then change the content of cell A1 to
"July".

hope this will solve your problem.

Jared Wrote:
It actually does work.
This a good temporary solution.

When i have novice users filling out this sheet i wouldn't want them to
get
all mixed up with this kind of process. I will have to limit there
option
which probably will include this one.

I will still be looking for a link formula to a specific cell which
only by
changing the contents of it will cause the link to change to a
different
workbook.

And so you understand:
If the cell contents = "April" then the links will be to
[April.xls]Sierra!A6
and when i change the cell to "May" then all the links will change to
[May.xls]Sierra!A6


but thanks anyway

"starguy" wrote:


you can change it for all 7 sheets at a time.
Find what: April
Replace with: July
click "Options " tab. it will expand window
select
within: workbook
then press Replace all

does it work..?


--
starguy

------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread:
http://www.excelforum.com/showthread...hreadid=539193




--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=539193