In excel 2000.
I would like to create a formula that would automatically populate the rows
on a worksheet according to a variable number.
Eg: Product A, Product B, and Product C with the variable number 3.
I would like to automatically populate the first column with three rows
containing "Product A", then the next three rows with "Product B", and a
further three rows with "Product C".
If the variable number were to change to 4 - then I want the same kind of
automated population except the column would have 4 rows of "Product A",
"Product B" and "Product C".
I've tried a combination of COUNTA and VLOOKUP functions, but I keep
creating a circular reference.
Is there a function for the "CTRL+ENTER" command?

1. I am assuming, if you have say 4 in a cell then in Col A you want
A1-A4 = "Product A"
A5-A8 = "Product B" and so on

Enter any number in Cell F1 then paste this formula in Col A
="Product "&INT((ROW()-1)/$F$1)+1
and copy down...

2. If you already have Product A, Product B and so on, in consecutive rows
in Col A
then enter this formula in B1 and copy down
=INDIRECT("A"&INT((ROW()-1)/$F$1)+1)

"Alaric" wrote:

In excel 2000.
I would like to create a formula that would automatically populate the rows
on a worksheet according to a variable number.
Eg: Product A, Product B, and Product C with the variable number 3.
I would like to automatically populate the first column with three rows
containing "Product A", then the next three rows with "Product B", and a
further three rows with "Product C".
If the variable number were to change to 4 - then I want the same kind of
automated population except the column would have 4 rows of "Product A",
"Product B" and "Product C".
I've tried a combination of COUNTA and VLOOKUP functions, but I keep
creating a circular reference.
Is there a function for the "CTRL+ENTER" command?