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

[dannyfromnj]

Hi,

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


Part 1:

I am currently using the following formula on Sheet2, in column C t
combine the category name value on Sheet1, with the default partia
category name values on Sheet2 (A2-A11). I'll paste two blocks so yo
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 block
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 ar
in blocks of ten? I've tried the fill handle, and copy/ paste... n
matter what I do the pattern doesn't stick.


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

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

 

[C01d]

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

 

[dannyfromnj]

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

Thank you very much for the reply!

 

[Biff]

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" <[email protected]>
wrote in message

 

[dannyfromnj]

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

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

"dannyfromnj" <[email protected]>
wrote in message
news:[email protected]...