Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Query using Match/Index
Every month my company outputs an excel file in the format (not that the
project number is only used once on a differnt row than everything else): project Desc Amount 1.2.1 labor 10 material 5 other 15 1.2.X labor 12 material 33 other 11 If, however, a project (using project 1.2.1 as an example) did not incur material expenses then the report would look like: project Desc Amount 1.2.1 labor 10 other 15 1.2.X labor 12 material 33 other 11 I'd like to set up a query to find out the cost for a particular project and a particular desc... for example, what were the labor costs for project 1.2.1. I can easily use match/index if the project number was on every row, but since it is only on one row, I am stumped. Any help would be much appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Query using Match/Index
I can easily use match/index if the project number was on every row,
but since it is only on one row, I am stumped. It's easy to set it up to be completely filled in a helper col, then just use the helper in the index/match Assume the top project number is in A2 In D2: =A2 In D3: =IF(A3="",D2,A3) Copy D3 down to the last row of data in cols B/C, to return the required "filled" replica of col A -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Bara Reyna" wrote: Every month my company outputs an excel file in the format (not that the project number is only used once on a differnt row than everything else): project Desc Amount 1.2.1 labor 10 material 5 other 15 1.2.X labor 12 material 33 other 11 If, however, a project (using project 1.2.1 as an example) did not incur material expenses then the report would look like: project Desc Amount 1.2.1 labor 10 other 15 1.2.X labor 12 material 33 other 11 I'd like to set up a query to find out the cost for a particular project and a particular desc... for example, what were the labor costs for project 1.2.1. I can easily use match/index if the project number was on every row, but since it is only on one row, I am stumped. Any help would be much appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Query using Match/Index
Sure this is an Excel group, but what you described would be perfect for an
Access query. Maybe you should look at this: http://office.tizag.com/accessTutori...essqueries.php HTH, Ryan-- -- RyGuy "Max" wrote: I can easily use match/index if the project number was on every row, but since it is only on one row, I am stumped. It's easy to set it up to be completely filled in a helper col, then just use the helper in the index/match Assume the top project number is in A2 In D2: =A2 In D3: =IF(A3="",D2,A3) Copy D3 down to the last row of data in cols B/C, to return the required "filled" replica of col A -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Bara Reyna" wrote: Every month my company outputs an excel file in the format (not that the project number is only used once on a differnt row than everything else): project Desc Amount 1.2.1 labor 10 material 5 other 15 1.2.X labor 12 material 33 other 11 If, however, a project (using project 1.2.1 as an example) did not incur material expenses then the report would look like: project Desc Amount 1.2.1 labor 10 other 15 1.2.X labor 12 material 33 other 11 I'd like to set up a query to find out the cost for a particular project and a particular desc... for example, what were the labor costs for project 1.2.1. I can easily use match/index if the project number was on every row, but since it is only on one row, I am stumped. Any help would be much appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index, match, multiple IFs query | Excel Worksheet Functions | |||
if, Isnumber, Match, &index query | Excel Discussion (Misc queries) | |||
index and match query | Excel Worksheet Functions | |||
INDEX AND MATCH QUERY ? | Excel Worksheet Functions | |||
index,match,match on un-sorted data | Excel Worksheet Functions |