Fill series that combines data from 2 sheets in blocks of 10

Hi,

I have a spreadsheet with two sheets... Sheet1, and Sheet2, that I use as sort of a staging area to format info before bulk uploading to my product database. The products I'm working with at the moment have 10 subcategories below each of them. Sheet1 is the full product database listing. I place new subcategories in blocks of 10 that I plan to add to the database on Sheet2. Sheet2 also holds the default partial category names in cells A2-A11.


Part 1:

I am currently using the following formula on Sheet2, in column C to combine the category name value on Sheet1, with the default partial category name values on Sheet2 (A2-A11). I'll paste two blocks so you have a clear picture of what I mean by blocks of ten.

=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$2)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$3)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$4)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$5)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$6)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$7)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$8)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$9)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$10)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$11)
=CONCATENATE(Sheet1!B3408;" ";Sheet2!$A$2)
=CONCATENATE(Sheet1!B3408;" ";Sheet2!$A$3)
=CONCATENATE(Sheet1!B3408;" ";Sheet2!$A$4)
=CONCATENATE(Sheet1!B3408;" ";Sheet2!$A$5)
=CONCATENATE(Sheet1!B3408;" ";Sheet2!$A$6)
=CONCATENATE(Sheet1!B3408;" ";Sheet2!$A$7)
=CONCATENATE(Sheet1!B3408;" ";Sheet2!$A$8)
=CONCATENATE(Sheet1!B3408;" ";Sheet2!$A$9)
=CONCATENATE(Sheet1!B3408;" ";Sheet2!$A$10)
=CONCATENATE(Sheet1!B3408;" ";Sheet2!$A$11)


Part 2:

On Sheet2 in the adjacent column D, I use the following code in blocks of ten to pull the higher category id value from Sheet1.

=Sheet1!A3407
=Sheet1!A3407
=Sheet1!A3407
=Sheet1!A3407
=Sheet1!A3407
=Sheet1!A3407
=Sheet1!A3407
=Sheet1!A3407
=Sheet1!A3407
=Sheet1!A3407
=Sheet1!A3408
=Sheet1!A3408
=Sheet1!A3408
=Sheet1!A3408
=Sheet1!A3408
=Sheet1!A3408
=Sheet1!A3408
=Sheet1!A3408
=Sheet1!A3408
=Sheet1!A3408

My question simply is how can I fill these formulas down when they are in blocks of ten? I've tried the fill handle, and copy/ paste... no matter what I do the pattern doesn't stick.


Huge thanks in advance to all who post answers on the board... you're all fantastic!

_________________
Danny Tedesco
http://www.wirelessphonecity.com
Cut the cables and let your data fly...

You could use a VBA for loop to populate the cells with formulas. Change the boundary values and cell references to suit your needs.

Dim rng as range
set rng = range("D1")

for row = 3407 to 3500
for i = 1 to 10
rng.formula = "=Sheet1!" & row
rng.offset(1,0)
next i
next row

I don't have any VBA experiance... but I will give it a shot.

Thank you very much for the reply!

Hi!

Try these formulas.

For Part 1:

=INDEX(Sheet1!B$3407:B$3450,CEILING(ROWS($1:1)/10,1))&"
"&INDEX(Sheet2!A$2:A$11,MOD(ROWS($1:1),10)+(MOD(ROWS($1:1),10)=0)*10)

Adjust this portion for the actual end of the range:

=INDEX(Sheet1!B$3407:B$3450

For Part 2:

=INDEX(Sheet1!A$3407:A$3450,CEILING(ROWS($1:1)/10,1))

Again, adjust this portion for the actual end of the range:

=INDEX(Sheet1!A$3407:A$3450

Biff

"dannyfromnj" <dannyfromnj.28w0qm_1149441000.5191@excelforum-nospam.com>
wrote in message
news:dannyfromnj.28w0qm_1149441000.5191@excelforum-nospam.com...
>
> Hi,
>
> I have a spreadsheet with two sheets... Sheet1, and Sheet2, that I use
> as sort of a staging area to format info before bulk uploading to my
> product database. The products I'm working with at the moment have 10
> subcategories below each of them. Sheet1 is the full product database
> listing. I place new subcategories in blocks of 10 that I plan to add
> to the database on Sheet2. Sheet2 also holds the default partial
> category names in cells A2-A11.
>
>
> Part 1:
>
> I am currently using the following formula on Sheet2, in column C to
> combine the category name value on Sheet1, with the default partial
> category name values on Sheet2 (A2-A11). I'll paste two blocks so you
> have a clear picture of what I mean by blocks of ten.
>
> =CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$2)
> =CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$3)
> =CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$4)
> =CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$5)
> =CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$6)
> =CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$7)
> =CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$8)
> =CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$9)
> =CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$10)
> =CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$11)
> =CONCATENATE(Sheet1!B3408;" ";Sheet2!$A$2)
> =CONCATENATE(Sheet1!B3408;" ";Sheet2!$A$3)
> =CONCATENATE(Sheet1!B3408;" ";Sheet2!$A$4)
> =CONCATENATE(Sheet1!B3408;" ";Sheet2!$A$5)
> =CONCATENATE(Sheet1!B3408;" ";Sheet2!$A$6)
> =CONCATENATE(Sheet1!B3408;" ";Sheet2!$A$7)
> =CONCATENATE(Sheet1!B3408;" ";Sheet2!$A$8)
> =CONCATENATE(Sheet1!B3408;" ";Sheet2!$A$9)
> =CONCATENATE(Sheet1!B3408;" ";Sheet2!$A$10)
> =CONCATENATE(Sheet1!B3408;" ";Sheet2!$A$11)
>
>
> Part 2:
>
> On Sheet2 in the adjacent column D, I use the following code in blocks
> of ten to pull the higher category id value from Sheet1.
>
> =Sheet1!A3407
> =Sheet1!A3407
> =Sheet1!A3407
> =Sheet1!A3407
> =Sheet1!A3407
> =Sheet1!A3407
> =Sheet1!A3407
> =Sheet1!A3407
> =Sheet1!A3407
> =Sheet1!A3407
> =Sheet1!A3408
> =Sheet1!A3408
> =Sheet1!A3408
> =Sheet1!A3408
> =Sheet1!A3408
> =Sheet1!A3408
> =Sheet1!A3408
> =Sheet1!A3408
> =Sheet1!A3408
> =Sheet1!A3408
>
> My question simply is how can I fill these formulas down when they are
> in blocks of ten? I've tried the fill handle, and copy/ paste... no
> matter what I do the pattern doesn't stick.
>
>
> Huge thanks in advance to all who post answers on the board... you're
> all fantastic!
>
> _________________
> Danny Tedesco
> http://www.wirelessphonecity.com
> Cut the cables and let your data fly...
>
>
> --
> dannyfromnj
> ------------------------------------------------------------------------
> dannyfromnj's Profile:
> http://www.excelforum.com/member.php...o&userid=35083
> View this thread: http://www.excelforum.com/showthread...hreadid=548295
>

Biff that worked perfectly... thanks so much for the reply!

Until now I've never used "ROW" "MOD" or "CEILING". I've seen them while flipping through the Excel help files, but their examples were so basic I was having trouble figuring out how to apply them. Your code sheds more light... thanks again.

As a result of C01d's post, I also spent some time today going through some VBA tutorials and I'm starting to see the light there as well. Seems to provide a lot of flexibility. Thanks for the inspiration :-)

You're welcome. Thanks for the feedback!

Biff

"dannyfromnj" <dannyfromnj.28wsim_1149477001.102@excelforum-nospam.com>
wrote in message
news:dannyfromnj.28wsim_1149477001.102@excelforum-nospam.com...
>
> Biff that worked perfectly... thanks so much for the reply!
>
> Until now I've never used "ROW" "MOD" or "CEILING". I've seen them
> while flipping through the Excel help files, but their examples were so
> basic I was having trouble figuring out how to apply them. Your code
> sheds more light... thanks again.
>
> As a result of C01d's post, I also spent some time today going through
> some VBA tutorials and I'm starting to see the light there as well.
> Seems to provide a lot of flexibility. Thanks for the inspiration :-)
>
>
> --
> dannyfromnj
> ------------------------------------------------------------------------
> dannyfromnj's Profile:
> http://www.excelforum.com/member.php...o&userid=35083
> View this thread: http://www.excelforum.com/showthread...hreadid=548295
>