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:
deiutza20
 | Femeie
21 ani
Bacau
cauta Barbat
21 - 44 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 17 ani
Postari: 2228
|
|
I use an Excel Table and most times I filter it via Slicers then clear the filter, my selection moves to the beginning of the table and it's annoying to always click a cell in column A then hit CTRL + DOWN.
So here's a formula to add in A1 for quick navigation to first blank cell (changed colored range with your desired range):
=HYPERLINK("#"&CELL("address";INDEX( A3:A2000 ;MATCH(9,99E+307; A3:A2000 )+1));"First blank")
Note:
My last cell with data in column A is A1349, I added 2000 as a margin but will increase it to 10000 when I get to 2000 with the data.
Source:
_______________________________________
|
|
| pus acum 3 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 787
|
|
Also possible with OFFSET.
My table starts from row 5 (header) and I want the hyperlink to select the first empty cell in column C.
=HYPERLINK("#C"& (OFFSET(A1;COUNTA(A:A);0)+9) ;"CAPAT TABEL COL. C")
|
|
| pus acum 3 ani |
|
Mrrrr
AdMiN
Inregistrat: acum 17 ani
Postari: 2228
|
|
You can also use:
=HYPERLINK("#" & ADDRESS( COUNTA(Table1[Date])+3; COLUMN()); "LAST ROW")
The +3 means the first row of my table falls on Excel row 3.
* * * *
If you don't have a table, you can do it similarly like this:
=HYPERLINK("#" & ADDRESS( COUNTA(A3:A10000)+3; COLUMN()); "LAST ROW")
_______________________________________
|
|
| pus acum 2 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 787
|
|
First Row: =HYPERLINK("#" & ADDRESS(ROW()+3; COLUMN()-2); "1st RW")
Last Row: =HYPERLINK("#" & ADDRESS( COUNTA(A:A)+3; COLUMN()-2); "last RW")
|
|
| pus acum 11 luni |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 787
|
|
In all the formulas above there is a static number added, which is the first few rows at the top of the data within a sheet.
But this can be variable. If decide to you insert rows on top, the formula will have to updated manually.
Assuming your table of data always has headers and they don't change their name on an usual basis, you can use that to find the row number for the header of the table.
My table has a column called "Data", and the following array formula (CTRL+SHIFT+ENTER) finds the number of the row:
=MATCH(TRUE;ISNUMBER(SEARCH("Data";A:A));0)
If you do not want to use an array formula, you can do INDEX-MATCH without helper column:
=MATCH(TRUE; INDEX(ISNUMBER(SEARCH("Data"; A:A)); 0); 0)
Or you can use a helper column:
Helper: =IF(ISNUMBER(SEARCH("Data"; A3)); ROW(); "")
Find first occurrence: =MIN(H:H)
Source: ChatGPT
|
|
| pus acum 6 luni |
|