changing a cell to changing the link

J

Jared

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?
 
J

Jared

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 said:
If current month is in A1:

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

HTH
--
AP

Jared said:
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?
Click to expand...
Click to expand...
 
S

starguy

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

Jared

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.
 
S

starguy

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..
 
J

Jared

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
 
S

starguy

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 hav
referenced to.
suppose you enter July in A1 and press enter, this will give #REF erro
if you have not opened workbook naming July.
first open workbook "July" then change the content of cell A1 t
"July".

hope this will solve your problem.
It actually does work.
This a good temporary solution.

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

I will still be looking for a link formula to a specific cell whic
only by
changing the contents of it will cause the link to change to
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
Click to expand...
 
Top