Auto Populate

[haylau]

Hi

Hard to explain this one.

In sheet 1 i have a list of products in Column A

Product1
Product2
Product 3
....
Product 3000

Etc

On sheet 2 I need to reference those cells, and expand it
So on sheet 2 I will end up with

product1
Product1
product1
product2
product2
product2
product3
product3
product3

etc.

In fact this list on sheet 2 will be expanded 20 times (so product 1 listed 20 times, then product2 listed 20 times etc)

This list is ongoing so when a new product is added on sheet 1 , i need to either automatically (or manually) update sheet 2)

My first thought was simply to replicate
So on sheet 2 have
=cell1
=cell1
=cell1
=cells2
=cells2
=cell2
etc, then replicate the above down, the next one woudld be=cell7 instead of =cell3

I hope that makes sense?

I have read a little about Rows() and Ofset(), but I just cant figure it out

Any ideas?

 

[haylau]

I thought you have header in sheets2, I can modify the code to adjust that.



I don't understand, I tried my code with no such problems.
Let's be clear:
1. Sheet1 must be the active sheet when you run the macro. You might want to put a button in sheet1 to run the code.
2. Sheet2 name is "Sheet2"
3. Say you add new items in A10:A15, after you finish adding the new data you must put the cursor back to A10 then run the macro.

Yep, did all that. And it seems random, Sometimes it pastes in to sheet 1, sometimes sheet 2, sometime data mismatch. But - see next post

 

[My Aswer Is This]

Are you refering to my code?

Sorry, misunderstood. Yes that now works

 

[haylau]

Thanks for the help, but I have realised it is going in the wrong direction

On sheet 2 I don't want the values of sheet 1, I need formula that will grab the data Why? We change the data on sheet1 regularly and that needs to update in sheet 2

So perhaps so live data screen shots may help explain (actually this is to create and update product attributes on Ebay)

Sheet 1 has the raw product data

Sheet two expands each row (based on product reference) so that we can add attributes for ebay (basically pack sizes)

So each of these 19 rows in column D have to POINT to cell A2 on sheet 1. So that if we change cell A2 on sheet 1, column D will be updated and therfore the rest of the sheet will be updated also

So for example

D2 should contain =sheet1!A2
D3= =sheet1!A2
D4=sheet1!A2

Etc down to row 20, then D21 needs to point to A3
D21 =sheet1!A3
D21 = =sheet1!A3

Etc down to row 39 then row 40 needs to point to A4
D40 =sheet1!A4
Etc

And there are currently over 2300 products.

I did have all this working fine in MS Access, but for all kinds of reasons trying to switch to Excel

 

[haylau]

Are you refering to my code?

Sorry, thought I hit reply with Quote. Yes your code does work how you designed. See my last post though to see why I have led you both down the wrong track

 

[My Aswer Is This]

Well from looking at your original post you showed what you have and what you want.
And that is what my script does.

But what your asking for now
Is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[My Aswer Is This]

Maybe I'm a little confused.
Where in your original post did you say anything about column D ?

 

[haylau]

Maybe I'm a little confused.
Where in your original post did you say anything about column D ?

I was trying to keep the idea simple, i can always adapt for different columns.

To be fair, my original post did talk about ofset() row() and simple formula such as=cell1 and the problem with replicating, and the need to "reference" the cells. I dont think I actually said I wanted a copy of the data

But no worries, I appreciate the effort

 

[haylau]

Cracked it

With help from this thread (https://www.mrexcel.com/forum/excel-questions/481497-formula-increment-numbers-1-1-1-2-2-2-etc.html)

So on sheet1 I added a new first column, simply populated with row numbers (1,2,3,4,etc)

Then using this formula in Column D sheet2 I can simply copy the formula down and it uses a vlookup to grab the product code from the first sheet based on the row number.

Fab!

=VLOOKUP(INT((ROW()-ROW($A$2))/19)+1,Product_Details!A:B,2,FALSE)