AutoFill Linked Cells

[TesnoBay]

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]

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

 

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

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

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

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

 

[Max]

Oops, sorry, typo in line:

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

Line should read as:

 

[GregR]

Max, thank you very much.

Greg

Oops, sorry, typo in line:

Line should read as:

 

[Max]

You're welcome !
Thanks for the feedback

 

[TesnoBay]

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]

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