BusSeriesQuestion

[laingds]

I think I'm on the right track but I can't seem to figure this out.

We have an inventory of bus parts and I want to be able to figure out
how many series of buses a part fits.

I'm trying in Column Y to have an IF statement which will look across
from ColJ to ColX and if the cell has a Y in it, then it fits the
series.

Bus Series
ColG ColJ ColK ColL...to ColX ColY

7551 7586 7601... 8066
7585 7591 7644... 8095
Part#
102665 Y Y Y
102862 Y
101164 Y Y Y... Y

Formula in ColY =IF(AND(J5="Y",K5="Y",L5="Y",M5="Y")=TRUE,($J$2& "-"&$M
$3),($J$2& "-"&$L$3))

So for;
part# 102665 in ColY should read 7551-7644
part# 102862 in ColY should read 7551-7585
part# 101164 in ColY should read 7551-8095

Suggestion/Thoughts ???

Dale

 

[Don Guillett Excel MVP]

I think I'm on the right track but I can't seem to figure this out.

We have an inventory of bus parts and I want to be able to figure out
how many series of buses a part fits.

I'm trying in Column Y to have an IF statement which will look across
from ColJ to ColX and if the cell has a Y in it, then it fits the
series.

        Bus Series
ColG    ColJ    ColK    ColL...to ColX  ColY

        7551    7586    7601... 8066
        7585    7591    7644... 8095
Part#
102665  Y       Y       Y
102862  Y
101164  Y       Y       Y...    Y

Formula in ColY =IF(AND(J5="Y",K5="Y",L5="Y",M5="Y")=TRUE,($J$2& "-"&$M
$3),($J$2& "-"&$L$3))

So for;
part# 102665 in ColY should read 7551-7644
part# 102862 in ColY should read 7551-7585
part# 101164 in ColY should read 7551-8095

Suggestion/Thoughts ???

Dale

Hard to tell your layout from this so
"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[Max]

This might do it for you
In Y5: =IF(J5="","",$J$2&"-"&INDEX($J$3:$M$3,COUNTIF(J5:M5,"Y")))
Copy down