Cell Referencing between worksheets

[Magoo]

Hi All

I am trying to get excel to recognise a pattern. The problem is that on sheet 2 of my work book I want cells A1:D1, A8:D8, A16:D18 to reflect identical data from sheet one. The procedure that I am using is:

On sheet 2 (in cell A1) I am clicking on = then sheet 1, cell A1 then clicking on the green tick. I follow this procedure for cells B1:D1, then again for cells A2:D2 to give excel an idea of the pattern. When I highlight my 2 rows of data in sheet 2 and drag down, I dont get the data I am looking for. I have tried the above procedure on up to 5 lines of data, but still no joy when I drag down.

Any ideas?

 

[Magoo]

Thank you all for your input and your chuckles at my expense. I am going to give it a go very shortly.

 

[Magoo]

I have tried

=INDIRECT("Sheet1!"&(CHAR(COLUMN(BM:BM))&(1+(((ROW()-1)/7)*1))))

I have typed it exactly as it is, verbatim, no variations or deviations and I get a #REF error message.

Now what???

 

[Magoo]

BTW

For all you phychic's out there, the gap is 7 rows!

 

[Chris Davison]

Magoo,

I did offer 2 formulae : the first looked at gaps of 6 and the second looked at gaps of 7.... you're still using the first, you need to use the 2nd (see above)

it's :

=INDIRECT("Sheet1!"&(CHAR(COLUMN(BM:BM))&(1+(((ROW()-1)/8)*1))))

in cell A1 sheet 2, copy accross then copy all 4 cells down to to your destination rows



_________________
Hope this helps,
Chris
(Excel '97, Windows ME)
This message was edited by Chris Davison on 2002-05-14 04:58

 

[Magoo]

I have corrected my formula but I still am getting higgldy, piggldy results. I may not have explained myself very well initially.

I want to take the information from sheet 1, rows 2, 9, 16, 23, 30 and so on incrementing by 7 each time and put this information in to sheet 2 on rows 2,3,4,5 and so on.

In other words:

the info on sheet 1, row 2, columns B-F needs to show up in sheet 2, row 2, in columns A-E
the info on sheet 1, row 9, columns B-F needs to show up in sheet 2, row 3, in columns A-E

the info on sheet 1, row 16, columns B-F needs to show up in sheet 2, row 4, in columns A-E

the info on sheet 1, row 23, columns B-F needs to show up in sheet 2, row 5, in columns A-E


I cant cut and paste because if the information on sheet one changes, then I need to be able to reflect this on sheet 2, plus it takes too long.

I have tried the following:

With the cursor in cell A2, sheet 2, I have clicked on the equals sign, then sheet 1, then cell A2. I can fill across using this method, but not down. Excel won’t recognise the pattern.

 

[Chris Davison]

=INDIRECT("Sheet1!"&CHAR(COLUMN(BN:BN))&(2+(((ROW()-2))*7)))

in cell A2, sheet 2

copy accross and then copy down

 

[Magoo]

EUREKA !!!!!!!!!!!!!!!!!!!!!!!!!!!

IT WORKS, IT WORKS, IT WORKS

))))

Many thanks for being so patient and helpful.