Sumifs, data validation list and want

[HdCpr]

Data Validation List-and Sumifs results to link another worksheet ,

Hello All

Hopefully someone can advise if what I am trying to do is possible and help. I am trying to create some excel workbook to help to me for work ,and stuck as my excel knowledge limited I have a excel workbook with 3 tabs, Raw data sheet, Work on sheet that I evaluate raw data and order sheet where I want evaluated final numbers to be
Created a data validation list to check orders for each store as there are around 58 store names in work on sheet, wrote a sumifs formula to get proposed numbers from raw data depend on two criterias ( product id and store name)
this part I used formula as =SUMIFS(Raw!$S$2:$S$8962,Raw!$A$2:$A$8962,'Work on '!$A5,Raw!$E$2:$E$8962,'Work on '!$A$2) which works fine
but from Work on sheet- I want to link the results and move to order sheet as it is or after I amended on work on. Wrote a sumifs formula is not working as could not understand where to put store criteria, all stores are in drop down list on work on sheet, does not know if another formula would help and if which ?
On order sheet store names on top of each column and in same order with data validation list, and I want to complete one store and move to next , and previous to be on the correct column and if possible I can save. not sure if possible or put some

 

[jorismoerings]

Hi,

Just to check:

1. worksheet "Raw" holds all data including the store name.
2. Worksheet "Order" has all product codes vertical (so in rows) and all Store names horizontal (so in columns)

If that's true, try this in worksheet Order cell B4

=SUMIFS(Raw!$S$2:$S$8962,Raw!$A$2:$A$8962,'Order'!$A4,Raw!$E$2:$E$8962,'Order'!B$3)

 

[HdCpr]

Hi,

Just to check:

1. worksheet "Raw" holds all data including the store name.
2. Worksheet "Order" has all product codes vertical (so in rows) and all Store names horizontal (so in columns)

If that's true, try this in worksheet Order cell B4

=SUMIFS(Raw!$S$2:$S$8962,Raw!$A$2:$A$8962,'Order'!$A4,Raw!$E$2:$E$8962,'Order'!B$3)

Thank you for your response. I have already written above formula that you mentioned. And you are correct it works. but on Work on sheet I need to amend raw data before sending it to Order. I have data validation list on cell A2 on Work on named sheet which actually makes it complicated for me. As on its on sum if doesn't not work fully as one condition doesn't have vertical list of full stores

 

[jorismoerings]

And where would that amended data be stored because the work on sheet is volatile because if you don’t store it somewhere it’s gone as soon as you change the cell with data validation.

 

[HdCpr]

Hi,

Just to check:

1. worksheet "Raw" holds all data including the store name.
2. Worksheet "Order" has all product codes vertical (so in rows) and all Store names horizontal (so in columns)

If that's true, try this in worksheet Order cell B4

=SUMIFS(Raw!$S$2:$S$8962,Raw!$A$2:$A$8962,'Order'!$A4,Raw!$E$2:$E$8962,'Order'!B$3)

Thank you for your response. I have already written above formula that you mentioned. And you are correct it works. but on Work on sheet I need to amend raw data before sending it to Order. I have data validation list on cell A2 on Work on named sheet which actually makes it complicated for me. As on its on sum if doesn't not work fully as one condition doesn't have

And where would that amended data be stored because the work on sheet is volatile because if you don’t store it somewhere it’s gone as soon as you change the cell with data validation.

Yes you are so right . Actually this all workbook a template and I thought only way is save as dated version on desktop and before change the cell with data validation lock the numbers on order sheet copy and paste as a value on dated version. I think so ?