Cell Referencing between worksheets

Magoo

New Member
Joined
May 3, 2002
Messages
28
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?
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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.
 
Upvote 0
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,
 
Upvote 0
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 ?
 
Upvote 0
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).
 
Upvote 0
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
uk.gif

This message was edited by Chris Davison on 2002-05-07 12:24
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top