Pull data from another sheet depending on two values

PJay128

I need to put the excess data onto another sheet depending on the month and supplier.

This is the sheet Im pulling the data from

So if someone places a request i can track their quota for the month and tell them if they are going to exceed it or not.

 

The other sheet is where they place the request and these are the columns i want to use to get the data

Cheers

jbradbury

I can help you with the formula if you can provide a larger photo of the sheet where requests are being placed. Or if you'd like to share a copy of the two sheets to me via a published link so I can see how they're setup, that may be easier. 

PJay128

Better picture.

thankyou for your help

Debbie Sawyer

Have you seen our new feature called Smarter Workflow Manager?  It moves data from sheet to sheet based on a condition meeting true.  Might work?

https://www.smarterbusinessprocesses.com/smarter-workflow-manager/

Kind regards

Debbie

Andrée Starå

Hi,

Try something like this.

I've used Month@row because you have a month column in the destination sheet.

Replace "Total" with the different supplier names so it matches the column it's in.

This example is for the supplier Total.

=SUMIFS({Source Range Current}; {Source Range Month}; Month@row; {Source Range Supplier}; "Total")

The same version but with the below changes for your and others convenience.

=SUMIFS({Source Range Current}, {Source Range Month}, Month@row, {Source Range Supplier}, "Total")

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

PJay128

Thankyou Andree,

I ended up Using

=SUMIFS(@{Ordering Range 4}, {Ordering Range 2}, @cell = 2, {Ordering Range 3}, @cell = "Total")

Out of curiosity is there anyway to fix a value from another sheet so it dosnt update?

I.e. if i use a Vlookup to pull a figure i want to keep it the same figure at the time i pulled it. like a one time lookup.

Ta

Andrée Starå

Excellent!

Happy to help!

Regarding the fixed value. How would you like to use it? When should it be "fixed"?

PJay128

As soon as an entry is added i want to pull the excess value at that time and fix it to do some calculations with it.

I can still make my sheet work however it would be much more advantage to keep the values static at the time the entry was created so we can interrogate it.

Andrée Starå

Ok.

The third-party integration/service Zapier is an excellent option for this scenario. Is that an option for you?

PJay128

I'm not sure it would do what i want. But also id need to get approval to use it Smartsheet is approved.

Is there no way to keep it within smartsheet?

Cheers

Andrée Starå

Unfortunately, as far as I know, it's not possible at the moment to do it automatically in Smartsheet, but it's a great idea!

Please submit an Enhancement Request when you have a moment.