Forum Discussion
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.
1 Reply
- rachelIron Contributor