Can someone please help adding arguments to a formula or provide a better solution?

I have inherited a spreadsheet and need to add some data fields to it and am not familiar with how they set it up. The particulars are below:

Data Entry cells
Cell J4 is an entry field where they will enter text data
Cell M4 is an entry field where they will enter number data

Existing Formula cells
Z4: =IF($J4="Open",$M4,0)
AA4: =IF(ISERR(SEARCH("LAND",J4,1))=FALSE,M4,0)
AC4: =IF(ISERR(SEARCH("LAND",J4,1))=TRUE,IF(J4<>"Open",M4,0),0)

Explanation of above:
If they enter "Open" in J4, the value in M4 will display in Z4.
If they enter "Land" in J4, the value in M4 will display in AA4
Any other value in J4, the value in M4 will display in AC4

Needed:
Inserted cells which are now AB and AD
AB: If "HBP-Land" is in J4, the value in M4 will display in AB4
AD: If any other value containing "HBP" is in J4, the value of M4 will display in AD4


I have tried a ton of variables but cant get it to work. Help on this would be GREATLY appreciated!!

Shane

Try these:

AB4: =IF(J4="HBP-Land",M4,"")

AD4: =IF(AND(ISNUMBER(SEARCH("*HBP*",J4)),J4<>"HBP-Land"),M4,"")

Hope this helps.

Pete

I tried something very similar and it will input in AB4/AD4 (depending on input) however the formula in AC is also grabbing it since it is a value other than "Land" or "Open".

Any other ideas to fix this?

A little messy, but given the criteria, this works. Tested each of your inputs and I assume the goal is to get 1 answer. Changed the logic in AA4 & AC4.

Good luck!

ExcelForm.com mshaneweaver 11.6.14.xlsx

Thanks but this doesnt really do what I need. They cant pull from a drop down box. The most common entries in the text box is "Open" and "Land", soon to be followed by "HBP-Land" and "HBP-*". Other items may be entered in J4 and if they are, then the M4 value should fall in AC.

For instance if someone enters "xyz" in J4, the M4 value will be displayed in AC. If someone types "HBP-Land" it will be in AB however if "HBP-xyz" is entered, then M4 is displayed in AD.

Shane

Ok I worked on what you gave me TxCPA and it works! I realized your drop down was just for testing.

Thanks guys!!!

Yep exactly - sorry meant to remove all that prior to uploading - glad you figured that out and you have a solution!

If you could mark as solved that would be good too!

TxCPA