Excel issues... experts in the house?

[mrhnau]

I'm running into some problems with Excel. Any experts in the house? Related to data validation, and coordination of data validation among multiple cells.

Thanks!

Jason

 

[rutherford]

Post your question. I'm not an expert, but I'll see what I can do and I'm sure somebody else will know if I don't.

 

[mrhnau]

I've got two issues:

1) If certain text exists in a series of cells, I want to add 10 to a different cell. For instance, if the word "Fast" exists in cells A1:A10, add 10 to cell B1

2) I've got a sheet containing a list. Lets say it contains 3 points of data.
Lets say "Short, Medium, Tall" in cells C1:C3. in cells D13 I have text giving a range of what I consider "short, medium tall", for instance I might want to say short is " < 5'5 ", medium " > 5'5 and < 6' ", tall " > 6' ". On a seperate sheet I've created a pull down menu using a list calling on the defined name called "height". On this seperate sheet, I'd like to be able to select "short" from the pull down menu, and have the cell right beside automatically update to the correlated " < 5'5 ".
I realize this would be easy using a relatively simple IF statement, but the list I'm dealing with are going to be in a few hundred pull down options, so IF statements would be a bit unreasonable.

Hope this is somewhat clear...
Thanks!

MrH

 

[oldnewbie]

Have you played with the Vlookup function...
It may be what your looking for

 

[mrhnau]

Have you played with the Vlookup function...
It may be what your looking for

thanks, thats getting close. I tried both Hlookup and Vlookup. The return value is the string I'm searching for.

Also tried playing with match. I'm trying to get a working IF statement. The closest thing I've been able to get is:

=IF(MATCH(Data!A1,F4:J4,0),1,0)
=IF(MATCH(CharacterData!A1,F4:J4),1,0)

The problem is that if Data!A1 does not exist in the list, I get a #N/A. Know of any form of work-around? Thanks!

MrH

 

[oldnewbie]

Well.. I may be off here, but if you nested your IF statement, so that a FALSE would trigger a .. "" .. string.... that way it would come up blank instead of displaying the #n/a.

Does that help?

 

[mrhnau]

Tried something similar. What I'm seeing is the Match statment (or vlookup,hlookup), does not return a proper value, just a N/A. I can't get any binary operator to acknowledge it... I just get another N/A.

Might be wrong here... tried the help pages, as well as doing a few google searches. can't seem to get around it.

 

[oldnewbie]

I take it that there could be an instance where Data!A1 is missing as a normal function.?? Not sure that you can fix the problem then...

It seems that the MATCH, V&Hlookups need all data present..all the time.


Sorry I couldn't help more...

 

[mrhnau]

I take it that there could be an instance where Data!A1 is missing as a normal function.?? Not sure that you can fix the problem then...

It seems that the MATCH, V&Hlookups need all data present..all the time.


Sorry I couldn't help more...

No, Data!A1 is always there, its fixed and valid. I'm checking that it exists in the row F4:J4. Sometimes the value of Data!A1 is in F4:J4. If it is, I want a 1 from the IF statement. If it is not, I get a N/A. Trying to see if I can change that N/A to a 0 (False).

 

[oldnewbie]

I get it now.

Don;t know of a way right now..

I'll do some looking, maybe I can stumble on something....

 

[oldnewbie]

How about nesting the ERROR.TYPE function to check for #N/A and return the text you want?

 

[mrhnau]

Think I found part of the solution, there is a function called ISNA (is N/A).

=IF(ISNA(IF(MATCH(Data!A1,F4:J4,0),1,0)),0,1)

That answers one of the problems. This allows me to do conditionals based on the existence of the string. Now the other problem still remains.

2) I've got a sheet containing a list. Lets say it contains 3 points of data.
Lets say "Short, Medium, Tall" in cells C1:C3. in cells D13 I have text giving a range of what I consider "short, medium tall", for instance I might want to say short is " < 5'5 ", medium " > 5'5 and < 6' ", tall " > 6' ". On a seperate sheet I've created a pull down menu using a list calling on the defined name called "height". On this seperate sheet, I'd like to be able to select "short" from the pull down menu, and have the cell right beside automatically update to the correlated " < 5'5 ".
I realize this would be easy using a relatively simple IF statement, but the list I'm dealing with are going to be in a few hundred pull down options, so IF statements would be a bit unreasonable.

 

[oldnewbie]

Oh okay, ISNA is better...

Let me look at the other stuff....

 

[oldnewbie]

The second issue sounds more like the Vlookup Function.

Column C Column D
Short <5'5
Medium >5'5
Tall >6'

VLOOKUP(A1,C13,4,FALSE)

would return <5'5 for short, >5'5 for Medium, and >6' for Tall