How to pick data from cells?

[oscarlimerick]

Good day learned folks of the microsoft forum, I have attached a spreadsheet that contains 4 tabs. I have most of it figured out except I want to create some data on the sales report tab but I need to have excel search the product list to determine how many units of each product were sold, and then populate that number into the qty sold (column B) field of my sales report tab. I know I can do this manually but I was hoping there's some way to have the excel do this for me. Any ideas? Many thanks

[Alansidman]

It would be easy if you actually supplied sample data that we can manipulate and then test. Make it easy for us to help you by helping us and not make more work for us. We are here to help. Our time is just as valuable as yours.

[oscarlimerick]

I did supply sample data, I attached a spreadhseet. didn't it work?

[ArviLaanemets]

An example how to do this in a way it will work without any need of redesign whenever something changes in future (like a new product or new player will be added).

This example is based solely on your example table (no info about when any of the sales happened are stored). So there was no need for any report sheet - all totals are calculated in product and players tables. Additionally, at top of every sheet is a subtotals row, where totals (meaningful ones) for any filtered set in table below are displayed.

In case you add some time identifier (date, week number in format YYYYWW, month number in format YYYYMM, quarter number in format YYYYQQ, or year number, etc.) into sales table, you can create additional report sheet(s), where user can determine the time period for this report, and proper totals for this time period are displayed.

[Alansidman]

Yes, you did supply a worksheet. But, you have not provided any data in the worksheet. All sales are zero. No expected results mocked up. How are we to know what you expect for results. That was my point.

[oscarlimerick]

Originally Posted by ArviLaanemets

An example how to do this in a way it will work without any need of redesign whenever something changes in future (like a new product or new player will be added).

This example is based solely on your example table (no info about when any of the sales happened are stored). So there was no need for any report sheet - all totals are calculated in product and players tables. Additionally, at top of every sheet is a subtotals row, where totals (meaningful ones) for any filtered set in table below are displayed.

In case you add some time identifier (date, week number in format YYYYWW, month number in format YYYYMM, quarter number in format YYYYQQ, or year number, etc.) into sales table, you can create additional report sheet(s), where user can determine the time period for this report, and proper totals for this time period are displayed.

Hello, your solution appears to work for me, does everything I was looking for. Now I just need to figure out he formulas and how you did this. Thanks for the solution, I will close this thread.