offset HYPERLINK

David J. Braden

unread,

Jan 25, 2000, 3:00:00 AM1/25/00

to

Peter,
This worked fine on my Mac:

=HYPERLINK("[Macintosh HD:Desktop Folder:LinTerp]Sheet1!D3","For Peter")

Dave Braden

~P~ wrote:
>
> =IF(BV24="","",HYPERLINK('Price List'!$I23))
>
> Im stuck on this one how do we offset this so instead of it going 'Price
> List'!$I23 here it looks at the offset number and goes to price list.
>
> Regards
> Peter.Young
> ~P~

David J. Braden

unread,

Jan 25, 2000, 3:00:00 AM1/25/00

to

Peter,
the first argument to HYPERLINK is a *string*. Now I'm confused. What are you
trying to accomplish? It's not clear to me theat HYPERLINK is the appropriate mechanism.

Dave Braden

~P~ wrote:
>
> hi dave
> you've baffled me
> perhaps you could help a little more?
>
> the cell to look up is bv21
> the worksheet with the formula is sales quote
> and it needs to offset to worksheet 'Price List'!$I21
>
> this is a formula next to it that works fine
>
> =IF(AND(A$1=1,BV23>""),OFFSET('Price
> List'!G$21,BV$21+BU23-1,0),IF(AND(A$1=2,BV23>""),OFFSET('Price
> List'!H$21,BV$21+BU23-1,0),""))
>
> if i leave out the hyper link part alls ok with this one as well but as soon
> as i try to put it in errors

>
> =IF(BV24="","",HYPERLINK('Price List'!$I23))
>

> im sure that your version works fine i just can not get my head around it as
> it is
> so if you could edit your demo to suit i would be grateful

>
> =HYPERLINK("[Macintosh HD:Desktop Folder:LinTerp]Sheet1!D3","For Peter")
>

> Regards
> Peter.Young
> ~P~

Tom Ogilvy

unread,

Jan 25, 2000, 3:00:00 AM1/25/00

to

Peter,
Are you trying to create a hyperlink that, if clicked goes to the cell that
your offset formula refers to?

Regards,
Tom Ogilvy

~P~ <Peter...@sa.mondial.net.au> wrote in message
news:MQjj4.10447$3b6....@ozemail.com.au...

~P~

unread,

Jan 26, 2000, 3:00:00 AM1/26/00

to

~P~

unread,

Jan 26, 2000, 3:00:00 AM1/26/00

to

Tom / David

sorry for any confusion
the following s on the quote page and returns a link that is on the
price list page.
example http://www.microsoft.com/Office/office/000/viewers.htm#excel
this is on the price list page in cell !$I23

=IF(BV24="","",HYPERLINK('Price List'!$I23))

and the formula above is on quote page bx24 and you can activate it from the
quote page
(if bv <> "")

to help a little further the cell next to it was written as

=IF(BV24="","",IF($A$1=1,'Price List'!$G23,'Price List'!$H23))

which has been re written as

=IF(AND(A$1=1,BV23>""),OFFSET('Price
>> List'!G$21,BV$21+BU23-1,0),IF(AND(A$1=2,BV23>""),OFFSET('Price
>> List'!H$21,BV$21+BU23-1,0),""))

the reason for this is the user had 500 odd rows and depending on his
selection from a
data validation (list) drop down box would only activate around 10 to 15 of
these 500 rows
thus when using the drop down in data validation found large spaces in the
list and was finding his info in the middle or at the end.

so by setting it with offset i have managed to bring the list back to 27
rows
(a few spare incase) making the drop down list more effective.

but i can not seem to get the same to happen with the hyperlink option.

ps i can get it so it takes me to the correct link in the price list by
typing into
the insert hyperlink box the formula (as above for the other cell) but i
should be able to press it in
the quote page and go straight from there.

Regards
Peter.Young
~P~

~P~

unread,

Jan 28, 2000, 3:00:00 AM1/28/00

to

all fixed

ended up using a combination of
match, HYPERLINK ,and vlookup

David & tom
thank you for your time and interest

Regards
Peter.Young
~P~

Tom Ogilvy

unread,

Jan 30, 2000, 3:00:00 AM1/30/00

to

Peter,
Glad you found a solution. I couldn't get a handle on your description, so
I really couldn't offer a suggestion.
Regards,
Tom Ogilvy