Using Find in formula as opposed to cell result

[cogswel__cogs]

Is it possible to use find in formula as opposed to cells result.

Thanks for any help

 

[Joe4]

Not sure what you mean.

Can you explain in more detail, maybe provide an example of what it is you are trying to do?

 

[cogswel__cogs]

Going to be using a formula like below.
Its being used in a report where I fear sometimes people might insert tabs into summary so it might change or it might not.
There are times the the number of tabs might be changed without insertion. I was hoping to have a formula (find) look at the cell and the first instance of :$A so I could take the value of the next 3 characters so that I could then have macro recalculate proper alignment.

So basically I was trying to find first :$F$ and first instance of &" below.
Then My macro will just change the number in the # for the formulas.


=SUMPRODUCT(SUMIF(INDIRECT("'"&Control!$F$4:$F$24&"'!$B$3:$b$1000"),$B9,INDIRECT("'"&Control!$F$4:$F$24&"'!$d$3:$d$1000")))

 

[cogswel__cogs]

Merry Christmas BTW

 

[cogswel__cogs]

Was still hoping to find way to do above.
Any help is greatly appreciated.

 

[Joe4]

I am sorry, but I really do not understand your explanation.

It might be best if you can lay out an actual example for us, denoting what actual data looks like (providing examples), and what should happen based on it.

 

[cogswel__cogs]

I have a workbook that many team members work on in individual tabs and summary pages.
The summary sheet pulls info from other tabs using below sumif formulas.

So on summary sheet I have in
cell B2 =SUMPRODUCT(SUMIF(INDIRECT("'"&Control!$F$4:$F$24&"'!$B$3:$b$1000"),$B9,INDIRECT("'"&Control!$F$4:$F$24&"'!$d$3:$d$1000")))

I want to have at top of rows (in this case RowB) return the last row being used in control tab reference within Cell B2's formula. (I.E. 24 above)

So I was hoping to use something like below
cell B1 = mid(b2,find(":F$",b2,1)+1,2)

Which I was hoping to pull result 24, only find is looking at the result of the cell not the formula text. Is there a way to use find or another formula to search the actual text of the formula in the cell.

Only find looks in the value B2
I want to find to look at the actual formula within the cell if possible.

Knowing the ending row is important because it should allign with a count from a row of unique tabs listed on control page.
If a member adds a tab to that list on control tab by inserting/deleting the formula changes. However if they just add to the bottom or change some other ways then the summary page will not update the control ranged reference.
So I plan to use this cell result(24) compared counting the expected tabs on the control tab in conditional formats to highlight disconnects in a particular row.

Thank you so much for helping.

 

[Joe4]

Which I was hoping to pull result 24, only find is looking at the result of the cell not the formula text.

That is correct. That is how Excel formulas work - they look at what is being returned in the cell, and not the formula behind it.
I think what you are trying to do will require VBA.

We can make our our function in VBA to do that. Here is one that should work specifically for your situation, and should be able to handle up to row 99999.

Function GetRow(rng As Range, fnd As String) As String
'   rng = cell address where formula resides
'   fnd = the string before the row number to look for
'   Example: =GetRow(B2,":$F$")

    Dim frm As String
    Dim temp As String
    Dim arr() As String
    
'   Get formula from cell
    frm = rng.Formula
    
'   Get row number from formula
    temp = Mid(frm, InStr(frm, fnd) + 4, 5)
    arr = Split(temp, "&")

    GetRow = arr(0)

End Function

So, you would use it like any other function in Excel, and enter this formula in the cell:

=GetRow(B2,":$F$")

I documented the arguments of the function right in the function above.

 

[cogswel__cogs]

Used the function you created works like a charm
BTW never used split, that's a cool command

Many thanks

 

[Joe4]

You are welcome!

Glad I was able to help.