UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Display The Results Of One Cell Based On Another, Office 2013    
 
   
halefamily104
post Jan 13 2020, 01:48 PM
Post#1



Posts: 529
Joined: 7-November 10



Hello UA!

I am working with a formula I received from UA that works perfectly! Basically, the formula:

=IF(ISERROR(FIND("TX", AllTracking!AA16)) = FALSE, AllTracking!AB16, "")

displays the results of AB16 if AA16 match "TX". That works great but now I need to add some layers. I need to add not only "TX", but also "CR", "CTRE", "SA" and "DPE". How can I modify my formula to include searching for these items. Using OR maybe??

Additionally, the cells to look for is actually AA16:AJ16.

Any help will be appreciated!

Thanks!

Go to the top of the page
 
halefamily104
post Jan 13 2020, 02:24 PM
Post#2



Posts: 529
Joined: 7-November 10



Here is a formula that would be perfect but I cant get it to work....

=IF(AA17="HV",DA17,IF(AA17="TX",DE17, IF(AA17="SRA",DL17,IF(AA17="GAD",DS17,IF(AA17="PHQ",DO17, IF(AA17="CSSRS",DW17,""))))))

just trying everything...
Go to the top of the page
 
cheekybuddha
post Jan 13 2020, 07:27 PM
Post#3


UtterAccess Moderator
Posts: 12,064
Joined: 6-December 03
From: Telegraph Hill


Hi,

What doesn't work about your last formula?
CODE
=IF(AA17="HV",DA17,IF(AA17="TX",DE17, IF(AA17="SRA",DL17,IF(AA17="GAD",DS17,IF(AA17="PHQ",DO17, IF(AA17="CSSRS",DW17,""))))))


It looks like it should work.

--------------------


Regards,

David Marten
Go to the top of the page
 
halefamily104
post Jan 13 2020, 09:16 PM
Post#4



Posts: 529
Joined: 7-November 10



Hi cheekybuddha!

Thanks for the response. hat_tip.gif I have the formula in AC16. In AA15 I have "HV" and in AB16, I have a date. The date should be returned in DA16 but DA16 it is blank.

Not sure what I am doing wrong.
This post has been edited by halefamily104: Jan 13 2020, 09:17 PM
Go to the top of the page
 
cheekybuddha
post Jan 14 2020, 03:13 AM
Post#5


UtterAccess Moderator
Posts: 12,064
Joined: 6-December 03
From: Telegraph Hill


Ah! your're going the wrong way round! The formula needs to be in the cell where you want to see the result.

A formula won't 'put' a value into another cell - you will have to use VBA if you want to do that.

I think you want as the formula in DA16:
CODE
=IF(SUMPRODUCT(--($AA16={"HV","TX","SRA", "GAD", "PHQ", "CSSRS"})), $AB16, "")


Then drag down over the other rows required.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
halefamily104
post Jan 14 2020, 09:38 AM
Post#6



Posts: 529
Joined: 7-November 10



Thanks again cheekybuddha!


This works perfect! If there is TX AND HV in AA16, will it still return the result? If not, is there a way to modify it?

Go to the top of the page
 
cheekybuddha
post Jan 14 2020, 10:51 AM
Post#7


UtterAccess Moderator
Posts: 12,064
Joined: 6-December 03
From: Telegraph Hill


So you mean that AA16 might contain a string of multiple values to look for?

Then it becomes a bit trickier!

Try:
CODE
=IF(
  SUMPRODUCT(
    --ISERROR(FIND("TX", $AA16)),
    --ISERROR(FIND("HV", $AA16)),
    --ISERROR(FIND("SRA", $AA16)),
    --ISERROR(FIND("GAD", $AA16)),
    --ISERROR(FIND("PHQ", $AA16)),
    --ISERROR(FIND("CSSRS", $AA16))
  ),
  "",
  $AB16
)

--------------------


Regards,

David Marten
Go to the top of the page
 
halefamily104
post Jan 14 2020, 12:20 PM
Post#8



Posts: 529
Joined: 7-November 10



Ok, thanks! How do I get this code to run?
Go to the top of the page
 
cheekybuddha
post Jan 14 2020, 12:35 PM
Post#9


UtterAccess Moderator
Posts: 12,064
Joined: 6-December 03
From: Telegraph Hill


Stick it in cell DA16


--------------------


Regards,

David Marten
Go to the top of the page
 
halefamily104
post Jan 14 2020, 12:49 PM
Post#10



Posts: 529
Joined: 7-November 10



I tried entering the formula but it copies to the cells below. I tried to condense it to see if that would work but I am getting a #Name error...

Here is what I tried..


=IF(SUMPRODUCT(ISERROR(FIND("TX", $AA16)),ISERROR(FIND("HV", $AA16)),ISERROR(FIND("SRA", $AA16)),ISERROR(FIND("GAD", $AA16)),SERROR(FIND("PHQ", $AA16)),ISERROR(FIND("CSSRS", $AA16))),"",$AB16)
This post has been edited by halefamily104: Jan 14 2020, 12:49 PM
Go to the top of the page
 
cheekybuddha
post Jan 14 2020, 01:36 PM
Post#11


UtterAccess Moderator
Posts: 12,064
Joined: 6-December 03
From: Telegraph Hill


The unary operator (double dashes -- ) are the secret sauce!

I split it over several lines to try and make it clearer for you to see what the separate components are.

Here's the single line version:
CODE
=IF(SUMPRODUCT(--ISERROR(FIND("TX", $AA16)), --ISERROR(FIND("HV", $AA16)), --ISERROR(FIND("SRA", $AA16)), --ISERROR(FIND("GAD", $AA16)), --ISERROR(FIND("PHQ", $AA16)), --ISERROR(FIND("CSSRS", $AA16))), "", $AB16)


(It will work with multiple lines as well, you just need to double-click the cell before entering the formula or enter it directly in the formula bar at the top.)

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
halefamily104
post Jan 14 2020, 01:37 PM
Post#12



Posts: 529
Joined: 7-November 10



Nevermind! Got it to work!!!!!

Thanks so much CHEEKYBUDDHA!!!

You ROCK!

Until next time! notworthy.gif pompom.gif

fundrink.gif
Go to the top of the page
 
cheekybuddha
post Jan 14 2020, 04:00 PM
Post#13


UtterAccess Moderator
Posts: 12,064
Joined: 6-December 03
From: Telegraph Hill


thumbup.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
Debaser
post Jan 16 2020, 07:37 AM
Post#14



Posts: 168
Joined: 11-October 18



FYI, a shorter version of that would be:

=IF(COUNT(FIND({"TX","HV","SRA","GAD","PHQ","CSSRS"}, $AA16)),$AB16,"")
Go to the top of the page
 
cheekybuddha
post Jan 16 2020, 08:30 AM
Post#15


UtterAccess Moderator
Posts: 12,064
Joined: 6-December 03
From: Telegraph Hill


Cool, I didn't realise you could pass an array to Find()! thumbup.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
Debaser
post Jan 16 2020, 08:33 AM
Post#16



Posts: 168
Joined: 11-October 18



Yes - it then returns an array of results, hence the use of COUNT (which, usefully, ignores error values).
Go to the top of the page
 
cheekybuddha
post Jan 16 2020, 08:52 AM
Post#17


UtterAccess Moderator
Posts: 12,064
Joined: 6-December 03
From: Telegraph Hill


hat_tip.gif

I love learning new things on this site!

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th January 2020 - 08:30 AM