Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
simona2000 pe Simpatie
 | Femeie
25 ani
Galati
cauta Barbat
25 - 53 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 787
|
|
I am using a Table (CTRL+T) but if inserting a row in the middle of the table, the formula won't update.
This formula is old formula:
="F"&IF(ISNUMBER(F10);COUNTA($C$10:C10);1)
When inserting a row in the middle, let's say between rows 33 and 34, the newly inserted row takes the formula from the row below it and so 2 rows have the same formula, the 2nd showing an "Inconsistent formula" error.
You would have to click that error notification, then click the "Restore to Calculated Column Formula" option every time you insert a new row.
Here is new formula that doesn't require that anymore. It entirely depends on ROW() and COLUMN() references of current row and current column:
="F"&IF(ISNUMBER(INDIRECT(ADDRESS(ROW()-1;COLUMN()+3)));ROW()-10;1)
|
|
| pus acum 2 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 787
|
|
Another one to start numbering from current row without adding a fixed row value (eg. ROW()-10 from above).
The following formula will start counting from 1, assuming the first ROW()-1 is not a number (eg. is a table header with text), and will continue counting regardless of row.
Works well when there is a possibility to insert new rows above the formula.
=IF(NOT(ISNUMBER(INDIRECT(ADDRESS(ROW()-1;COLUMN()))));1;INDIRECT(ADDRESS(ROW()-1;COLUMN()))+1)
|
|
| pus acum 2 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 787
|
|
In newer versions of Excel - i.e. Excel 2021 and Excel 365 - you can also use the SEQUENCE function.
However, it's not as dynamic as I'd like.
When using a range (not a table):
- assuming you want a sequence starting from A2 to A10 (for my items in cells B2 to B10)
- you would have to anticipate if you are likely to insert more items in the range, otherwise it won't expand.
=SEQUENCE(COUNTA(B2:B50))
Inside a table:
- spill ranges do not work inside tables - they error - no workaround that I know of with a spill formula like SEQUENCE
|
|
| pus acum 1 luna |
|