The value to look for is on a sheet named Ark1 and in a matrix b4:m1002
and
in the fourth column
In Norway we use ";" between parameters and not "," and that fools me
everytime.
Translation between these two version is found here:
http://www.piuha.fi/excel-function-name-translation/index.php?page=english-norwegian.html
But it didn't work.
When clicking or doble clicking on the cell it took me to the cell were
what
to look for is stored, namely C23, and not to the other sheet named Ark1
where the look up value is.
The returned value in the lookup cell is a number, not a text.
Why the "+3" in the formula...?
--
Kind regards
Øyvind Granberg
(e-mail address removed)
www.tresfjording.com
"RagDyer" <
[email protected]> skrev i nyhetsmeldingen:
(e-mail address removed) ...
For starters, let's hilite the differences in our versions of XL
pertaining to the formula I'm suggesting.
I don't have XL07, but I don't think there's a function like the one you
used in your formula example ... V.LOOKUP ... with a dot in the name - I
assume that's a typo.
The Hyperlink() function needs the full WB name, including the file
extension.
My version uses .xls - Yours is most probably .xlsx - so when replacing
the WB name that I used with your actual WB name, make sure you use the
proper file extension.
Also, your separators are semi-colons where mine are commas.
I created a WB named Sales, and this formula works perfectly in my XL2k,
USA version:
=IF(C18="","-",HYPERLINK("[Sales.xls]Sheet2!"&
ADDRESS(MATCH("*"&C18&"*",Sheet2!$C$4:$C$1002,0)+3,4),
INDIRECT("Sheet2!"&ADDRESS(MATCH("*"&C18&"*",
Sheet2!$C$4:$C$1002,0)+3,4))))
To select a cell that contains a hyperlink without performing an actual
jump, either click in an adjacent cell and arrow into the hyperlink
cell,
OR, click and hold the mouse button until the cursor changes from a
pointed finger to a cross.
Replace the separators - commas with semi-colons -
Replace the "Sales.xls" WB name with your WB name ... don't forget the
proper file extension -
The Hyperlink() function's first argument requires a text parameter, so
the Address() function works fine here on it's own, since it's return is
Text.
The second argument however determines what is displayed in the cell, so
there the Address() function must be wrapped with the Indirect()
function
in order to make it an actual, workable, cell address.
The first argument of the Address() function is the row number.
The Match() function returns a *relative* location in the "lookup
array",
and since you're starting your datalist at Row 4, the "+3" is to size
the
return to match the *actual* Worksheet row number, enabling the
Address()
function to accurately identify the cell's row location.
The second argument is the column number.
Since you know that the data you're looking for is in Column D and no
calculation is necessary, simply entering a "4" is all that's needed.
You should be able to revise this formula to meet your scenario.
Post back with any questions.
--
Regards,
RD
------------------------------------------------------------------------ ---
Please keep all correspondence within the NewsGroup, so all may benefit
!
------------------------------------------------------------------------ ---
=IF(C18="";"-";V.LOOKUP("*"&C18&"*";'Sheet2'!$C$4:$I$1002;2;FALSE))
--
Zadig Galbaras
(nick)
www.tresfjording.com
"Ragdyer" <
[email protected]> skrev i nyhetsmeldingen:
(e-mail address removed) ...
I don't know why you think that hyperlinks wouldn't work.
I'm talking about the Hyperlink() function!
I can write you a formula that will return a value to a cell, exactly
as
a
Vlookup formula, and when you click on the cell, it can jump to the
original
data cell ... just as you requested.
Then, when (if) you change the lookup value, clicking in that same
cell
will
jump you to that new data cell location.
Just give me an idea of your formula or datalist structure and I'll
give
you
a formula that you should be able to revise yourself to fit your own
specific parameters.
You don't have to reveal any proprietary information.
--
Regards,
RD
---------------------------------------------------------------------- -----
Please keep all correspondence within the NewsGroup, so all may
benefit
!
---------------------------------------------------------------------- -----
sorry, no can do.. secret service.. hehe..
Actually hyperlinks are a no good solutin because cell content
changes
and
there are hundreds of them.
I just want to follow up a v.lookup to the cell where the cell
content
originates.
Sorry about the mess with the link in earlier messages, but my early
rectifying message got stuck in the outbox without my knowledge....
--
Zadig Galbaras
(nick)
www.tresfjording.com
"RagDyeR" <
[email protected]> skrev i nyhetsmeldingen:
(e-mail address removed) ...
You started off mentioning links.
A Vlookup() function does not create an *actual* link.
It sounds to me that you might want to use hyperlinks.
Post an example of your formula and let's see if we can make
something
that
might be to your liking.
--
Regards,
RD
--------------------------------------------------------------------- -----
---------------------
Please keep all correspondence within the Group, so all may benefit
!
--------------------------------------------------------------------- -----
---------------------
Have you tried this with the v.lookup function?
--
Kind regards
Øyvind Granberg
(e-mail address removed)
www.tresfjording.com
"RagDyer" <
[email protected]> skrev i nyhetsmeldingen:
(e-mail address removed) ...
Hitting <Enter> too fast.
Meant to say:
There's no returning to A10 on Sheet1 without first going to:
<Tools> <Auditing> <Trace Dependents>,
and double clicking the dotted line between the cell and the sheet
icon.
--
Regards,
RD
-------------------------------------------------------------------- -----
--
Please keep all correspondence within the NewsGroup, so all may
benefit
!
-------------------------------------------------------------------- -----
--
TYPO = sorry!
Need to hit:
<CTRL> < [ >
in *both* cases.
Once you've selected the A1 to A5 range, hitting:
<CTRL> < ] >
will jump you to the formula in A10.
Then, alternating <CTRL> with " [ " and " ] " will jump you back
and
forth between the formula and the range.
There's no return to A10 on Sheet1.
--
Regards,
RD
------------------------------------------------------------------- -----
---
Please keep all correspondence within the NewsGroup, so all may
benefit
!
------------------------------------------------------------------- -----
---
This works in XL02:
Formula in D10 on Sheet2:
=Sum(D1
5)
On Sheet1 in A10 is this formula:
=Sheet2!D10
With A10 on Sheet1 selected, hit:
<Ctrl> < [ >
This jumps the focus (selection) to D10 on Sheet2.
With D10 now being selected, hit:
<Ctrl> < ] >
And the focus jumps to the range D1 to D5.
--
HTH,
RD
------------------------------------------------------------------ -----
----
Please keep all correspondence within the NewsGroup, so all may
benefit
!
------------------------------------------------------------------ -----
----
Sorry about not revealing my Office vesion. It's 2007.
I am not looking for a way to see where the values in a cell is
coming
from, I want to GO TO the cell the values are coming from.
In a jiffy...!
I consider the F1 Help function i all MS Office applications as
a
waste
of time. Google is much better. NG's are too....
--
Kind regards
Xyvind Granberg
(e-mail address removed)
www.tresfjording.com
"AltaEgo" <Somewhere@NotHere> skrev i nyhetsmeldingen:
uyFY#
[email protected] ...
Greetings!
XL 2003:
Tools, Formula Auditing, Show Formula Auditing Toolbar.
XL 2007 try Help "Auditing"
Cellmatrix has a nifty add-in:
http://www.cellmatrix.net/index.php/downloads/formula_trace_tool1/
The website states the above add-in is untested in XL2007.
--
Steve
Hi...
I have this workbook with a few spreadsheets all containing
data
to
be collected in sheet1
I have set up the links. Hundreds of them.
This is links in the current document, not links to external
documents or sites.
Now...
How do I, in a cell in sheet1, go to the cell the link in
this
cell
is pointing to in sheet2?
--
Kind regards
Xyvind Granberg
(e-mail address removed)
www.tresfjording.com
