How to quickly populate cells with data from other sheets

[nutmegy]

Hi

I am trying to populate a spreadsheet with other data obtained from another sheet. ie cell formula is

"=Sheet2!A1" which works fine but takes ages as i need to do the following

Sheet1 is where i need the info to be re sorted

so...

e.g. Sheet1 Cell A1 =Sheet2!A1 ... cell B1 = Sheet2!A2 .. cell C1 = Sheet2!A3

then....

Sheet1 Cell A2 = Sheet2!A4... Cell B2 = Sheet2!A5 ... Cell C2 = Sheet!A6

I've tried using TRANSPOSE command to do each row of data but I have hundreds of rows to do this on, and its taking way too long. (I've used =TRANSPOSE(Sheet2!$A$1:$A$3) for one row of data and increased each figure by 3 each time)

Can anyone help?

 

[GlennUK]

Put this in cell A1 of Sheet1 :

=OFFSET(Sheet2!$A$1,(ROW()-1)*3+COLUMN()-1,0)

and copy across to column C, and copy down as required.


Cheers, Glenn.

 

[01Patrik]

Have you tried to 'paste special' / 'transpose' instead? If you constantly need to update the data from the first sheet, you can write in a macro and place it under workbook_open ...?

Should be a lot faster, but does it 'fulfill your needs'?



// Patrik

 

[nutmegy]

Glenn...

i've tried your suggestion, but it comes up with the same value from cells A1 to C1 and blank on the rows further down.

Any ideas?

 

[xlbo]

Think Glen's suffering from FFS
=OFFSET(Sheet2!$A$1,0,(ROW()-1)*3+COLUMN()-1)
should do it - seems to put everything in row 1 on Sheet1 into 3 columns in sheet2

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[nutmegy]

AAArrgghhh it's still not working

What i've entered is

=OFFSET(Data!$C$4,0,(ROW()-1)*3+COLUMN()-1)

into Sheet1 Cell C4,

when i copy it across it's giving me figures from

Data!H4 to Data!j4 in the first row and M4 to O4 in the next row etc... so it's jumping by 3 columns each time.

I am completely confused now

Thanksfor your help so far guys

 

[xlbo]

That's because you have changed the criteria - you started off by using a start point of A1. The formula is working on incrementing rows and columns based on a specified start point (A1). Of course it won't work if you change the start point. Please tell us exactly
a: Where your data starts and ends (cell references please)
b: The 1st cell you will be entering the formula into on Sheet1

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[nutmegy]

oops apologies

I am starting on Sheet1

populating cells C4 to E4 .. C5 to E5

obtaining data from Data!C4 to C6 ... C7 to C9 etc...

 

[xlbo]

in that case:
=OFFSET(Sheet2!$C$4,0,(ROW()-4)*3+COLUMN()-3)

which is basically the 1st formula but with the hardcoded constants changed to reflect the new positions

Basically, the offset function in this case, takes arguments of
a: the starting position to offset from (Sheet2!$C$4)
b: the number of rows to offset(0)
c: the number of columns to offset - in this case, to calculate the number of columns, we are using the row and column of the formula cell to determine how much to offset to the right

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[nutmegy]

Sorry about this Geoff... it still isn't doing the right stuff

What it's doing now is

Sheet1!C4=Data!C4
Sheet1!D4=Data!D4
Sheet1!E4=Data!E4
Sheet1!C5=Data!F4
Sheet1!D5=Data!G4
Sheet1!E5=Data!H4

What I need to do is

Sheet1!C4=Data!C4
Sheet1!D4=Data!C5
Sheet1!E4=Data!C6
Sheet1!C5=Data!C7
Sheet1!D5=Data!C8
Sheet1!E5=Data!C9

etc...

I am really sorry if i was unclear

 

[GlennUK]

Hi there,

so for no responses for a while, had to work. Looks like Geoff has been helping ( what is FFS? ), but I think he's got his formula working in the wrong direction. Try this:]

=OFFSET(Sheet2!$C$4,(ROW()-4)*3+COLUMN()-3,0)

Cheers, Glenn.

 

[nutmegy]

Nice one mate... here's a star for ya both.... I really do have to learn how that function works... it seems bloody useful

 

[nutmegy]

Hi Glenn

So can you explain to me each section of that as I would really like to understand it to apply it to other cells

Thanks

Andrew

 

[GlennUK]

Ok, here goes explaining it...

=OFFSET(Data!$C$4,(ROW()-4)*3+COLUMN()-3,0)

there are 3 arguments involved here:
a) Data!$C$4 : this is the point from which offsetting will occur
b) (ROW()-4)*3+COLUMN()-3 : number of rows to offset. This calculation uses the ROW() and COLUMN() functions to adjust the offsetting. The adjustment of the offsetting occurs with respect to the cell position of the cell with this formula in. So the formula in Sheet1!D5 gives a ROW() of 5 and a COLUMN of 4, so (ROW()-4)*3+COLUMN()-3 becomes (5-4)*3+4-3 which equals 4. Offsetting from Data!$C$4 by 4 rows gives a reference of Data!$C$8 which is what you want.
c) 0 : offset by zero columns

Phew, I hope that explains it a bit.

Cheers, Glenn.

 

[xlbo]

D'oh - misread it
Glenn was right all along - mine works for horizontal data

Glenn - FFS = Fat Finger Syndrome. I thought you had mixed up where the 0 went. Seems more like I was suffering from a newer form - FSS (F****d Sight Syndrome)

andrew - you can use my explanation as the only difference between Glenn's formula and mine is that mine looks horizontally, Glenn's looks vertically. Look in the help files for OFFSET if you want more info

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[nutmegy]

cheers guys.....

 

[GlennUK]

Cheers Geoff,

I'm off home now to rest my fat fingers, ha ha.

I hear a beer calling.

Bye.