Hello,
I have annual financial statements issued for each area, every year. Each financial statement is saved as a separate excel file. I would like to compare the results area by area, and year by year. To do this, I need my main spread sheet to reference specific cells in other spreadsheets.</SPAN>
But there are two challenges I can’t figure out how to overcome.</SPAN>
Volumes- </SPAN>
Product A </SPAN>
Volumes per day- </SPAN>
Product A </SPAN>
Prices- </SPAN>
Product A</SPAN>
Revenue-</SPAN>
Product A</SPAN>
So what I need it to do is look up the correct header, then look ONLY in the values below that header for the value I want.</SPAN>
*Note*: The Headers are always the same; Only the products (and consequently the row #) change. I have included an example below.</SPAN>
Output:
<TBODY>
</TBODY></SPAN>
Data: (Note- each "Property" is a different spreadsheet)
<TBODY>
</TBODY>
<TBODY>
</TBODY>
I have annual financial statements issued for each area, every year. Each financial statement is saved as a separate excel file. I would like to compare the results area by area, and year by year. To do this, I need my main spread sheet to reference specific cells in other spreadsheets.</SPAN>
But there are two challenges I can’t figure out how to overcome.</SPAN>
- The cells I’m interested in are not always on the same row. (Ex. if one area doesn’t sell a specific product, that product is omitted. So one area may sell 3 products and would have Product A, Product B, Product and Product C in Rows 1, 2, and 3. Another area may sell 4 different products and would have Product B, Product D, Product Q, and Product R in Rows 1, 2, 3 and 4). Generally, I could manage this with VLOOKUP/INDEX, but part 2 confounds this. </SPAN>
- The Product names repeat themselves under different headers. </SPAN>
Volumes- </SPAN>
Product A </SPAN>
Volumes per day- </SPAN>
Product A </SPAN>
Prices- </SPAN>
Product A</SPAN>
Revenue-</SPAN>
Product A</SPAN>
So what I need it to do is look up the correct header, then look ONLY in the values below that header for the value I want.</SPAN>
*Note*: The Headers are always the same; Only the products (and consequently the row #) change. I have included an example below.</SPAN>
Output:
Volume per day</SPAN> | |||||||||
A</SPAN> | B</SPAN> | C</SPAN> | D</SPAN> | G</SPAN> | H</SPAN> | Q</SPAN> | S</SPAN> | T</SPAN> | |
Property 1</SPAN> | 2</SPAN> | 22</SPAN> | 15</SPAN> | 9</SPAN> | NA</SPAN> | NA</SPAN> | NA</SPAN> | NA</SPAN> | NA</SPAN> |
Property 2</SPAN> | 4</SPAN> | 22</SPAN> | NA</SPAN> | NA</SPAN> | NA</SPAN> | NA</SPAN> | 14</SPAN> | 9</SPAN> | 4</SPAN> |
<TBODY>
</TBODY>
Data: (Note- each "Property" is a different spreadsheet)
PROPERTY 1</SPAN> | Year:</SPAN> | 2011</SPAN> | ||
Volumes</SPAN> | ||||
Product</SPAN> | Account Major</SPAN> | Account Minor</SPAN> | Account Name</SPAN> | Total</SPAN> |
6000</SPAN> | 10</SPAN> | A</SPAN> | 730</SPAN> | |
6000</SPAN> | 20</SPAN> | B</SPAN> | 8030</SPAN> | |
6000</SPAN> | 22</SPAN> | C</SPAN> | 5475</SPAN> | |
6000</SPAN> | 30</SPAN> | D</SPAN> | 3285</SPAN> | |
Volumes Per Day</SPAN> | ||||
Product</SPAN> | Account Major</SPAN> | Account Minor</SPAN> | Account Name</SPAN> | Average</SPAN> |
6000</SPAN> | 10</SPAN> | A</SPAN> | 2</SPAN> | |
6000</SPAN> | 20</SPAN> | B</SPAN> | 22</SPAN> | |
6000</SPAN> | 22</SPAN> | C</SPAN> | 15</SPAN> | |
6000</SPAN> | 30</SPAN> | D</SPAN> | 9</SPAN> | |
Prices</SPAN> | ||||
Product</SPAN> | Account Major</SPAN> | Account Minor</SPAN> | Account Name</SPAN> | Average</SPAN> |
6000</SPAN> | 10</SPAN> | A</SPAN> | $ 0.50 </SPAN> | |
6000</SPAN> | 20</SPAN> | B</SPAN> | $ 1.10 </SPAN> | |
6000</SPAN> | 22</SPAN> | C</SPAN> | $ 1.90 </SPAN> | |
6000</SPAN> | 30</SPAN> | D</SPAN> | $ 0.70 </SPAN> | |
Revenue</SPAN> | ||||
Account Type</SPAN> | Account Major</SPAN> | Account Minor</SPAN> | Account Name</SPAN> | Total</SPAN> |
6000</SPAN> | 10</SPAN> | A</SPAN> | $ 365.00 </SPAN> | |
6000</SPAN> | 20</SPAN> | B</SPAN> | $ 8,833.00 </SPAN> | |
6000</SPAN> | 22</SPAN> | C</SPAN> | $ 10,402.50 </SPAN> | |
6000</SPAN> | 30</SPAN> | D</SPAN> | $ 2,299.50 </SPAN> |
<TBODY>
</TBODY>
PROPERTY 2</SPAN> | Year:</SPAN> | 2011</SPAN> | ||
Volumes</SPAN> | ||||
Product</SPAN> | Account Major</SPAN> | Account Minor</SPAN> | Account Name</SPAN> | Total</SPAN> |
6000</SPAN> | 10</SPAN> | A</SPAN> | 1460</SPAN> | |
6000</SPAN> | 22</SPAN> | C</SPAN> | 8030</SPAN> | |
6000</SPAN> | 90</SPAN> | Q</SPAN> | 5110</SPAN> | |
6300</SPAN> | 10</SPAN> | S</SPAN> | 3285</SPAN> | |
6300</SPAN> | 17</SPAN> | T</SPAN> | 1460</SPAN> | |
Volumes Per Day</SPAN> | ||||
Product</SPAN> | Account Major</SPAN> | Account Minor</SPAN> | Account Name</SPAN> | Average</SPAN> |
6000</SPAN> | 10</SPAN> | A</SPAN> | 4</SPAN> | |
6000</SPAN> | 20</SPAN> | B</SPAN> | 22</SPAN> | |
6000</SPAN> | 90</SPAN> | Q</SPAN> | 14</SPAN> | |
6300</SPAN> | 10</SPAN> | S</SPAN> | 9</SPAN> | |
6300</SPAN> | 17</SPAN> | T</SPAN> | 4</SPAN> | |
Prices</SPAN> | ||||
Product</SPAN> | Account Major</SPAN> | Account Minor</SPAN> | Account Name</SPAN> | Average</SPAN> |
6000</SPAN> | 10</SPAN> | A</SPAN> | $ 0.50 </SPAN> | |
6000</SPAN> | 20</SPAN> | B</SPAN> | $ 1.10 </SPAN> | |
6000</SPAN> | 90</SPAN> | Q</SPAN> | $ 0.10 </SPAN> | |
6300</SPAN> | 10</SPAN> | S</SPAN> | $ 9.00 </SPAN> | |
6300</SPAN> | 17</SPAN> | T</SPAN> | $ 14.00 </SPAN> | |
Revenue</SPAN> | ||||
Account Type</SPAN> | Account Major</SPAN> | Account Minor</SPAN> | Account Name</SPAN> | Total</SPAN> |
6000</SPAN> | 10</SPAN> | A</SPAN> | $ 730.00 </SPAN> | |
6000</SPAN> | 20</SPAN> | B</SPAN> | $ 8,833.00 </SPAN> | |
6000</SPAN> | 90</SPAN> | Q</SPAN> | $ 511.00 </SPAN> | |
6300</SPAN> | 10</SPAN> | S</SPAN> | $ 29,565.00 </SPAN> | |
6300</SPAN> | 17</SPAN> | T</SPAN> | $ 20,440.00 </SPAN> |
<TBODY>
</TBODY>