I have data in sequentially numbered worksheets; such as "7-7-1", "7-7-2", etc.

I would like to compile data found in certain cells of these worksheets--the
same cell in each sheet.

Right now I start with something like "='7-7-1'!$O$45" and then just drag
down to copy and manually change the "7-7-1" part to "7-7-2" and so on.

Is there a way to do this more efficiently?

I have figured out how to use the CONCATENATE function to make a string that
matches the "='7-7-1'!$O$45" part, but I don't know how to use this further.

Any suggestions?

Maybe you could use the indirect() function. You can then build an
address by combining data in different cells. ie

in column A, enter 1, 2, 3 etc down the column
in B1, enter "=7-7-"&A1&"!$O$45"
in C1 enter indirect (B1)

copy the whole lot down the columns and it should give you what you
want.

Matt


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=561246

Thanks! The indirect function works perfectly!

"Mallycat" wrote:

>
> Maybe you could use the indirect() function. You can then build an
> address by combining data in different cells. ie
>
> in column A, enter 1, 2, 3 etc down the column
> in B1, enter "=7-7-"&A1&"!$O$45"
> in C1 enter indirect (B1)
>
> copy the whole lot down the columns and it should give you what you
> want.
>
> Matt
>
>
> --
> Mallycat
> ------------------------------------------------------------------------
> Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
> View this thread: http://www.excelforum.com/showthread...hreadid=561246
>
>