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:
Profil AlinaMicu
 | Femeie
25 ani
Brasov
cauta Barbat
25 - 39 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 17 ani
Postari: 2186
|
|
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 2 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 739
|
|
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 2 ani |
|
Mrrrr
AdMiN
Inregistrat: acum 17 ani
Postari: 2186
|
|
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 1 an |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 739
|
|
First Row: =HYPERLINK("#" & ADDRESS(ROW()+3; COLUMN()-2); "1st RW")
Last Row: =HYPERLINK("#" & ADDRESS( COUNTA(A:A)+3; COLUMN()-2); "last RW")
|
|
| pus acum 3 luni |
|