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
> Index Func, Office 2010    
 
   
SemiAuto40
post Feb 8 2019, 05:01 PM
Post#1



Posts: 656
Joined: 3-April 12
From: L.A. (lower Alabama)


Trying to help a friend with a very complicated formula.
CODE
=IF(ROWS(E$5:E22)>$E$1,"",INDEX('Master (2)'!H$2:'Master (2)'!H$2:H4065,SMALL(IF(Master[Product For]=$B$1,ROW(Master[Product For])-ROW('Master (2)'!$H$2)+1),ROWS(E$5:E22))))

This formula is on tab 'Asta' and looks at tab 'Master (2)' to look for any matches of the text string in $B$1 of the selected tab 'Asta'.

The problem is how do I use a text string wildcard for the value found in $B$1?? For example if the value in $B$1 is ABC how do I include from tab Master (2) rows which contain ABCD?

Thank you for your time.
Go to the top of the page
 
Debaser
post Feb 9 2019, 05:05 AM
Post#2



Posts: 84
Joined: 11-October 18



Instead of:

IF(Master[Product For]=$B$1

use:

IF(ISNUMBER(SEARCH($B$1,Master[Product For]))

to locate it anywhere in the cell. If you want case sensitivity (eg abc is not the same as ABC) then use FIND instead of SEARCH.
Go to the top of the page
 
SemiAuto40
post Feb 9 2019, 09:48 AM
Post#3



Posts: 656
Joined: 3-April 12
From: L.A. (lower Alabama)


*My original formula (which currently works to only retrieve the exact text in $B$1) had curly braces at both ends (I think this has something to do with an array), and I forgot to show them in my original post - or they got removed somehow.
CODE
{=IF(ROWS(E$5:E22)>$E$1,"",INDEX('Master (2)'!H$2:'Master (2)'!H$2:H4065,SMALL(IF(Master[Product For]=$B$1,ROW(Master[Product For])-ROW('Master (2)'!$H$2)+1),ROWS(E$5:E22))))}


My new formula is now with debaser's suggestion is:
CODE
{=IF(ROWS(E$5:E22)>$E$1,"",INDEX('Master (2)'!H$2:'Master (2)'!H$2:H4065,SMALL(IF(ISNUMBER(SEARCH($B$1,Master[Product For])),ROW(Master[Product For])-ROW('Master (2)'!$H$2)+1),ROWS(E$5:E22))))}


**For some reason when I place this in the formula all I see is the exact text including the braces in the cell itself??

If I exclude the braces like this and place the formula behind a cell:
CODE
=IF(ISNUMBER(SEARCH($B$1,Master[Product For])),"Found")

then the value showing in the cell is FALSE. I know that the contents of Master[Product For] on the Master (2) tab is present in the form that my cell $B$1 contents are. I'm not getting the excel ISNUMBER function aspect of this.


Note: Since the original formula works as it is - to get ONLY the exact text setting in $B$1, that's not the problem. The problem is trying to get wildcard values to what is in $B$1 like the COUNTIF function does.
CODE
=COUNTIF(Master[Product For],"*"&$B$1&"*")


Wildcards don't seem to work when I put the same terms in the original working formula.


I greatly appreciate a resolution to this if I can get one to work.
This post has been edited by SemiAuto40: Feb 9 2019, 10:15 AM
Go to the top of the page
 
Debaser
post Feb 9 2019, 10:11 AM
Post#4



Posts: 84
Joined: 11-October 18



Don't type the curly brackets. Type the formula without them but then press Ctrl+Shift+Enter and not just enter. You should then see the curly brackets appear around the formula.
Go to the top of the page
 
SemiAuto40
post Feb 9 2019, 10:42 AM
Post#5



Posts: 656
Joined: 3-April 12
From: L.A. (lower Alabama)


Thank you debaser for that tip of Ctr,Shft,Enter!

Now about using a wildcard so that the contents of $B$1 can be searched with. The base problem is not using the cell reference $B$1, which already works... but using what is in $B$1 with a wildcard on both sides just like the COUNTIF function.



Thank you very much debaser!!! I think I've got it now, and I couldn't have done it without you!


Thank you for your time!
This post has been edited by SemiAuto40: Feb 9 2019, 11:31 AM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th February 2019 - 12:30 PM