re my new problem

[HANYANA via OfficeKB.com]

Hey Max & Bob,
my new problem is i have data that i on one worksheet that i need to be
represented on a diferent work sheet.
eg

BRAD 0 36 SIMON 0 16 ADRIAN 0 0
TRAVIS 0 28 RAY 0 0
MARK 0 0 DANE 0 0

these figures are the sums of coloured cells on the main roster sheet,
then i need to put them onto the second sheet under there respective colums
for annual leave n training days.

eg of sheet 2

NAME A/L T/D
BRAD need the figures from the formulas in the cells in sheet one
TRAVIS to show in theses cells.
MARK
SIMON
RAY
DANE
ADRIAN

hope this makes sense.
i know it's probably simple, but i am probably just stupid.
i really appreciate all your help with my problems.
Thanks
Han

 

[Max]

Here's one play ..

Assuming the source data below is in Sheet1's A2:I4

BRAD 0 36 SIMON 0 16 ADRIAN 0 0
TRAVIS 0 28 RAY 0 0
MARK 0 0 DANE 0 0

where the 2nd col's: 36, 28, 16, etc represents the annual leave days, and
the 1st col's zeros are the training days
(1st and 2nd cols are cols adjacent to the names)

In Sheet2,

In A2:
=OFFSET(Sheet1!$A$2,MOD(ROW(A1)-1,3),INT((ROW(A1)-1)/3)*3)

In B2:
=OFFSET(Sheet1!$C$2,MOD(ROW(A1)-1,3),INT((ROW(A1)-1)/3)*3)

In C2:
=OFFSET(Sheet1!$B$2,MOD(ROW(A1)-1,3),INT((ROW(A1)-1)/3)*3)

Select A2:C2, copy down to C8

This returns:

BRAD 36 0
TRAVIS 28 0
MARK 0 0
SIMON 16 0
RAY 0 0
DANE 0 0
ADRIAN 0 0

 

[Max]

If you have more than 3 source data rows to re-arrange, eg: you have 10
rows, just change the "3" within the MOD(...) and INT(...) portions of the 3
formulas to "10" instead, ie use:

In Sheet2,

In A2:
=OFFSET(Sheet1!$A$2,MOD(ROW(A1)-1,10),INT((ROW(A1)-1)/10)*3)

In B2:
=OFFSET(Sheet1!$C$2,MOD(ROW(A1)-1,10),INT((ROW(A1)-1)/10)*3)

In C2:
=OFFSET(Sheet1!$B$2,MOD(ROW(A1)-1,10),INT((ROW(A1)-1)/10)*3)

Then just select A2:C2 and copy down as far as required

 

[HANYANA via OfficeKB.com]

sorry max i know i sound stupid i don't get it...

the source data is on sheet 1 row 329, 330, 331 and al colomns are B, E and H,

T/D are Colomns C, F,and I.
i need them to be repersented on sheet 2 on colomns C and E starting at Row 4
and ending at row 16...
I really REALLY thank you for all this, i just don't know what bits to change
to get them to go in the right spots without it returning a #VALUE? message..

Thanks
Han

 

[Max]

Try these adapted formulas, which are customized to suit the layout
described in your response:

Source data is within A329:I331 in Sheet1

In Sheet2,

Labels assumed in C3:E3 :
Name, A/L, T/D

Put in C4:
=OFFSET(Sheet1!$A$329,MOD(ROW(A1)-1,3),INT((ROW(A1)-1)/3)*3)

Put in D4:
=OFFSET(Sheet1!$B$329,MOD(ROW(A1)-1,3),INT((ROW(A1)-1)/3)*3)

Put in E4:
=OFFSET(Sheet1!$C$329,MOD(ROW(A1)-1,3),INT((ROW(A1)-1)/3)*3)

Select C4:E4, copy down to E12

The required results should appear correctly within C4:E12. It should end at
E12, not E16 since there's only a maximum of 9 names within A329:I331 in
Sheet1.

Pl ensure that the source sheet is named as:
Sheet1
(ie without a space between the number 1 and letter t)

It should work fine now, but do let me know how it went for you.

 

[HANYANA via OfficeKB.com]

Max,
You are a real superstar, you got exactly what i needed and it all worked
wonderfully!!
The reason it had to drop to E16 was because i had to add a few more
employees to sheet1and 2.
I cant thank you enough for all your help.
take care
Han

Try these adapted formulas, which are customized to suit the layout
described in your response:

Source data is within A329:I331 in Sheet1

In Sheet2,

Labels assumed in C3:E3 :
Name, A/L, T/D

Put in C4:
=OFFSET(Sheet1!$A$329,MOD(ROW(A1)-1,3),INT((ROW(A1)-1)/3)*3)

Put in D4:
=OFFSET(Sheet1!$B$329,MOD(ROW(A1)-1,3),INT((ROW(A1)-1)/3)*3)

Put in E4:
=OFFSET(Sheet1!$C$329,MOD(ROW(A1)-1,3),INT((ROW(A1)-1)/3)*3)

Select C4:E4, copy down to E12

The required results should appear correctly within C4:E12. It should end at
E12, not E16 since there's only a maximum of 9 names within A329:I331 in
Sheet1.

Pl ensure that the source sheet is named as:
Sheet1
(ie without a space between the number 1 and letter t)

It should work fine now, but do let me know how it went for you.

sorry max i know i sound stupid i don't get it...

[quoted text clipped - 12 lines]

Thanks
Han

 

[Max]

.. exactly what i needed and it all worked wonderfully!!

Always glad to hear that kind of statement !
You're welcome, and thanks for feeding back.