Auto populating a row in a sheet based on a row in another sheet!

Copper Contributor

Hi, I'm trying to apply some formulas/ functions to an Excel worksheet where there is pricing details for each product as can be seen in the "SONALIKA" worksheet. The variable here is the increase given to the product and the increase is mostly same for each product (products are categorized into two categories and price increase for products in one category are the same). I'm trying to create a master worksheet such that I can add the price increase for a category in this worksheet and that auto populates a row for all products of that category in the other worksheet such that now we would have a new row added at the bottom for each product of that category with the date, increase given etc. I'm just trying to think of the most optimal way to do this.

 

One way and probably the easiest is to not have a master but just added a new row for one product and then auto populate a new row for other products in that category with the same values but I don't think this is most optimal. I feel having a master sheet is more intuitive for the user but not sure what formulas/ functions to use to achieve the above stated goal using this method. 

 

Any help would be much appreciated, thank you!

 

(Tried to add the file here but could not see an option to do so!)

9 Replies

@vishrutGoel 

You might create a stripped-down copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

@Hans Vogelaar Thanks for helping - here is the link! - Link to Google Sheet 

@vishrutGoel 

See the attached version. Doing this fully automatically would be complicated.

I added a button to the template sheet that will update the other sheet.

Thank you very much - deeply appreciated. I'm trying to understand how you did the such that I can make a modification in that the "SONALIKA" worksheet would consist of 2 categories of products and so would "SONALIKA MASTER". If one category is updated in "SONALIKA MASTER" (say CI), then only products corresponding to CI in "SONALIKA" should get updated. Not sure if this is an easy modification or if this was what you were referring to as complicated but I thought I'll give it a shot.

@vishrutGoel 

How can we determine which category a product belongs to?

@Hans Vogelaar Hey Hans, sorry for the delayed response. I've modified the sheet to reflect that, here's the new link - Link to worksheet  - in "Sonalika Master", I can have two categories - CI and SG - and master table for each. In the "Sonalika" worksheet, I added column for product category and each category can belong to either CI or SG. Based on which one is updated in the master, the products belonging to that category should get updated in the "Sonalika" worksheet. 

@vishrutGoel 

See the attached version.

To make it work, I had to repeat the category in column B of the SONALIKA sheet. When new rows are inserted, the macro will fill in the category.

Thank you Hans. When I now try to insert a new row in "Sonalika Master" for either of the category, the "Sonalika" sheet does not get updated. Was trying to figure out why this is happening!

@vishrutGoel 

Did you click the Update SONALIKA button after adding a row? It works for me!