Excel automaticly advancing copied cell numbers
 

Forgive me, I don't know much Excel lingo. I'm changing the layout of
packing slips for a fundraiser company.

The Tally Sheet read:
Column A Column B Column C
Cookies Cakes
Row1: Joey 5 2
Row2: Billy 3 1

The packing slips read:
Row1: Name - Joey
Row2: Cookies 5
Row3: Cakes 2
Row4: _________________________
Row5: Name - Billy
Row6: Cookies 3
Row7: Cakes 1

The individual student's sales are on one worksheet, but when I'm pulling
them onto the packing slip, the cells are several rows apart, therefore the
cell number advances that many. Any way to do this besides entering it on
all 250-500 packing slips by hand? Thanks!

Excel automaticly advancing copied cell numbers
 

If you're reading the earlier message in microsoft's community webpage,
do not click on the link:

http://www.flypicture.com/download/MTU1OTM=

Do a copy n paste of the entire link (including the "=" at the end) into
your browser's address bar
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Excel automaticly advancing copied cell numbers
 

Thanks Max. This is definately what I was hoping for. The first formula
(for A1) worked wonderfully, but when I'm putting in the 2nd, I'm running
into some trouble. The example I gave was simpler than what I'm doing. The
actual packing slip looks like this:
______________________
Team/Club name: (pulled from Worksheet 1)
Student Name: (Pulled from Tally worksheet)

Cinnamon Rolls
Tuxedo Brownies
Pumpkin Rolls

Apple Dumpings
Carmel Turtle Cheesecake
White Choc. Rasp. Cheesecake
Festive Favorites Cheesecake


PRIZE CODE: (pulled from Prize worksheet)
______________________
I need the spaces in between the rows and with info pulled from multiple
worksheets, I can't figure out how to formulate it with the =IF one you gave
me. I can change the cell starting positions so if you assume that the info
I need from each worksheet starts on A1, I'll understand. Thanks.

Amazon H.


"Max" wrote:

Assume source data is in a sheet: Tally, cols A to C, with data from row2 down

In another sheet: Packing Slips,

Put in A1;
=INDEX({"Names";"Cookies";"Cakes";""},MOD(ROWS($1: 1)-1,4)+1)

Put in B1:
=IF(OFFSET(Tally!$A$2,INT((ROWS($1:1)-1)/4),MOD(ROWS($1:1)-1,4))=0,"",OFFSET(Tally!$A$2,INT((ROWS($1:1)-1)/4),MOD(ROWS($1:1)-1,4)))
Select A1:B1, copy down as far as required. If there's 500 source names to
cover, copy down by 500 x 4 lines per name = 2,000 rows to B2000. Cols A and
B will return the required results.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Amazon H." wrote:
Forgive me, I don't know much Excel lingo. I'm changing the layout of
packing slips for a fundraiser company.

The Tally Sheet read:
Column A Column B Column C
Cookies Cakes
Row1: Joey 5 2
Row2: Billy 3 1

The packing slips read:
Row1: Name - Joey
Row2: Cookies 5
Row3: Cakes 2
Row4: _________________________
Row5: Name - Billy
Row6: Cookies 3
Row7: Cakes 1

The individual student's sales are on one worksheet, but when I'm pulling
them onto the packing slip, the cells are several rows apart, therefore the
cell number advances that many. Any way to do this besides entering it on
all 250-500 packing slips by hand? Thanks!

Excel automaticly advancing copied cell numbers
 

If there's regularity in the structure of the other stuff that you want
compiled from other sheets into Packing Slips, think there might be a way to
extend the earlier OFFSET(..,INT(..),MOD(..)) in col B to cover.

I'd need to examine an actual sample of what you have over there. Use either
of the 2 filehosts below to upload the sample. Then copy n paste the
generated link to your sample file in response he

http://www.flypicture.com/
http://cjoint.com/index.php

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Amazon H." wrote:
Thanks Max. This is definitely what I was hoping for. The first formula
(for A1) worked wonderfully, but when I'm putting in the 2nd, I'm running
into some trouble. The example I gave was simpler than what I'm doing. The
actual packing slip looks like this:
______________________
Team/Club name: (pulled from Worksheet 1)
Student Name: (Pulled from Tally worksheet)

Cinnamon Rolls
Tuxedo Brownies
Pumpkin Rolls

Apple Dumpings
Carmel Turtle Cheesecake
White Choc. Rasp. Cheesecake
Festive Favorites Cheesecake


PRIZE CODE: (pulled from Prize worksheet)
______________________
I need the spaces in between the rows and with info pulled from multiple
worksheets, I can't figure out how to formulate it with the =IF one you gave
me. I can change the cell starting positions so if you assume that the info
I need from each worksheet starts on A1, I'll understand. Thanks.

Amazon H.

Excel automaticly advancing copied cell numbers
 

The file is too big for either of those sites to upload. It's a little over
5 MB.


"Max" wrote:

If there's regularity in the structure of the other stuff that you want
compiled from other sheets into Packing Slips, think there might be a way to
extend the earlier OFFSET(..,INT(..),MOD(..)) in col B to cover.

I'd need to examine an actual sample of what you have over there. Use either
of the 2 filehosts below to upload the sample. Then copy n paste the
generated link to your sample file in response he

http://www.flypicture.com/
http://cjoint.com/index.php

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Amazon H." wrote:
Thanks Max. This is definitely what I was hoping for. The first formula
(for A1) worked wonderfully, but when I'm putting in the 2nd, I'm running
into some trouble. The example I gave was simpler than what I'm doing. The
actual packing slip looks like this:
______________________
Team/Club name: (pulled from Worksheet 1)
Student Name: (Pulled from Tally worksheet)

Cinnamon Rolls
Tuxedo Brownies
Pumpkin Rolls

Apple Dumpings
Carmel Turtle Cheesecake
White Choc. Rasp. Cheesecake
Festive Favorites Cheesecake


PRIZE CODE: (pulled from Prize worksheet)
______________________
I need the spaces in between the rows and with info pulled from multiple
worksheets, I can't figure out how to formulate it with the =IF one you gave
me. I can change the cell starting positions so if you assume that the info
I need from each worksheet starts on A1, I'll understand. Thanks.

Amazon H.

Excel automaticly advancing copied cell numbers
 

Just upload a sample**, not the actual.
And you could also zip it up first before uploading?

**what's in your 3 source sheets:
Sheet1, Tally and Prize, just a few sample rows of data (delete the rest of
data)

As for the "Packing slip" format, I'll refer to your post, or you could
indicate a sample in your "Packing Slip" sheet.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Amazon H." wrote in message
...
The file is too big for either of those sites to upload. It's a little
over
5 MB.