Copying formula - I need HELP!

[AuditorDorCo]

I am building a spreadsheet that will have a summary page (first page
and then 50 worksheets behind the summary page. Once each of the 5
worksheets have been filled in, information from each worksheet wil
copy to the summary page (makes a report of the information located o
the other 50 worksheets).

This is the problem: I need to link each worksheet to the summar
page. I have done that with the first worksheet and want to simpl
highlight and drag the formulas so that I don't have to individually g
to each sheet and copy and paste the link.

When I try to highlight, drag and copy the formula, everything work
fine except that the worksheet stays constant when I need the formula
to go to the next worksheet. For example it looks like this:

='Record 1'!$C$6
='Record 1'!$C$6
='Record 1'!$C$6

and I want it to look like this:

='Record 1'!$C$6
='Record 2'!$C$6
='Record 3'!$C$6

How can I do this easily without having to go into EACH formula t
change the record number or having to individually copy and paste EAC
link from EACH worksheet? Any help is GREATLY appreciated!
:confused

 

[Max]

Try in the starting cell (say, in C2) in Summary:
=INDIRECT("Record"&ROW(A1)&"!$C$6")

This returns the equivalent of, in C2:

='Record 1'!$C$6

Copy C2 down to return the equivalents in C3, C4, etc of:

='Record 2'!$C$6
='Record 3'!$C$6

If you're copying C2 across, and want the same equivalents in D2, E2, etc,
Put instead in C2: =INDIRECT("Record"&COLUMN(A1)&"!$C$6")

 

[Frank Kabel]

Hi Max
you probably meant:
=INDIRECT("'Record" &ROW(A1)&"'!$C$6")

 

[Bernie Deitrick]

Frank,

And to be very picky <vbg>, you probably meant:

=INDIRECT("'Record " &ROW(A1)&"'!$C$6")

Note the space after Record

HTH,
Bernie
MS Excel MVP

 

[Frank Kabel]

Hi Bernie
thought I inserted it <vbg>
thanks for beeing picky <ebg>

 

[Max]

Yes, fine spot, Frank ! Thanks.
Guess it's really time to change my old "scratched" lenses <g>

Corrected formulas
(to gel with > ='Record 1'!$C$6)

For copying down
=INDIRECT("'Record "&ROW(A1)&"'!$C$6")

For copying across
=INDIRECT("'Record "&COLUMN(A1)&"'!$C$6")

 

[Max]

Note the space after Record

Thanks, Bernie !

I noted this error in the formulas
upon Frank's alert - now duly corrected

hey, its correct to be picky, otherwise the formulas will error
(but I gotta really do something about the scratches on my lens -
it's hiding the apostrophes and even spaces <bg>)