-
Notifications
You must be signed in to change notification settings - Fork 56
FAQ
A worksheet can contain up to 256 sheets, and each sheet contains 256 columns and 8192 rows. However, if you split your data between multiple worksheets, then you can use "Linked Files", which allows you to use the data from one worksheet in another. There is no upper limit to the number of linked files you can use.
A label can be up to 512 characters in length, and numbers should be in the range 1e-99 and 9.99e+99.
Use the /Range Value option, instead of /Copy.
Use map mode, /Worksheet Window Map Enable.
The worksheet will zoom out and show formulas as +, values as # and labels as ".
Hit Enter to exit map mode when you're done.
1-2-3 predates Unicode, but Lotus had the foresight to know that codepages were not the answer.
They came up with their own solution called LMBCS.
Internally, 1-2-3 stores everything in LMBCS, but it can only read and display ASCII. We're working on fixing this with full UTF-8 support, but this is a big job and the work is not complete (see #73).
Until that work is finished, sometimes characters are transliterated (i.e. a similar looking ASCII symbol is displayed). The character is being stored correctly internally, you can check with @CODE and checking the reference table here.
There are various settings that could cause this, try these:
- There might be an empty window, try
/Worksheet Window Clear. - Do you have any hidden columns? Try
/Worksheet Column Display. - Are there hidden worksheets? Try
/Worksheet Hide Disable. - Cell contents can be hidden with
/Range Format Hidden.
If you still can't solve the mystery, please open an issue.
A circular reference is simply a formula that eventually references itself. The simplest example would be if cell A1 contained +A1*2.
If you see the CIRC indicator, you can check /Worksheet Status to see the first cell that contains a problematic formula.
This isn't a fatal error, but if you weren't expecting to see this it does strongly suggest you have made a mistake in your worksheet.
You should examine and correct the formula in that cell, then repeat this process until the CIRC indicator goes away.
Use /Data Fill. The parameters can be values, formulas, cell references or dates.
You can enter dates or times into /Data Fill using the format 01-Jan-2023, or 11:42 AM.
For example, if you want a new row for every week, you could enter:
- Range
A1..A128, Start01-Jan-2023, Stop01-Jan-2024, Step7
Note: Remember to use
/Range Format Date, otherwise you will just see a date serial number.
If you want each row to be a month, enter the step as 1m instead.
- Range
A1..A128, Start01-Jan-2022, Stop01-Jan-2023, Step1m.
| A | |
|---|---|
| 1 | 01-Jan-2022 |
| 2 | 01-Feb-2022 |
| 3 | 01-Mar-2022 |
| 4 | 01-Apr-2022 |
| 5 | 01-May-2022 |
This also works for years (1y) and quarters (1q).
Note:
10mmeans "ten months", if you are trying to increment by minutes, use10min.
Yes, it works perfectly under WSL2 on Windows 10 and higher.
Yes, a port is underway (see #86).
The file is locked, you or another user has the file open in another terminal.
1-2-3 is asking if you would like to open the file anyway, in read-only mode. If you do open the file read-only, but decide you want to make changes, you can try to retrieve a reservation (i.e. a lock) from the /File Admin Reservation menu.
If that doesn't work (the other user is still using it) you can save your changes to a different file.
1-2-3 uses advisory locks, see man 2 flock. You can use the standard lslocks command to see which process holds the lock.
Sealing is not really a security feature, the password is stored in the file in plain text.
You can just run strings worksheet.wk3, and it should be visible in the output.
Copy the worksheet, then use /File Combine Substract. All the values will be zeroed but formulas will remain intact.
Use Ctrl+C, this is different to how macro debugging worked on DOS.
You might use this if you're debugging a macro in STEP mode, or you want to interrupt a macro that uses {?}.
Some macro commands automatically advance the cell the macro is reading from, so you can't have one cell that contains more commands after {OPEN}. You should continue the macro in the next cell.
This is so that you can specify actions on error, for example if {OPEN} fails.
No, you can use {GOTO}A:A1~ instead.
If you select /Graph Name Use ... then the graph will be displayed, which pauses your macro.
This might not be what you wanted if you were trying to update data ranges.
The solution is to use {GRAPHON GRAPHNAME,nodisplay} instead, which sets the graph as current but doesn't automatically display it.
You can use the formula @MOD(date, 7), which will give a number indicating the day (0 = Sat, 1 = Sun, 2 = Mon, and so on).
If you need the day as a string, you can use @CHOOSE(@MOD(@TODAY, 7), "Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri").
Note: This will only return the correct weekday for dates after March 1st 1900, the reason is described here.
UNIX time is the number of seconds since 1970-1-1, Lotus time is the number of days since 1900-1-1, with the fractional part representing intraday seconds.
Therefore, you can use the formula (unixtime / 86400)+@DATE(1970, 1, 1).
You can display this as a time or date using the /Range Format Date menu.
This neat solution appeared in the January 1990 edition of Lotus Magazine:
31 - @CHOOSE(@MONTH(@TODAY),31,0,@CHOOSE(@MOD(@YEAR(@TODAY),4),2,3,3,3),0,1,0,1,0,0,1,0,1,0)
You can replace @TODAY with a cell reference that contains a date number.
Another solution would be to subtract the first day of next month from the first day of this month.
@DATE(2023,3,1) - @DATE(2023,2,1)
Perhaps you want to sum the last n occupied columns of a table.
There is no @OFFSET function in 1-2-3, one solution is using @COORD and @@ instead.
To @SUM the last 12 occupied cells in A1..IV1, you could use this:
@SUM(@@(@COORD(1, @COUNT(A1..IV1) - 11, 1, 1) & "..IV1"))
This uses @COUNT to find the last occupied column in the row, then moves 11 columns backwards.
The @COORD function can translate that into a start and/or end cell address string, which we can concatenate (&) with the range operator ...
We need to use @@ to convert that string into a range before passing it to @SUM.
The most common cause for this is that you tried to use /Worksheet Insert, but the worksheet is full.
The confusion is because "full" doesn't necessarily mean the cells are occupied, just that they have had an attribute assigned. For example, perhaps you set a format for the entire column or row. If that's the case, try resetting the format for some cells you aren't using with /Range Format Reset.
If you press EndHome 123 will jump to the furthest "occupied" cell. If this is far away from where your work area, then you can try resetting some cells in the area to make more room.
There is a limit of 48 parameters to most functions. You should reorganize your worksheet to use @HLOOKUP() instead, which has no such limit.
There was a common trick among 1-2-3 users to mark a range Unprotected using /Range Unprot, which highlights the range. 1-2-3 support more formatting options internally, but most are only visible when printing. This could be improved in future.
It's used to embed printer commands or format lines with /Data Parse Format-Line.
Some people would use it to create "comment labels", labels that would not be visible when printed.
There is no base conversion @function, although we could add one in future.
You have to do it the old-fashioned way!
These can get long, for example, here is a formula to display the value in A1 as a 32-bit hex integer using @MID and @MOD.
@MID("0123456789ABCDEF",@MOD(A1/2^28,16),1)&@MID("0123456789ABCDEF",@MOD(A1/2^24,16),1)&@MID("0123456789ABCDEF",@MOD(A1/2^20,16),1)&@MID("0123456789ABCDEF",@MOD(A1/2^16,16),1)&@MID("0123456789ABCDEF",@MOD(A1/2^12,16),1)&@MID("0123456789ABCDEF",@MOD(A1/2^8,16),1)&@MID("0123456789ABCDEF",@MOD(A1/2^4,16),1)&@MID("0123456789ABCDEF",@MOD(A1,16),1)
The inverse formula, converting a 32-bit hex integer into a value, would be something like this.
@FIND(@MID(B1,0,1),"0123456789ABCDEF",0)*2^28+@FIND(@MID(B1,1,1),"0123456789ABCDEF",1)*2^24+@FIND(@MID(B1,2,1),"0123456789ABCDEF",2)*2^20+@FIND(@MID(B1,3,1),"0123456789ABCDEF",3)*2^16+@FIND(@MID(B1,4,1),"0123456789ABCDEF",4)*2^12+@FIND(@MID(B1,5,1),"0123456789ABCDEF",5)*2^8+@FIND(@MID(B1,6,1),"0123456789ABCDEF",6)*2^4+@FIND(@MID(B1,7,1),"0123456789ABCDEF",7)
If the result of a calculation cannot fit in a column, it is displayed as ********.
This is so that you don't see a truncated result and accidentally think it is the real value!
You can adjust the width of the column using /Worksheet Column Set-Width.
If the result remains as ******** no matter how wide you make the column, then the result exceeded 9.99e99, the largest value 1-2-3 can display.
Note: This means you cannot enter the number Googol into 1-2-3.
Use Format Lines, /Data Parse Format Create. See here for a tutorial.
You can import CSV using /File Import Numbers, and even automate this with a Keystroke Macro. See here for more information.
Note: As of 5660440f, there is an
@SYSTEM()function to run external commands.
Alternatively, you can write a macro that runs commands and reads the output. I use a macro like this to fetch stock prices (simplified):
{SYSTEM "stocks.sh GOOGL > /tmp/stockprice.txt"}
{OPEN "/tmp/stockprice.txt", "r"}
{READLN C1}
{CLOSE}
stocks.sh is just a shell script like this:
#!/bin/bash
declare api="https://query1.finance.yahoo.com/v7/finance/quote"
declare filter=".quoteResponse.result[].regularMarketPrice"
curl -s "${api}?symbols=${1:-^GSPC}" | jq -r "${filter}"If you want to see a sample worksheet with this setup, I have an example here.
See Also, AutoExec macros.
Yes! Normally you would bind a macro to Alt+R that will refresh any data, but if you want this to happen automatically you need to use the Signal Macro.
To use this feature, create a macro that updates the data, then name it \1 (use /Range Name Create).
For example, maybe you want to import fresh data with /File Import when it's available, so your macro would look something like {GOTO}A5~/fin{CE}/path/to/input.csv~.
That means move to cell A5 in the current sheet, then use /File Import Numbers to import a csv file.
Now your script can use kill -USR2 $(pidof 123) to inform 1-2-3 that it should update!
Try adjusting /Graph Options Scale Skip until it looks neater.
Yes, if you send 1-2-3 a SIGUSR1 it will create a screenshot in the current working directory. This was never documented, and probably for internal testing.
There are undocumented @functions @ISAAF() and @ISAPP() but they don't do anything on UNIX.
The original 1-2-3 UNIX release refused to start if your $LINES exceeded 90, and would crash if your terminal had more than 256 $COLUMNS.
This has been fixed in the Linux port, which has no hardcoded limits on terminal size.