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.

> 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.
>
>

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.
> >
> >