combing multiple worksheets in a workbook

I have 2 worksheets I am trying to combine, the first sheet contains a store list of 200 stores, the second worksheet contains a list of 155 products, I need to list each of the 200 stores with all 155 products.

Is there a formula to do this or an easier way than copying the 155 product list to columns B C D, then copying the first store number to column A, then copying the store number down 155 times, then repeating for second store?

I have attached a spreadheet, the summary page is where I want all 200 stores with all 155 products listed, I have manually copied over the 1st 2 stores.

Put these formula in your combined sheet (Summary) in the cells stated:

A2: =INDEX('store list'!A:A,INT((ROWS($1:1)-1)/(COUNTA('product list'!A:A)-1))+2)

B2: =INDEX('product list'!A:A,MOD(ROWS($1:1)-1,COUNTA('product list'!$A:$A)-1)+2)

Copy the formula from B2 into C2:D2, so that you have these:

C2: =INDEX('product list'!B:B,MOD(ROWS($1:1)-1,COUNTA('product list'!$A:$A)-1)+2)

D2: =INDEX('product list'!C:C,MOD(ROWS($1:1)-1,COUNTA('product list'!$A:$A)-1)+2)

Then you can copy these formulae down as far as you need to, and then fix the values.

Hope this helps.

Pete

That worked perfectly, thanks much, this saves me hours of copying.

Glad to help - thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete