Forum Discussion

Sigert_V's avatar
Sigert_V
Copper Contributor
Nov 03, 2023
Solved

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 dateElementQuantity
03.11.2023Requirement-200
07.11.2023Delivery300
09.11.2023Requirement-500
09.11.2023Delivery1000

 

And the destination table looks like this:

DateRequired QuantityQuantity 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.

Resources