Auto-Populate Rows From Specific Cells In Rows That Contain A Substring [closed]

asked 2014-02-27 07:14:19 +0100

cryptic ツ gravatar image

updated 2015-08-27 13:10:20 +0100

Alex Kemp gravatar image

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.

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2016-02-19 14:51:29.744264