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