May 27 2022 01:04 AM
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!)
May 27 2022 04:16 AM
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.
May 28 2022 12:59 AM
@Hans Vogelaar Thanks for helping - here is the link! - Link to Google Sheet
May 28 2022 02:00 AM
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.
May 28 2022 06:00 AM
May 28 2022 06:38 AM
How can we determine which category a product belongs to?
May 30 2022 08:34 PM
@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.
May 31 2022 02:58 AM
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.
Jun 01 2022 11:18 PM
Jun 02 2022 01:01 AM
Did you click the Update SONALIKA button after adding a row? It works for me!