App: Calc (LibreOffice)
Version: 4.2.1.1
Build ID: 420m0(Build:1)
I have the following where I have an E column which contains a comma separated list of IDs.
A B C D E
1. Item Name ColName Count ColName Location ID(s)
2. Tomato somevalue 4 somevalue 1,4
3. Apple somevalue 6 somevalue 1,2
4. Avocado somevalue 2 somevalue 1,3,4
5. Pumpkin somevalue 8 somevalue 2,3
...
On different sheets I want to auto-populate the rows by having it grab the A Item Name column, and the C Count column (but have the count value divided by 2) where a specified location ID is a substring in the location list for that item. This should change automatically if I add new rows to the above or change the location IDs.
The following are the example rows that would be generated from above sample for each location ID (note how the count value has been divided by 2):
Sheet 1 (location ID list contains 1):
A B
1. Item Name Count
2. Tomato 2
3. Apple 3
4. Avocado 1
...
Sheet 2 (location ID list contains 2):
A B
1. Item Name Count
3. Apple 3
5. Pumpkin 4
...
Sheet 3 (location ID list contains 3):
A B
1. Item Name Count
4. Avocado 1
5. Pumpkin 4
...
Sheet 4 (location ID list contains 4):
A B
1. Item Name Count
2. Tomato 2
4. Avocado 1
...
How would I got about auto-populating such a list and fetching only the rows that match the substring. I’ve done a similar thing using SUMIF() and a regular expression, but in this case I am not trying to calculate anything other than doing a simple division of the Count cell.