Welcome!

Pull data from another sheet depending on two values

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

Find more posts tagged with

Comments

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.

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.

Sign in to join the conversation!

Quick Links

Trending discussions

I am getting an error message when attempting to export to excel
I am attempted to export to excel. I have two different smartsheet documents to export. One of them exported to excel fine, but not the other. This happened a few weeks ago. I make sure all columns were the correct size & no cells were hidden. "We have found a problem with (file) Do you want us to try and recover as much…
Need Help w/ Report View - Nothing Is Showing
Hello Smartsheet Community, I need some help with a report that I created and shared with a customer. The customer is not seeing any of the information that is pulling into the report. See screen shot below that the customer texted me. I do have the report filter only showing the customer their product pulling from our…
How can I hard code a cell based on a trigger?
BLUF: When I create an Invoice, I need the cell-link to my live price data to be severed. I currently have a Price sheet for my products that is updated frequently. I use Control Center to create Projects, which includes an Invoice sheet. I have a dropdown on my invoice sheet to select a product, and then I use Index…