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?

[inarbeth]

Why not use Paste Special? On Sheet 1 select all of the data you want to be copied on to Sheet 2. Copy it to clipboard. Go to Sheet 2 and click the first cell you want - in your case A1. Select Edit, Copy, Paste Special and click Paste Link.

[Magoo]

Will this work if the data on sheet one changes?

[Chris Davison]

Hi Magoo,

this'll work for that specific example :

in cell A1, sheet 2 :

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

then just fill it accross to column D and then fill down to your 3 destination rows

is your actual range a lot bigger ? the "pattern" you're referrign to is firstly a gap of 6 rows, followed by a gap of 7 rows

does it continue...6 then 7 then 6 then 7

or is it 6 then 7 then 7 all the time ?

or maybe 6 then 7 then 8 then 9 etc etc etc

anyhow,

[Chris Davison]

On 2002-05-05 01:12, inarbeth wrote:
Why not use Paste Special? On Sheet 1 select all of the data you want to be copied on to Sheet 2. Copy it to clipboard. Go to Sheet 2 and click the first cell you want - in your case A1. Select Edit, Copy, Paste Special and click Paste Link.

Ian,

That's odd - it seems to increment the links out of sync for some reason. Maybe cos of the gaps being of different sizes ?

[inarbeth]

Sorry. Because the ranges are separated they will have to be pasted individually.

[Magoo]

Sorry, my example was inacurrate (like my spelling). The gaps are all the same size. I also cant 'cut and paste' because I need my second sheet to link to sheet 1 in order to pick up any changes to the original data (on sheet 1).

[Chris Davison]

On 2002-05-06 20:00, Magoo wrote:
The gaps are all the same size.

Magoo,

Contrary to popular belief, we're not pyschic.... are your gaps 6 rows or are they 7 rows ?

Rant over...

If your gaps are 6 rows, try this in Sheet2, cell A1 and then copy it to D1 :

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

then highlight A1 to D1 and copy it to your destination cells (CNTRL C then CNTRL V)

If your gaps are 7 rows,

try this in Sheet2, cell A1 and then copy it to D1 :

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

then highlight A1 to D1 and copy it to your destination cells (CNTRL C then CNTRL V)





_________________
Hope this helps,
Chris


[ This Message was edited by: Chris Davison on 2002-05-07 12:24 ]

[James]

and were not "psychic" either!

James

[Chris Davison]

*chuckle*