Linking to other worksheets

C

Cav

I have 12 worksheets (Jan - Dec) each containing data for each month. I also
have a summary sheet that i would like to link to the monthly data. On the
summary sheet my months are listed vertically A5 (Jan) - A16 (Dec) with sales
data to go in B5-B16. Formula for B5 is =Jan!$B$35, B6 is =Feb!$B$35 and so
on.

How can I set it up so i don't have to manually type in the worksheet
reference every time? For example it won't allow to reference the worksheet
name from a cell: =A5!$B$35
 
P

Pete_UK

If the worksheet names are in cell A5 and below, then you can do this:

=INDIRECT("'"&A5&"'!B"&ROW(A35))

Then you can copy down and the ROW(A35) bit will become ROW(A36),
ROW(A37) etc, returning 35, 36, 37 and so on in turn.

Hope this helps.

Pete
 
O

ozgrid.com

RE: How can I set it up so i don't have to manually type in the worksheet
reference every time?

Why are typing the Worksheet name manually? Use you mouse pointer and let
Excel do the typing and fill down.

You could use INDIRECT to reference the sheet name in a cell, BUT then all
your references will be Volatile.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top