changing a cell to changing the link

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" <Jared@discussions.microsoft.com> a écrit dans le message de news:
646366E5-AEEF-43B4-8EF6-A16333A8D40D@microsoft.com...
>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" <Jared@discussions.microsoft.com> a écrit dans le message de news:
> 646366E5-AEEF-43B4-8EF6-A16333A8D40D@microsoft.com...
> >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

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

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