how do i fill down a series of linked cells

[Guest]

i have created a log on worksheet 1 that references cell h6 on ws2,
ws3,ws4...... and i want to be able to fill down in a series but the fill
handle only fills down the same worksheet so i have type the ws# each time.
how can i fill down easily

 

[Guest]

Use the INDIRECT() function:

Let's say we have six sheets:
Sheet1
Sheet2
Sheet3
Sheet4
Sheet5
Sheet6

In A1 enter:
=INDIRECT("Sheet"&ROW()&"!H6") and copy down thru A6

 

[Gord Dibben]

=INDIRECT("ws" & (ROW()) & "!$H$6")

Substitute for "ws" if not "ws" per your example.

Entered in a cell in row 1 and copied down that column


Gord Dibben MS Excel MVP

 

[Guest]

I'm still lost, this didn't seem to work. I must be doing something wrong.

 

[Guest]

maybe im doing something wrong or my question was not explained properly. I
have a workbook with sheet 1 thru sheet 31, sheets 1 thru 30 are formatted
identical except that there are different values in the cells, ie cell a1 on
sheet 1 the value is contractor A, sheet 2 cell a1 the value is contractor B.
what i would like to do is have sheet 31 be a log so that i can see at a
glance what is on sheet 1 thru sheet 30 cel a1. so i copied cell a1 on sheet
1 and pasted it to sheet 31 and linked the cells, i then filled down but had
to change the sheet # in each cell because it picked up sheet 1 all the way
down the fill. how can i accomplish this without having to type in every
cell.

 

[Gord Dibben]

barry

You have explained what you want.

We have given you a method.

Are the worksheets named sheet 1 through sheet 31(note space in name)?

Or named something else.

Give us the precise naming convention.

You should be able to figure out from the two INDIRECT formulas posted what to
do, but maybe your sheet names aren't what you say they are?


Gord

 

[Guest]

Gord,

=INDIRECT("MCCO COVER SHEET 01" & (ROW()) & "!$C$16") this is what i pasted
and then filled down but the cells return the following; #REF!, the sheets
are named MCCO COVER SHEET 01, MCCO COVER SHEET 02, MCCO COVER SHEET
03....... the log sheet is named; MCCO LOG. Your help is appreciated.

 

[Gord Dibben]

Barry

I created a workbook with 3 sheets. One named MCCO LOG and the others as the 01
and 02 cover sheets per your example.

Tested this formula entered in A1 of MCCO LOG and copied down. Works fine.

=INDIRECT("'MCCO COVER SHEET 0" & ROW() &"'!$C$16")

NOTE: you need the ' added around the sheet name because of the spaces in the
name.

" then' before the M

" then ' before the !


Gord