My proficiency with Excel has been a tremendous advantage for me in my career…
[/quote]
Maybe you can help me.
I’ve got a problem in that the shipping cost is coming back as an error, but only when “MW” is chosen as the destination region. Basically, cell D22 looks at D3 to find the shipping destination, then references the third column in table G26:J31 and enters the appropriate shipping charge.
It works correctly with every region, except MW and I can’t figure out why.
In cell E24, a customer discount is applied according to the following rules: If the total invoice is greater than $40,000 then a discount is applied based on the customer type.
So…
If(D20>40,000,?,0)
and
VLOOKUP(D4,K3:L5,2,FALSE)
So, do I embed the VLOOKUP function in the [value if true] area of the IF function (where I have the question mark right now)? If not, how do I link the 2 functions in a cell so that if the statement is true, then go look up the appropriate discount based on the customer type entered in a different cell altogether?
Thanks again.
(yes, this is my homework but the assignment has already been turned in and the penalties assessed. I"m trying to figure it out for next time.)
In cell E24, a customer discount is applied according to the following rules: If the total invoice is greater than $40,000 then a discount is applied based on the customer type.
So…
If(D20>40,000,?,0)
and
VLOOKUP(D4,K3:L5,2,FALSE)
So, do I embed the VLOOKUP function in the [value if true] area of the IF function (where I have the question mark right now)? If not, how do I link the 2 functions in a cell so that if the statement is true, then go look up the appropriate discount based on the customer type entered in a different cell altogether?
Thanks again.
(yes, this is my homework but the assignment has already been turned in and the penalties assessed. I"m trying to figure it out for next time.)
[/quote]
Not sure without playing around with it, but I would try adding another column and use the “or” function and embed based off that. I’m not really sure though.
[quote]usmccds423 wrote:
Not sure without playing around with it, but I would try adding another column and use the “or” function and embed based off that. I’m not really sure though. [/quote]
I’ll play around with it, thanks.
In general, can you nest a VLOOKUP within an IF function?
[quote]usmccds423 wrote:
Not sure without playing around with it, but I would try adding another column and use the “or” function and embed based off that. I’m not really sure though. [/quote]
I’ll play around with it, thanks.
In general, can you nest a VLOOKUP within an IF function?
[/quote]
[quote]usmccds423 wrote:
Not sure without playing around with it, but I would try adding another column and use the “or” function and embed based off that. I’m not really sure though. [/quote]
I’ll play around with it, thanks.
In general, can you nest a VLOOKUP within an IF function?
[/quote]
you should be able to as the IF fn is just a logic fn in that whatever is returned needs to match the criteria you set for it. To get your desired outcome you might have to nest a few vlookups as each loop can only be assigned to one “True” I believe - not really sure what you’re goal is, though. (i.e. =if(vlookup()=x,“True”,if(vlookup()=y),“True”,if(vlookup()=z,“True”,…,“False”) … that SHOULD work, of course you might have edit it down and if you make a mistake in “programming” it it might be tedious to find the mistake.
[quote]polo77j wrote:
not really sure what you’re goal is, though…[/quote]
Something simple, like “If the total invoice is less than $100, then go look up the shipping charge based on their region. If false, then enter $15 for shipping.”
IF(A1<100,=Vlookup (d3,d5:f10,3,false),0) where A1 contains the invoice amount.
To my inexperienced eyes the above formula says “If the invoice is less than $100 then look at cell D3 for the region and use it to refer to a table contained in cells D5:f10 and refer to column 3 for the shipping charge. If the invoice is greater than or equal to 100, then return 0 for the shipping charge”
[quote]LoRez wrote:
Yeah, VLOOKUP can be nested in an IF.
For the condition part of the IF, all that matters is if it resolves to a true or false. For the “if true” and “if false” parts, it can be anything.[/quote]
I tried to put a jpeg of Jamie Eason’s cheeks, but it only kicked back a #N/A … I don’t think I was doing it right
[quote]LoRez wrote:
Yeah, VLOOKUP can be nested in an IF.
For the condition part of the IF, all that matters is if it resolves to a true or false. For the “if true” and “if false” parts, it can be anything.[/quote]
I tried to put a jpeg of Jamie Eason’s cheeks, but it only kicked back a #N/A … I don’t think I was doing it right :([/quote]
You’ll have to define it so you can pass in the row and column info for the cell. (Unless there’s a way to get the path of the calling cell, but I don’t know how to do that)
[quote]LoRez wrote:
Yeah, VLOOKUP can be nested in an IF.
For the condition part of the IF, all that matters is if it resolves to a true or false. For the “if true” and “if false” parts, it can be anything.[/quote]
I tried to put a jpeg of Jamie Eason’s cheeks, but it only kicked back a #N/A … I don’t think I was doing it right :([/quote]
You’ll have to define it so you can pass in the row and column info for the cell. (Unless there’s a way to get the path of the calling cell, but I don’t know how to do that)
This actually inserts the image into the file, versus just showing it… but here’s the proof of concept
Public Function LoadImage(ByVal url As String, ByVal cellPosition As Range)
Dim pic As Picture
Application.ScreenUpdating = False
With cellPosition
Set pic = .Parent.Pictures.Insert(url)
With .Offset(, 0)
pic.Top = .Top
pic.Left = .Left
pic.height = .height
pic.width = .width
End With
End With
Application.ScreenUpdating = True
[quote]LoRez wrote:
This actually inserts the image into the file, versus just showing it… but here’s the proof of concept
Public Function LoadImage(ByVal url As String, ByVal cellPosition As Range)
Dim pic As Picture
Application.ScreenUpdating = False
With cellPosition
Set pic = .Parent.Pictures.Insert(url)
With .Offset(, 0)
pic.Top = .Top
pic.Left = .Left
pic.height = .height
pic.width = .width
End With
End With
Application.ScreenUpdating = True
