[Solved] Current not working w search

[dbird]

Cell h22 contains the string "birth".
Cell h23 contains = H22 & SEARCH("Feast"; CURRENT() )

Shouldn't cell h23 display the string "birth" followed by the string "#VALUE!"?

Instead cell h23 displays the string "birth" followed by the number 1.

What's going on?

Thanks,

Dave

[RusselB]

I think this might be due to the way CURRENT is being evaluated.
Specifically, in this case, I believe it's applying the concatenated string of "birth" and "Feast" into the evaluation of CURRENT, thus the SEARCH is seeing something like SEARCH("Feast";"birthFeast"), thus Feast is found and returning 1.

[MrProgrammer]

Cell h22 contains the string "birth". Cell h23 contains = H22 & SEARCH("Feast"; CURRENT() ). Shouldn't cell h23 display the string "birth" followed by the string "#VALUE!"? Instead cell h23 displays the string "birth" followed by the number 1. What's going on?

In this case, I suspect that CURRENT() has the value "Feast".

This function returns the result to date of evaluating the formula of which it is a part (in other words the result as far as that evaluation has got).

Here's how I read that:
• If you're using an example of CURRENT() from the documentation, the documentation will tell you the result of the function.
• If you're an Open Office developer, the result of the function can be determined from a study of the source programs.
• Otherwise the result of CURRENT() is undefined.

I recommend never using CURRENT(). I don't because I'm not a developer. Instead use an expression which has the value you want. In H23 that might be =H22&SEARCH("Feast";H22) though I don't know what you're trying to accomplish.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[RoryOF]

The Help file gives some worked examples of CURRENT()

CURRENT
This function returns the result to date of evaluating the formula of which it is a part (in other words the result as far as that evaluation has got). Its main use is together with the STYLE() function to apply selected styles to a cell depending on the cell content.
Syntax
CURRENT()
Example
=1+2+CURRENT()
The example returns 6. The formula is calculated from left to right as: 1 + 2 equals 3, giving the result to date when CURRENT() is encountered; CURRENT() therefore yields 3, which is added to the original 3 to give 6.
=A2+B2+STYLE(IF(CURRENT()>10;”Red”;”Default”))
The example returns A2 + B2 (STYLE returns 0 here). If this sum is greater than 10, the style Red is applied to the cell. See the STYLE function for more explanation.
="choo"&CURRENT()
The example returns choochoo.

[dbird]

Thanks for the replies.

I think RusselB probably has the most concise explanation for what is going on.

MrProgrammer, your suggestion does indeed work, but my example was constructed to help diagnose the problem.

In reality, the cell ref h22 is an approximately 500 character string of commands which ultimately returns the contents of a cell. What I am trying to do is to subsequently set the background color of the cell depending on the presence of the word "feast" in the string returned. So, I am trying to avoid an additional 500 character string within the search.

Here's a condensation of what an actual functioning cell content would look like:

<long function string yielding cell ref> & T(STYLE(IF(ISNUMBER(SEARCH("Feast"; "<long function string yielding cell ref>")); "GRN"; "Default")))

The <cell ref> yielded by the long function string has the correct background color, but the color is not preserved. Thus the attempt to use STYLE.

Any ideas?

Thanks,

Dave

[dbird]

Never mind everyone. I figured out how to do this. Simple. Here's how:

j22=<long function string yielding cell ref>

h22= j22 & T(STYLE(IF(ISNUMBER(SEARCH("Feast"; j22)); "GRN"; "Default")))

Works good. Thanks for all the comments.

Dave