Odtnhj

So, here's the thing. I have an excel workbook with two worksheets.
Worksheet 1: People tell me how many items they own per category.
Worksheet 2: People give me details for each item.

And I would like Worksheet 2 to already contain the rows, so they only have to add the details. So I basically want to prepopulate it.

Here is table 1 / Worksheet 1:
enter image description here
So I give people the ID and the category and they add the quantity

And here is table 2 / Worksheet 2:
That's what it should look like (I manually pasted the ID and the Category but the table should be prepopulated automatically)

I know you could do that with a Macro. But my organization doesn't really like Macros so I am looking for a formula.

I really appreciate your help on this!

Thanks,
B

This solution does not require VBA.

In sheet 1:

• Insert a new column at the beginning so that all the data is shifted one column to the right (A becomes B, B becomes C etc).




• In cell A2,enter 1. Then in A3 enter =A2+D2, then fill this formula down to A6




• From F1 to F6, enter the numbers 1 to 6 consecutively (1 in F1, 2 in F2 etc)




• Hide column A and F

Now move to sheet 2

• In cell A2, enter the formula =VLOOKUP(ROW(A2),Sheet1!$A$2:$C$5,2) and fill it down to A6




• In cell B2, enter the formula =VLOOKUP(ROW(B2),Sheet1!$A$2:$C$5,3) and fill it down to B6

The formulas in sheet 2 can now be extended/filled down further as required to capture all the data in sheet 1.

The final result (sheet 2) looks like this: