AutoFill Linked Cells

[[email protected]]

Hi,
I have a cell that is linked to another sheet in the same workbook;
let's say the reference is =Jan!$B$21. I want the cell next to that to
be =Feb!$B$21.

Is there any way to use AutoFill? This would save a huge amount of time
for me rather than Copy-->PasteSpecial-->PasteLink
All help appreciated in advance

[Arien]

You can use the indirect formula

=INDIRECT(A1&"!"&A2)
where A1 = jan or feb (sheetname)
and A2 = $B$21 (reference)

regards,
Arien

[Max]

Try using INDIRECT on an autofilled row ..

Put in say, C10: Jan
Drag C10 across to autofill: Feb, Mar, etc in D10, E10, etc

Then put in C11: =INDIRECT("'"&C10&"'!B21")
Copy C11 across

C11 will return the same result as: =Jan!$B$21
D11 will return the same result as: =Feb!$B$21
and so on
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
<[email protected]> wrote in message
news:[email protected]...
> Hi,
> I have a cell that is linked to another sheet in the same workbook;
> let's say the reference is =Jan!$B$21. I want the cell next to that to
> be =Feb!$B$21.
>
> Is there any way to use AutoFill? This would save a huge amount of time
> for me rather than Copy-->PasteSpecial-->PasteLink
> All help appreciated in advance
>

[GregR]

Max, how is this formula modified to include a workbook name and the year is
added to the month. For instance the intial formula is:

='[SEA Oct04.xls]SCCS'!$J173

and dragging the formula will produce:

='[SEA Nov04.xls]SCCS'!$J173.

Is it =Indirect(""'[SEA &c10&04.xls]SCCS"'!$J173"). TIA

Greg
"Max" <[email protected]> wrote in message
news:O%[email protected]...
> Try using INDIRECT on an autofilled row ..
>
> Put in say, C10: Jan
> Drag C10 across to autofill: Feb, Mar, etc in D10, E10, etc
>
> Then put in C11: =INDIRECT("'"&C10&"'!B21")
> Copy C11 across
>
> C11 will return the same result as: =Jan!$B$21
> D11 will return the same result as: =Feb!$B$21
> and so on
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> > I have a cell that is linked to another sheet in the same workbook;
> > let's say the reference is =Jan!$B$21. I want the cell next to that to
> > be =Feb!$B$21.
> >
> > Is there any way to use AutoFill? This would save a huge amount of time
> > for me rather than Copy-->PasteSpecial-->PasteLink
> > All help appreciated in advance
> >
>
>

[Max]

Assuming the set-up is:

Listed in B1:B2 are the text: SEA, SCCS

Put in B2: Oct
Copy across to C3, etc to autofill: Nov, ...

Put in B3:
=INDIRECT("'["&$B$1&" "&B$2&"04.xls]"&$C$1&"'!J173")
Copy B3 across

The above will return the links you want

Or, if the sheetname "SCCS" doesn't have to be softcoded,
you could just put in B3:
=INDIRECT("'["&$B$1&" "&B$2&"04.xls]SCCS'!J173")
and copy across as before

Note that the "slave" books: SEA Oct04.xls, SEA Nov04.xls, etc
have to be open for INDIRECT to work,
otherwise you'll get #REF! errors
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"GregR" <[email protected]> wrote in message
news:#[email protected]...
> Max, how is this formula modified to include a workbook name and the year
is
> added to the month. For instance the intial formula is:
>
> ='[SEA Oct04.xls]SCCS'!$J173
>
> and dragging the formula will produce:
>
> ='[SEA Nov04.xls]SCCS'!$J173.
>
> Is it =Indirect(""'[SEA &c10&04.xls]SCCS"'!$J173"). TIA
>
> Greg
> "Max" <[email protected]> wrote in message
> news:O%[email protected]...
> > Try using INDIRECT on an autofilled row ..
> >
> > Put in say, C10: Jan
> > Drag C10 across to autofill: Feb, Mar, etc in D10, E10, etc
> >
> > Then put in C11: =INDIRECT("'"&C10&"'!B21")
> > Copy C11 across
> >
> > C11 will return the same result as: =Jan!$B$21
> > D11 will return the same result as: =Feb!$B$21
> > and so on
> > --
> > Rgds
> > Max
> > xl 97
> > ---
> > GMT+8, 1° 22' N 103° 45' E
> > xdemechanik <at>yahoo<dot>com
> > ----
> > <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi,
> > > I have a cell that is linked to another sheet in the same workbook;
> > > let's say the reference is =Jan!$B$21. I want the cell next to that to
> > > be =Feb!$B$21.
> > >
> > > Is there any way to use AutoFill? This would save a huge amount of
time
> > > for me rather than Copy-->PasteSpecial-->PasteLink
> > > All help appreciated in advance
> > >
> >
> >
>
>

[Max]

Oops, sorry, typo in line:
> Listed in B1:B2 are the text: SEA, SCCS

Line should read as:
> Listed in B1:C1 are the text: SEA, SCCS

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[GregR]

Max, thank you very much.

Greg
"Max" <[email protected]> wrote in message
news:[email protected]...
> Oops, sorry, typo in line:
> > Listed in B1:B2 are the text: SEA, SCCS
>
> Line should read as:
> > Listed in B1:C1 are the text: SEA, SCCS
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>

[Max]

You're welcome !
Thanks for the feedback
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"GregR" <[email protected]> wrote in message
news:[email protected]...
> Max, thank you very much.

[[email protected]]

This seems way more complicated than I hoped.
Copy>pasteSpecial>pasteLink will be faster

This just seems like way to much effort for something I hoped would be
simple

[Max]

<[email protected]> wrote:
> This seems way more complicated than I hoped.
> Copy>pasteSpecial>pasteLink will be faster
>
> This just seems like way to much effort for something I hoped would be
> simple

It'll take less than 15 seconds to compose say,
an entire year's (12 months) set-up.

> Put in say, C10: Jan
> Drag C10 across to autofill: Feb, Mar, etc in D10, E10, etc
>
> Then put in C11: =INDIRECT("'"&C10&"'!B21")
> Copy C11 across

(You could have also just selected C10:C11, and filled across 12 cols to
N11)

IMO, 15 seconds seems a pretty good option
compared to a cell-by-cell / sheet-by-sheet approach using paste link ..

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----