series of if/else statements within an if/else statement

  • Thread starter Thread starter Legacy 306954
  • Start date Start date
L

Legacy 306954

Guest
Hi

Done my best to make this work and have failed. So would apppreciate some help.

excel 2007

I need this.. in excel talk:

if c61="THW", then if d31>=.05 but < 1.5, then d61=j40, else if d31>=1.5 but <2.5, d61=j41, else if d31>=2.5 but < 3.5, d61=j42, else if d31>=3.5 but < 4.5, d61=j43, else if d31>=4.5, d61=j44, [else if c61="Own", (user can enter own value in d61)]

Thanks for your help
David
 
Last edited by a moderator:
Hello

does this do what you want?

Code:
=IF(C61="THW",IFERROR(LOOKUP(D31,{0.05;1.5;2.5;3.5;4.5},J40:J44),"OWN"),"OWN")
 
Upvote 0
Surely, own input will overwrite any formula?
 
Last edited:
Upvote 0
Hi Tony

I was trying to explain what I wanted in as non prose a manner as possible... And yes, of course you're right that the formula would be overwritten, but it should only appear in d61 if C61 says THW [from a drop down menu of two in that cell]. Otherwise if the user has selected 'Own' from the drop down, they should be able to enter their own number.

Hope that's clearer and sorry for confusion.
Cheers
David
 
Upvote 0
Thanks but that produces the word 'own' in D61, when it should contain a value from that list, depending on the value of c31, and then only if c61 says 'THW, selected from a drop down [which I shold ahve mentioned.. sorry.]
 
Upvote 0
=IF(C61="THW",IFERROR(LOOKUP(C31,{0.05;1.5;2.5;3.5;4.5},J40:J44),"OWN"),"OWN")

looks like i had the wrong cell, try the modified version
 
Upvote 0
Still displays 'OWN' rather than a value. If I change the IFERROR outcome to "", the cell goes blank, so I presume the formula is generating an error.
 
Upvote 0
David,

Don't know if you saw my original post which I subsequently edited, because I thought I had totally misread your question?
That was asking if your 'excel talk' was pseudo-formula or pseudo-vba?
As far as I understand your situation .....

If selection from D61 dropdown = "THW" then D61 = J? dependent upon C31
If selection from D61 dropdown = "OWN" then user enters value in D61


.....you will need to use vba ?
 
Upvote 0
Tony, yes you've understod the problem precisely. I don't know VBA so am going to have to rely on help here then. I've found workrounds for other instances where this board have said use VBA, by using conditional formatting and other tricks, but I can't see a work round for this. Thanks for your help anyway,.. and perhaps VBA Geek will be able to help me.
 
Upvote 0
Actually perhaps there is a workround. If I retain the drop down, but use two seperate lines for the possible outcomes. So if the drop down reads OWN, signify that the user must enter data in one line, and put the 'else' part of the if statement of the THW formula as "" so it doesn't display anything. I'll have to figure out how to lock cells though, so the formula can't be accidentally overwritten
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top