Excel 2007 : Can I use the IF function with drop down lists?

[traceylreed]

I am trying to figure out how to populate one workbook with the colaborative information from 4 other workbooks. The 4 workbooks have drop down lists that are used for the information needed. My first question would be, is there a way to say "IF Board #783 is selected (from the drop down), then it will also populate in another workbook"

I have uploaded the spreadsheet for better understanding. Benches 1-3 and the Hardware bench, are where the users will enter their information. The "Info" tab is the information for the drop down lists. Then the PCB .... Tab is where I would like the information to colaboratively populate.

In the PCB .... tab, the board #'s are across the botton and the #'s up the left are to represent batch 1,2,3 etc. So, if a board # on this sheet matches one that the "benches" chooses, I want it to populate in the box, how many defects they had (which is the "Defects observed" column on the "benches" workbooks. Does this make any sense at all?

[dilipandey]

HI tarceylreed,

I have filled in the information in Row 5 in Bench 1 sheet, now where this information should appear in the PCB.. tab ?

See attached: Post Wave data collection sheet.zip


Regards,
DILIPandey

<click on below 'star' if this helps>

[traceylreed]

Hi dilipandey,

Ok, so what I am looking for is the board # (041143) and the # of defects (defects observed) to populate in the first box of the 041143 column on the PCB tab.

Thank you for your help.

[dilipandey]

the 041143 column on the PCB tab.

That is my main question... where is that in PCB tab ?

Regards,
DILIPandey

<click on below 'star' if this helps>

[traceylreed]

OH! I apologize, we use the last three numbers of the board, so box one in the 143 column.

[dilipandey]

Hi Tracey,

You can use the below array formula:-

{=IFERROR(INDEX('Bench 1'!$A$5:$E$18,MATCH("041"&'PCB Defects @ PW'!AF$28&'PCB Defects @ PW'!$A27,'Bench 1'!$A$5:$A$18&'Bench 1'!$C$5:$C$18,0),5),"")}

see attached:- Post Wave data collection sheet.zip

for box 1 of 143 column ... also I have dragged the formula to cover 1 and 2 for all boards .. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[traceylreed]

Hello again!

Could you possibly explain what you did? I am really new to this and would like to understand how you do this.

Thank you so much for your help!!

[dilipandey]

you are welcome tracy,

Below is the explanation of the formula:-

{=IFERROR(INDEX('Bench 1'!$A$5:$E$18,MATCH("041"&'PCB Defects @ PW'!AF$28&'PCB Defects @ PW'!$A27,'Bench 1'!$A$5:$A$18&'Bench 1'!$C$5:$C$18,0),5),"")}

Above I have indexed the range A5:E18 and picked the value from the row

where 041143 is matching in range 'Bench 1'!$A$5:$A$18 AND 'PCB Defects @ PW'!$A27 is matching in range 'Bench 1'!$C$5:$C$18.. so basically where 041143 and 1 matched in the respective ranges in the sheet 'PCB Defects @ PW'

and from column

5

and at the end I have included a error handler i.e., iferror which works like
=iferror(<formula>,"")

Which means if the formula output is an error then show BLANK. Hope this helps. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[traceylreed]

Thank you so much! Yes that all makes sense except one part, when you choose "match" and it has 'Bench 1'!$C$5:$C$18in there, why do we have to choose this when it is the E column that the total amount of defects is being pulled from.

[dilipandey]

Hi Tracey,

It is being used there to match 'PCB Defects @ PW'!AF$28&'PCB Defects @ PW'!$A27 in the range 'Bench 1'!$C$5:$C$18 i.e., it is matching 1 in the Bench 1 sheet in range C5:C18

Hope this helps.

Regards,
DILIPandey

<click on below 'star' if this helps>

[traceylreed]

Hello again.

Ok, I guess I'm still no understanding the signifigance of needing column "C" in the range at all. We are not pulling information from that list, are we? I thought we were just using column A and column E's information. Ugh, lol.

[dilipandey]

Ok.. no worries

You can get rid of from that formula portion

cheers

Regards,
DILIPandey

<click on below 'star' if this helps>

[traceylreed]

=IFERROR(INDEX('Bench 1'!$A$5:$E$18,MATCH("041"&'PCB Defects @ PW'!AF$28&'PCB Defects @ PW'!$A26,'Bench 1'!$A$5:$A$18&'Bench 1'!$C$5:$C$18,0),5),"")

Ok, so if I take out the "'Bench 1'!$C$5:$C$18" from the formula and I click in the box it shows the formula but no information is showing. Like, for the 143 box, it shows 8 when we keep this part of the formula intact, (which is correct) but if I remove this part of the formula, the 8 is no longer there. Does that make sense?

I'm just tryiing to make sense of this in my own head. lol

[dilipandey]

=IFERROR(INDEX('Bench 1'!$A$5:$E$18,MATCH("041"&'PCB Defects @ PW'!AF$28&'PCB Defects @ PW'!$A26,'Bench 1'!$A$5:$A$18&'Bench 1'!$C$5:$C$18,0),5),"")

Get rid of the bold part highlighted above ..

Regards,
DILIPandey


<click on below 'star' if this helps>

[traceylreed]

Hi dilipandey,

If I remove the highlighted area, it still isn't working for me. It gives me nothing. Am I doing something wrong?? I have so much to learn!

[dilipandey]

Hi tracey,

I removed the highlighted and got following:-

=IFERROR(INDEX('Bench 1'!$A$5:$E$18,MATCH("041"&'PCB Defects @ PW'!AF$28,'Bench 1'!$A$5:$A$18,0),5),"")

Check this one and let me know. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[traceylreed]

That seemed to do the trick! Thanks.

[dilipandey]

Cheers

Regards,
DILIPandey

<click on below 'star' if this helps>