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:
surender_lady Profile
 | Femeie
25 ani
Arad
cauta Barbat
26 - 47 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 787
|
|
Link to Another Excel File - with the file open
I adapted this formula: =HYPERLINK("#"&"'" & B5 & "'!" & C5,D5)
into this formula: =HYPERLINK("#"&"'" & H3 & "'!" & I3;I3)
where:
in H3 I have the file and the sheet, in this form: [FISE_APTITUDINE.xlsm]Sheet1
in I3 I have the cell reference: $G$578
However, if I obtained the cell address using this formula:
=CELL("address";INDEX([FISE_APTITUDINE.xlsm]Sheet1!$G$4:$G$984;MATCH(A432;[FISE_APTITUDINE.xlsm]Sheet1!$G$4:$G$984;0);1))
The result would be [FISE_APTITUDINE.xlsm]Sheet1!$G$578
So I could simplify this formula: =HYPERLINK("#"&"'" & H3 & "'!" & I3;I3)
into this formula: =HYPERLINK("#"&K3;I3)
in K3 I have the full name [FISE_APTITUDINE.xlsm]Sheet1!$G$578
Source:
2 more options from Source:
Link Within the Same File
You can use a HYPERLINK formula to create a link to goes to a cell or range somewhere within the current workbook. The trick is to add a pound sign (#) at the start of the address.
Here are three examples of HYPERLINK formulas within the same workbook:
Type the Address: Start with a #, then type the sheet name and cell reference. Optional - Type a friendly name, such as "Budget" to appear in the cell:
=HYPERLINK("#Sheet2!B2","Budget")
Use Cell References: Use the & operator to create a link location. Here, the sheet name is in cell B5, the cell address is in C5, and the friendly name is in cell D5.
=HYPERLINK("#"&"'" & B5 & "'!" & C5,D5)
Type a Range Name: For a link to a workbook-level named range in the same workbook, just use the range name as the link location.
=HYPERLINK("#StartCell","StartCell")
Link to Another Excel File
To create a link to another Excel fiale, in the same folder, just use the file name as the link_location argument for the HYPERLINK function.
=HYPERLINK("MyFileC.xlsx","FileC")
For files that are up a level or more in the hierarchy, use two periods and a backslash for each level.
In this example, the file is 1 level up.
=HYPERLINK("..\MyFileB.xlsx","FileB")
In this example, the file is 2 levels up.
=HYPERLINK("..\..\MyFileA.xlsx","FileA")
Modificat de TRaP (acum 5 ani)
|
|
| pus acum 5 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 787
|
|
I created one big formula for the hyperlink to cell addresses in another file, in order to simplify the process of obtaining the cell address and the hyperlink to it (without the need of other columns for partial results):
=HYPERLINK("#"&CELL("address";INDEX('[ZN 02.05.2019.XLSX]Sheet1'!$A$2:$A$72365;MATCH(N4;'[ZN 02.05.2019.XLSX]Sheet1'!$A$2:$A$72365;0);1));RIGHT(CELL("address";INDEX('[ZN 02.05.2019.XLSX]Sheet1'!$A$2:$A$72365;MATCH(N4;'[ZN 02.05.2019.XLSX]Sheet1'!$A$2:$A$72365;0);1));LEN(CELL("address";INDEX('[ZN 02.05.2019.XLSX]Sheet1'!$A$2:$A$72365;MATCH(N4;'[ZN 02.05.2019.XLSX]Sheet1'!$A$2:$A$72365;0);1)))-29))
"#" is required for the hyperlink to work
CELL("address";INDEX('[ZN 02.05.2019.XLSX]Sheet1'!$A$2:$A$72365;MATCH(N4;'[ZN 02.05.2019.XLSX]Sheet1'!$A$2:$A$72365;0);1)) is the formula that returns the entire address of the cell, meaning that it returns: '[ZN 02.05.2019.XLSX]Sheet1'!$A$50247
RIGHT(CELL("address";INDEX('[ZN 02.05.2019.XLSX]Sheet1'!$A$2:$A$72365;MATCH(N4;'[ZN 02.05.2019.XLSX]Sheet1'!$A$2:$A$72365;0);1));LEN(CELL("address";INDEX('[ZN 02.05.2019.XLSX]Sheet1'!$A$2:$A$72365;MATCH(N4;'[ZN 02.05.2019.XLSX]Sheet1'!$A$2:$A$72365;0);1)))-29)) - this part shortens the result above ('[ZN 02.05.2019.XLSX]Sheet1'!$A$50247) and displays only the cell address $A$50247;
29 in the end of the formula represents the number of characters of '[ZN 02.05.2019.XLSX]Sheet1'! that will be removed from '[ZN 02.05.2019.XLSX]Sheet1'!$A$50247 to obtain $A$50247
Modificat de TRaP (acum 5 ani)
|
|
| pus acum 5 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 17 ani
Postari: 2228
|
|
Use the following formula to create a link to a cell you desire:
=HYPERLINK("#"&CELL("address";$A$3);"go to cell A3")
_______________________________________
|
|
| pus acum 5 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 787
|
|
TRaP a scris:
I created one big formula for the hyperlink to cell addresses in another file, in order to simplify the process of obtaining the cell address and the hyperlink to it (without the need of other columns for partial results): |
I used the code below in the same sheet where I wanted to make a cell hyperlink in E2 if the number I write manually in cell D2 is found in column E of a table starting in cell E9.
If the INDEX-MATCH is done for a table, it takes considerably less characters in the formula, even if it would be in a different sheet of the same excel file.
=HYPERLINK("#"&CELL("address";INDEX(Table2[4];MATCH(E1;Table2[4];0);1));RIGHT(CELL("address";INDEX(Table2[4];MATCH(E1;Table2[4];0);1));LEN(CELL("address";INDEX(Table2[4];MATCH(E1;Table2[4];0);1)))+9))
Note that the formula in the end has +9, obviously because the table starts in cell 9, below E1 where I entered my formula. In the previous (quoted) example, the formula was placed below, hence the -29 in the end of that other formula. It all depends on the relative position of the cell with the formula and the cell when INDEX-MATCH is done.
|
|
| pus acum 5 ani |
|
Mrrrr
AdMiN
Inregistrat: acum 17 ani
Postari: 2228
|
|
Assuming the range for which you want cell addresses is a row instead of a column (cell addresses are coming from a horizontal range, you must change line 1 below with line 2:
Line 1 - cells for which to show addresses are in a column (different rows)
=HYPERLINK("#"&CELL("address";INDEX($G$7:$R$7;MATCH(F3;$G$7:$R$7;0);1));RIGHT(CELL("address";INDEX($G$7:$R$7;MATCH(F3;$G$7:$R$7;0);1));LEN(CELL("address";INDEX($G$7:$R$7;MATCH(F3;$G$7:$R$7;0);1)))+1))
Line 2 - cells for which to show addresses are in a row (different columns)
=HYPERLINK("#"&CELL("address";INDEX($G$7:$R$7;1;MATCH(F4;$G$7:$R$7;0)));RIGHT(CELL("address";INDEX($G$7:$R$7;1;MATCH(F4;$G$7:$R$7;0)));LEN(CELL("address";INDEX($G$7:$R$7;1;MATCH(F4;$G$7:$R$7;0))))+1))
The only thing you have to do is reverse MATCH(F4;$G$7:$R$7;0) and 1 in each INDEX formula.
_______________________________________
|
|
| pus acum 5 ani |
|
Mrrrr
AdMiN
Inregistrat: acum 17 ani
Postari: 2228
|
|
To get only column letter without hyperlink and without $:
=MID(CELL("address";INDEX($G$7:$R$7;1;MATCH(F3;$G$7:$R$7;0)));2;1)
_______________________________________
|
|
| pus acum 5 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 787
|
|
Moved from another topic
By default, if you have hyperlinks to cells (made in the classic add hyperlink kind of way) in Sheet1, and want to make a copy of Sheet1 and edit in there, the hyperlinks will be linked to the cells in Sheet1.
Also if you change the sheet's name the hyperlink will point nowhere and give an error.
And it's tiring to have to recreate the hyperlinks again.
Fortunately, there's a solution thanks to:
I adapted the formula a bit and came up with this (note: my sheets have spaces in their names):
=HYPERLINK(SUBSTITUTE(MID(CELL("filename";$A$1);FIND("[";CELL("filename";$A$1));99);"]";"]'")&"'!"&CELL("address";$A$1);"<---- TOTAL")
If you put the formula above in any cell, it will display <---- TOTAL and link to cell A1.
Now if you copy the sheet, it will still link to cell A1 but in the new sheet.
Also, I made cell A1 absolute ($A$1) so you can copy the formula from once cell to another. Leaving them normal (A1), when pasting in a new cell A1 would be replaced with #REF!
The only disadvantage I could find is that you cannot hyperlink to a range (eg. A1:K1), which you can do in normal Hyperlink insert.
|
|
| pus acum 11 luni |
|
