I’m trying to set up a situation where it only says if the cell is empty or not. To that end, I have tried =IF(sheet1.T3>0),1,0. This gives me #NAME? The same happens when I try =IF(sheet1.T3<>""),1,0.
Apparently I’m getting the syntax wrong, but I’m not seeing how. All I want is for it to look at T3 on the first sheet, and if it’s not empty put a 1 in the cell. If it is empty, leave it 0.
Suggestions?
I’m obviously still not understanding something. I went to that setting, set it to Excel A1.
=IF(SHEET1!T3.0),1,0 AND =IF(SHEET1!T3<>""),1,0 now give me Error 509. Same thing if I try =SHEET1!T3>0,1,0 and =SHEET1!T3<>"", 1,0.
And yes, I clicked Use English Function Names the moment I saw it.
=IF(Sheet1.T3>0;1;0) or =IF(Sheet1.T3="";0;1)?
So far using the syntax you suggested is just shifting from Err:509 back to #NAME?
Well, try this - FunctionIF_test.ods (7.6 KB)
Will the formula in cell B2 on the Formula sheet still work if you change T3 on Sheet1? And what does this formula look like to you? I’m really interested in how this formula looks in your Calc
That works. It works precisely the way I’d expect it to.
Put any value in T3 (including 0), B2 returns a 1. Empty it, B2 is 0. Just like the formula says it should.
I give. I’m stumped.
But copy and paste your exact same formula into my spreadsheet, it returns #NAME?
Mine is saved as an .xlsx. That may be a factor.
I would love to, but I can’t see your spreadsheet… Can you share it?
Spell Tree.xlsx (19.7 KB)
It’s something that I’m working on as a supplement for a game. It has A LOT of conditional formatting in it, which may affect matters, I don’t know.
Yes, we were looking for the cause of the error in the wrong place 
- please try =IF(Fundamentals.T3="";0;1)
Oops. It’s been 9 years since I used spreadsheets this extensively, I forgot renaming the sheet would create this problem.
However, that didn’t solve it. #NAME?
It’s okay - we still find this error today. And will fix it! Please see this - Spell Tree.xlsx (19.4 KB)
Did you really try the formula @JohnSUN suggested, or have you only renamed the sheet, and kept the exclamation mark, like in =IF(Fundamentals!T3="";0;1)?
I copied and pasted his suggestion right off of his reply into the cell, just as he wrote it. Now granted, I still have the spreadsheet defaulting to Excel, so maybe the lack of the exclamation point could be part of the problem.
I dl’d this and brought it up. That’s putting numbers in the cells of the “Nuts & Bolts” page without any apparent regard to the cells being referenced on “Fundamentals.” In addition, it made the “Fundamentals” page go haywire. Here’s a screenshot.
@GrumpyCurmudgeon Do you mean that cells T4:T6 are empty now? Oh no, I broke this to make sure the formulas on Nuts & Bolts show 0 in the correct place
There was =A17, =SUM(A11;J17), =SUM(A11;J17)
“Do you mean that cells T4:T6 are empty now?”
That’s part of it. Also that E through N, 15 through 17 have gone blank and the backgrounds went a bit weird on O through U, 15 through 17. There’s basically a big stripe of weirdness across the speadsheet now, 3 rows long and 17 columns wide.
“Oh no, I broke this to make sure the formulas on Nuts & Bolts show 0 in the correct place.”
Ah, okay. Then I need to swap things around, so that zeroes in the front page will show as zeroes on Nuts & Bolts, whereas anything over zero shows up as one.
It happened before you attached the file to this post… It probably happened as early as 12/08/2022, 11:00:11
So in other words it came to you that way?
Hmmm… it wasn’t that way before I posted it. I had to close it out to send it.
Ewkaaayyy… But okay, I’ll work on it.
Yeah, that’s conditional formatting throwing extra things in that I’m pretty sure I didn’t ask for. Dunno why that is, but I found it and took it out.
I dunno why it wasn’t working before, but it’s working now. Maybe it was the same glitch that added all that conditional formatting.
Thanks
Going back and forth between Calc and Excel context may add a number of different things. Conditional formatting is conceivably one such area. The developers frequenting this site may know more about whether that may be touched in “translation”.
Only use Excel file format in Calc for a work in progress if you are sure that you must do this.
The originating cause of your original #NAME issue was most likely that you used Excel syntax in Calc. The ! separator between sheet name and cell reference is Excel syntax. The correct separator in Calc (as also apparent from the help suggestions) is a period ..
If the Sheet1 part of the cell reference were interpreted correctly as a sheet name in your original formula, the formula would also have been updated accordingly when you changed the sheet name to Fundamentals.
