Sumifs, data validation list and want

HdCpr

New Member
Joined
Aug 8, 2021
Messages
14
Office Version
  1. 365
Platform
  1. MacOS
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
 

Attachments

  • Screenshot 2021-08-30 at 14.39.01-min.png
    Screenshot 2021-08-30 at 14.39.01-min.png
    131.7 KB · Views: 8
  • Screenshot 2021-08-30 at 14.39.17-min.png
    Screenshot 2021-08-30 at 14.39.17-min.png
    249.2 KB · Views: 8
  • Screenshot 2021-08-30 at 14.39.31-min.png
    Screenshot 2021-08-30 at 14.39.31-min.png
    58.4 KB · Views: 7
  • Screenshot 2021-08-30 at 14.40.27-min.png
    Screenshot 2021-08-30 at 14.40.27-min.png
    81.3 KB · Views: 8

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,482
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
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

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

HdCpr

New Member
Joined
Aug 8, 2021
Messages
14
Office Version
  1. 365
Platform
  1. MacOS
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

Excel Formula:
=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

Well-known Member
Joined
Jul 4, 2014
Messages
1,482
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
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

New Member
Joined
Aug 8, 2021
Messages
14
Office Version
  1. 365
Platform
  1. MacOS
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

Excel Formula:
=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 🤔
 

Forum statistics

Threads
1,147,962
Messages
5,744,057
Members
423,843
Latest member
alex2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top