MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Conditional display based on another cell


Posted by Eric on December 17, 2001 9:10 PM

I would like to have one cell with some letters
example...

D2

Then I would like to have the cell next to it display a number 4 for the letters D2 and only those letters. But if there was a D1 there instead I would like a 6 in the next cell. Or if there was a D2C/N1 I would like an 8, and so on. Not necessarily continuing on in that order.

So I have my own list of predefined texts with corresponding numbers associated with them (which are actually in hours) and I would like to be able to have a formula that will display the hours I want when the cell before it has the appropriate Text (I would like it to be case insensitive but it is not necessary). Any help is greatly appreciated.

I could do this in C but I wouldnt know how to put it in and I dont know VBA. Thanks in advance.


Posted by Eric on December 17, 2001 9:13 PM

Re: This will also be done on Excel 95

This will also be done on Excel 95 which I am not allowed to upgrade which would probably make it easier to do. Thanks Again for the help.

Eric

Posted by Eric on December 17, 2001 10:17 PM

Re: Conditional Display

I have determined how to do what I call a conditional display from reading other posts but Now I will have trouble when I copy it to other cells.

Lets say I have Column B with all my Text fields and Column C has the number of hours that correspond to each cell in Column A. Then I can use nested if-else's to do what I want.

Example: I am in D5 and D4 has the data that I am
looking at.
=IF(D4=B1,C1,IF(D4=B2,C2,IF(D4=B3,C3,IF(D4=B4,C4,IF(D4=B5,C5,IF(D4=B6,C6,"Invalid"))))))

This will work for what I am trying to do but If I try to copy it all the References will get incremented or decrimented. The only one that I want to get incremented or decremented is the D4 reference since my defined columns B and C do not move. Does anyone know how to restrict Excel from incrementing or decremening for only a certain references? Thanks.

Eric

Posted by Aladin Akyurek on December 17, 2001 10:29 PM

Re: Conditional Display

Eric --

You don't need IF to do what you want.

In D5 enter: =IF(COUNTIF($B$1:$B$6,D4),VLOOKUP(D4,$B$1:$C$6,2,0),"Invalid")

From your description I gathered that you have 6 entries in B from B1 on and another 6 entries in C from C1.

Aladin

========

Posted by Eric on December 17, 2001 10:36 PM

Re: Thank You <- Conditional Display

Thank You Aladin,

That is exactly what I am looking for. --