condition of 2 cells = result of 3rd cell

[Perry Diels]

Hello,

I'll try to ask my question with an example:

Between [ ] = cell numbers

SHEET 1

[A1] item1 [B1] 150
[A2] item2 [B2] 290
[A3] item3 [B3] 80
[A4] item4 [B4] 159

And so on .... in fact the A column are products and the B column are the
respective prices.

SHEET 2

Here I have made drop down lists, those are working perfectly. This has been
done via "Data/Validation/Allow (list)"
In the drop down list I see all the items of the sheet 1 A column (item1,
item2, item3 etc. ...)

Finally what I want is, when I select an item in a drop down list that its
corresponding price (the respective B column of sheet 1) appears in the
target cell. Hence if the target is let's say [C1] I want something like "if
B1= the value of sheet1[A1], then = [B1]
In this example the result would be 150.

Sorry for my somewhat clumsy explanation, I hope you understand what I mean

Thanks in advance,
Perry

 

[Peo Sjoblom]

Use the table you have for a vlookup, i.e.
assume you select from the validation list
and the result is in C1, now use

=IF(C1="","",VLOOKUP(C1,Sheet1!A1:B50,2,0))

it should lookup the selected value and then return the
price..

 

[immanuel]

In C1, try:

=VLOOKUP(B1,Sheet1!A1:B4,2,FALSE)

/i.

 

[Perry Diels]

Hello Immanuel,

Thanks a lot for your time and your input. Your formula works perfectly for
what I intended to do.
My project (automated price list) is almost ready, I have a couple of
questions left related to that formula.

When I delete the data in the cells with the drop down lists (the B column
of SHEET 2) then in the C columns (where you sent me the formula for)
appears #N/A. Perhaps you have a trick whenever a cell in the B column is
empty it's corresponding C cell is filled with 0.00. I was thinking of
something with the ELSE command in the formula, but I couldn't find out.

The last question is if a clickable button could be inserted that resets all
the B cells to empty (and hence the C cells to 0.00)

Kindest regards,
Perry



-----------------------------------------------------------------------

In C1, try:

=VLOOKUP(B1,Sheet1!A1:B4,2,FALSE)

/i.

Hello,

I'll try to ask my question with an example:

Between [ ] = cell numbers

SHEET 1

[A1] item1 [B1] 150
[A2] item2 [B2] 290
[A3] item3 [B3] 80
[A4] item4 [B4] 159

And so on .... in fact the A column are products and the B column are the
respective prices.

SHEET 2

Here I have made drop down lists, those are working perfectly. This has been
done via "Data/Validation/Allow (list)"
In the drop down list I see all the items of the sheet 1 A column (item1,
item2, item3 etc. ...)

Finally what I want is, when I select an item in a drop down list that its
corresponding price (the respective B column of sheet 1) appears in the
target cell. Hence if the target is let's say [C1] I want something like "if
B1= the value of sheet1[A1], then = [B1]
In this example the result would be 150.

Sorry for my somewhat clumsy explanation, I hope you understand what I mean

Thanks in advance,
Perry

 

[Perry Diels]

Hello Peo,

Thanks for your answer. The formula you proposed does not work for me, maybe
I use it wrong or you misinterpreted my question. The answer of Immanuel has
the working formula, maybe if you have a look at it, thinks will be clear.

Anyway thanks for your time.

Best Regards,
Perry

 

[Peter]

Hi Perry,

Try modifying the Immanuel's formula to:

=IF(ISNA(VLOOKUP(B1,Sheet1!A1:B4,2,FALSE)),0,VLOOKUP(B1,Sheet1!A1:B4,2,FALSE))

This will give you 0.00 instead of #N/A Error. The cells should be
formatted as Number to get the 0.00 format.

Regarding the button that resets all the column B cells to empty try
the following:
1. Click View > Toolbars > Control Toolbox and insert Command Button
2. Right-click the button, click View Code and paste the following
line of code:
ActiveSheet.Range("b:b").ClearContents
3. Go back to the sheet with the button and click the Exit Design Mode
button (the green triangle) on the Control Toolbox then click the
button to clear the B column.

Hope this helps,

Peter

 

[Peter]

Hi Perry,

Try modifying the Immanuel's formula to:

=IF(ISNA(VLOOKUP(B1,Sheet1!A1:B4,2,FALSE)),0,VLOOKUP(B1,Sheet1!A1:B4,2,FALSE))

This will give you 0.00 instead of #N/A Error. The cells should be
formatted as Number if you want to see the zero as 0.00.

Regarding the button that resets all the column B cells to empty try
the following:
1. Click View > Toolbars > Control Toolbox and insert Command Button
2. Right-click the button, click View Code and paste the following
line of code:
ActiveSheet.Range("b:b").ClearContents
3. Go back to the sheet with the button and click the Exit Design Mode
button (the green triangle) on the Control Toolbox then click the
button to clear the B column.

Hope this helps,

Peter

 

[Perry Diels]

Hi Peter,

Obviously your Email server says it all SYMPATICO, thanks ;-) It all works as expected, apart from the fact that we are now involved with a macro in our document. On some of the computers in our LAN this specific ..xls file opens very slooowly, it takes like 30 second before the question "enable Macro's" appears. On some other workstations it opens in a couple of seconds, I could not find a pattern why. Perhaps someone has a solution for accelerating or a workaround?

Best Regards,

Perry





----------------------------------------------------