Using IF statements to insert text

[Guest]

Hi all,

I wonder if someone can help ...

I'm constructing a statement that checks two cells for values, if those
values are as expected, then another cell should display a specific value.

i.e. :
If I6 = "US" And I10 = "Test" Then
G25 = "TestingUS"

I'd like the value to appear in the G25 cell (basically so I can use it for
other things).

I don't know that much about VB, hence why I'm getting stuck on how to get
it to actually insert the value into the G25 cell.

Any help would be greatly appreciated!

Thanks,

Sian

 

[Guest]

in G25 put :

=IF(AND(I6="US",I10="Test"),"TestingUS","")

HTH

 

[Guest]

Hiya,

Thanks for the info - I did have that in place, when I was testing to see
whether I could get it to work. Problem is I have several values to check
for and several results to display, so I need to put this into VB and an
'elseif' statement. Any ideas?

Thanks again,

Sian

 

[Guest]

in VB

If application.and(range("i6")="US",range("I10")="Test") then
Range("G25")="TestingUS"
Else
If application.and(range("i7")="UK",range("I10")="Test") then
Range("G25")="TestingUK"
End iF
End if

HTH

 

[Andrew Taylor]

Sian
Depending on how complex the rules are, it might still be possible
to do what you want without using VBA, which would be my
preference.: for example something like

=IF(I10="Test","Testing","Normal")&IF(I6="US","US","Non-US")

You might also be able to use a lookup table and the VLOOKUP
function to translate the i10 and i6 values to what you want.


However, if you're definitely set on using VBA, then there are
two ways you could go about it: either a macro that you
run on demand or on some Event, e.g. Worksheet_Calculate,
that puts the value into G25. This would look something like

Sub SetG25()
Dim i10Val, i6Val, g25Val
i10Val = ThisWorkbook.Sheets("Sheetname").Range("I10")
i6Val = ThisWorkbook.Sheets("Sheetname").Range("I6")

if I10Val = "Test" and i6Val="US" Then
g25Val = "TestingUS"
elseif I10Val = "Test" and i6Val="France" Then
g25Val = "TestingFrance"
elseif .... etc
else ' default
g25Val = "Invalid entries in I6/I10"
End if

ThisWorkbook.Sheets("Sheetname").Range("G25") = g25Val

End Sub


or (preferably IMO), you could make a user-defined
function to return the correct value. In G25 you would put
=myFunc(I10, I6)

and in VBA:

Function myFunc(v1, v2)
if v1 = "Test" and v2 ="US" Then
myFunc = "TestingUS"
elseif .... etc as before
else ' default
myFunc = "Invalid entries"
End if
End Function


hope this helps
Andrew Taylor

 

[Guest]

That's great, thank you.

Can I be a real pain and now ask whether I can change this to display values
in a named list rather than an actual value? (another thing I want to do!).
I can do a validation, but it always gives me a drop down list, and I just
want to display the full list straight off without a drop down.

 

[Guest]

Sian,
Do you mean you have a named range called (for exampe"MyList")
and you want to display the nth item?

Can you give an example?

 

[Guest]

Ok ... long story short, I have two drop down boxes (created with a named
list and a validation).

The first one you pick a location. The second one you pick a product.
Depending on these two values, I'd like a list of values to appear - these
are set up as named lists at the moment (but don't have to stay that way).

Basically the choices depend on the area (US, EMEA, APAC) and the product
selected.

Does that make sense?

Thanks (again!)

Sian

 

[Guest]

Hi,
Look at this site as appears to offer what you want. I'll have to
sign off now but may be back later in the day.

http://www.contextures.com/xlDataVal01.html