Copying cell references

Posted by Wilma on December 02, 2001 10:40 PM

Hi all,
Can someone please help?

I have a workbook containing a few worksheets.
The main worksheet (W1) contains a summary of the other worksheets. The information on W1 is organised horizontally. The other worksheet (W2) is organised vertically.
The problem is that when I tried and reference a range of cells in W1 to W2 it doesnt work properly. EG.
If I had a range of cells in W2 E7 - E49 I then go to W1 and put in the following =W2!$E7 and attempt to drag the crossbar horizontally so that the E will stay the same and the row number will increment. It doesnt. It will copy E7 into all of the cells.

What am I doing wrong?

Wilma

Posted by Ian Mac on December 03, 2001 4:28 AM

Hi,

Your doing anything wrong and unfortunatly it is working properly.

The way you describe it is that your using an absolute reference to E i.e. $E but not with the row '7'
so when you drag horizontally across the E won't change BUT neither will the 7 as you draging it within the same row.
Excel will only see that you wish to keep the row reference in that row (doesn't work like auto filling), if you were to drag it down it would change.

This all doesn't solve your problem though and apart from using some kinda indirect thingy like
=INDIRECT("W2!$E"&B2)
Where you put the formula in B3 and reference B2 which would contian your 7 (just the No 7).
in C2 put the number 8 and so on (draging autofill across to the number you need)
then drag the formula to just under that last number.
In this way each formula would be increasing by 1, i.e. the number you've put in row 2.

There must be an easier way but I'm full of Flu and can;t see the wood for the trees

Any help to you??

Ian Mac