[Solved] IF(defined name; ; )

[me231]

=IF(phase_ii?;C113;C21)
so in the sheet i bought - they have created a drop down index selection phase 1, phase 2,
the cell name phase_ii? has the following formula $u!1
=O4="2 Phases"
returns true or false based upon drop down selection
if true then c113
if false then c21


the selection box has 2 choices -
it then auto fills the top "Summary items

it works in excel but not in open office i need help getting this to work as intended
so in open office
if(phase_ii =true;c113;c21) is not working
the short hand?

thanks in advance

[FJCC]

I don't see any named cell ranges. Which cell should phase_ii? refer too. Also, I don't think a range name can contain a question mark.

[me231]

apologies - i cut and pasted only portin of spreadshet - i have defines the cell as is in the original sheet

[FJCC]

I used the menu Insert->Names->Define to make the name phase_ii refer to U1. I then replaced all of the mentions of phase_ii? with phase_ii using Find & Replace. There are no errors in the sheet now.

[me231]

thanks trying right now - i hate that it would be that easy - i will let you know - thank you again

[me231]

not replacing - will keep trying and report

[me231]

the cmdF or edit find and replace all is not working - brute force 1 at a time works - thank you

[me231]

myedit find replace all - does not find ?
so i am not able to remove this character
OOA 4.1.3

[FJCC]

How are you searching? Do you have regular expressions turned off? I searched for phase_ii? and replaced with phase_ii.

[Zizi64]

me231,

Did you used the Enter key in the cell reference box when you named the cell U1?

(There is not any named cell in your attachments. You can check it by the Navigator.)

[me231]

did you check the second file - i forgot in the first -
i tested cmf f with cells of text - as a test - nonsense 4x then cmd f re[place all and viola success

but if i insert a "?" in the cell - it does not find the text and then at the the end of the text as in the ex. phase_ii?, replace phase with "cbcbc" for example all is "phase" is replaced but not the "?"
weird right?

[me231]

regular expressions enabled in system preferences - so thats not it - thanks

[RoryOF]

Regular Expressions must be enabled in Find and Replace, More Options.

[me231]

they are - i unchecked then recheck or ticked box still no cmd f for "?" - but thanks again for not giving up on this topic

[Zizi64]

did you check the second file - i forgot in the first -

There is not any NAMED CELL (nor NAMED CELL RANGE) in your second attachment.

[me231]

ok checking - give me a sec

[me231]

this is it rigfht - named cell - U1
?
did i do it incorrectly

[Zizi64]

Please use 64 color depth PNG images with a larger resolution. Its size will be smaller than 128 KiB (what value is the limit in this forum), and the image will not be such blurry...

[FJCC]

To search for a question mark, regular expressions should be turned off.
The file I posted earlier includes the named range. You should duplicate what you see in that file.

[me231]

Please use 64 color depth PNG images with a larger resolution. Its size will be smaller than 128 KiB (what value is the limit in this forum), and the image will not be such blurry...

off topic - i know how to adjust color on display olr rgb etc and am able to create profiles but i confess i don't know how to select or make 64 color for screenshot?

[me231]

To search for a question mark, regular expressions should be turned off.
The file I posted earlier includes the named range. You should duplicate what you see in that file.

i opened your fjcc file and i was able to verify that my file and named cells look like they are equal -
i opened the native file and the operation - with expression off and on - only adds "?" to the cells it finds and after the "replace all" operation . i have added the full file i bought xlsx - maybe it is just he gnome. on my machine

note interestingly - i copied the named cell phase_II? to use to replace the now .ods " phase_ii? " that occurs when i open file with OO. in excel the sheet works fine
i hesitate to load the native excel file bc i paid for it - so i trust others will not steal? i do not know the policy on this sharing - to be clear i did not create the file i am sharing rather purchased
well i can't share native it is 275kb - need to trim in half - will try again

[Zizi64]

how to select or make 64 color for screenshot?

I am using a free third party image manager software named IrfanView. I just paste the result of the Windows PrtScr or Alt-PrtScr into the IrfanView, and then I modify the Color depth to a custom value (64 color). Finally I save the image into the lossless compression type PNG format. Here is a sample, and its size is only 106 KiB.

Click on the image for a better view.

[me231]

ok makes sense i will grab it - thanks

[FJCC]

I downloaded the Resi_Land file you uploaded. I added a named range called Phase_II that points to U1. I then did a Find and Replace with phase_ii? in the Search For box, Phase_II in the Replace with box, Match Case not selected, and, under More Options, Regular Expressions turned off and the Search In box set to Formulas. The cells that had been showing Err 508 or Err 509 now show numbers.

[me231]

i don't get it at all -= has to be my machine
do i need to delete the phase_II? nameed cell

just not working - argh!!

[me231]

ok - major senior moment - change regular expressions in cmdF
more options". i had been searching and changing the wronf parameter... thank you so much it all works now... phew!!!! i greatly appreciate the schooling this morning - issue solved -
now one further lesson - why in the conversion from xlsx to ods - does this type of thing happen in formulas all the time or lots - the use of ; vs , in formulas for example -

[Zizi64]

You can not use the F&R function for managing the names of the named cells and cell ranges. There is a specific function for manage the properties of the named cell.