SOLVED

# Pull Data from another sheet based on multiple criteria

Copper Contributor

# Pull Data from another sheet based on multiple criteria

I have a list of data that is automatically generated, from which I want to take certain data and put it into another sheet/document. The origin file lists only the dates where quantities change, whereas the destination sheet has a full calendar.

The data looks more or less like this:

 Planned date Element Quantity 03.11.2023 Requirement -200 07.11.2023 Delivery 300 09.11.2023 Requirement -500 09.11.2023 Delivery 1000

And the destination table looks like this:

 Date Required Quantity Quantity to be delivered 02.11.2023 03.11.2023 04.11.2023 05.11.2023 06.11.2023 07.11.2023 08.11.2023 09.11.2023 10.11.2023 11.11.2023 12.11.2023

I would need a formula that pulls the data from the first table and puts it into the right place (the 2nd respectively 3rd column) in the second one. I tried using the index function, but this puts #N/A into the first rows until there is a value to be put (here it would be for 02.11.), and it repeats the values where there shouldn't be one. For example for 4.11. until 6.11. there should be nothing, but here it would put -200 in each of those cells. Furthermore, I would need absolute values in my destination table instead of negative ones for the requirements. Lastly, in my origin table there might be a date listed twice because there can be a requirement and planned delivery on the same day, which the index formula that I tried to use just ignores, whereas I do need both values in my destination table, but in seperate columns.

Any suggestions on how to do this would be greatly appreciated.

best response confirmed by Sigert_V (Copper Contributor)
Solution

# Re: Pull Data from another sheet based on multiple criteria

Hi, I think you can use SUMIFS to handle this:

(I attached an example file)

1 best response

Accepted Solutions
best response confirmed by Sigert_V (Copper Contributor)
Solution

# Re: Pull Data from another sheet based on multiple criteria

Hi, I think you can use SUMIFS to handle this:

(I attached an example file)