Error in formula - "Formula typed contains error"

Something like

=HYPERLINK(INDEX('Appendices II'!C:C,MATCH(3,'Appendices II'!B:B,0)+1),"bla bla bla")

Should work

The first argument of OFFSET must be a valid range, "#'Appendices II'!B" & MATCH( returns a text string so would need to be qualified as a range using INDIRECT.

Hi all, thanks for the suggestions. I can't get any to work correctly, and all but Jason.b75 display #ref in cell. Jason.b75's suggestion links to a single offset cell from the first but doesn't select the whole range from one cell to another. If I link to the top row of image, there is a change bottom of image will be off screen and previous image front and center. Likewise, if I select bottom row of image, there's a change top of image will be off screen and next image front and center. This all depends on whether previously selected cell on that sheet was above or below hyperlink reference. This is the reason I want to select a range of cells to ensure whole image being linked to appears in center of screen.

I may have been misleading with my example, as I really want to be hyper-linking to referenced cell (match(3,'Appendices II'!B:B,0)), but also selecting 10 or 15 cells below. So that part would be more like OFFSET(*the cell*,0,0,15,0) I think.

I will prepare and upload a sample sheet now, as suggested by The Excelphile.

Thanks,

Matthew

Thanks martindwilson for the suggestion. The +15 works great for offsetting to a lower cell, but I would sill have an issue as only one cell is being selected (not the whole range from B45 to B69). This means if linking to item 3 or lower, and then later linking to item 2, the offset cell (B69) will appear at top of screen, and the top of the image and text box for item 2 are cut off (or off screen).

It's a great tip though that I wasn't aware of so will probably use in another situation

Matthew

Originally Posted by martindwilson

to properly jump you may need some code

I don't think 2010 starter allows for the use of code.

This is a little better than my earlier failed attempt.

=HYPERLINK("#'Appendices II'!B"&MATCH(1,'Appendices II'!B:B,0)&":B"&MATCH(1,'Appendices II'!B:B,0)+34,"bla bla bla")

I guess the solution would need to be either:

1. Select a range from cell? to cell?+15
or 2. Select cell? and somehow make that the top row visible on screen.

If code is required I can work on a different machine with full (non-starter) version of excel. Full version is on work laptop but I usually work on home desktop which only has starter. Of course if required I'll use the laptop.

I think for now even linking to a single cell in the middle of the image would be OK as a compromised solution, but would be great to find that perfect solution.

Regarding adding code to excel, I have used VB in the past but never with excel, and would be completely lost with setting this up. If very complicated that route would unfortunately have to go on the back burner for now, as I couldn't justify spending too long trying to fix this half issue. Maybe in my spare time I'll get a chance to look into excel coding.

Jason.b75. - I just tested your formula and works nicely just can't figure out why it's always selecting to cell?:B34 instead of cell?:cell?+34.

Also I'm wondering now if selecting a range will actually solve this problem, as hyperlink may not necessarily try to fit entire selection on screen (may just select first cell from range and then allow the rest to go off screen).

I wonder if it's possible to hyperlink to the actual image or text box.

Thanks again for your time and help,

Matthew

martindwilson, I really like this solution:

=HYPERLINK("#mypic1","Each time I open the app (debug) app on device, I receive DEBUG: ERROR (see sheet: Appendices II, Item 1).This occurs after splash screen while updates are loading.")

This also seems to be adaptable i.e. if I edit an appendices item and need to insert rows, the named range updates correctly

Seems, the simple solutions are always best! I overlooked this because I initially wanted to select the entire row (up to column N) when linking back to bug on WP7 V2 sheet using a named range. This did not work correctly while using filters/sorting, but as I do not filter/sort Appendices II it's great for these links.

Thanks so much :D

Also jason.b75 - just as a point of interest, I now realize your formula does not select cell?:B34, it selects cell?:B(x+34) where x is the value inside cell? (i.e. 1,2,3 etc...).

You are all clever people! Thanks again,

Matthew

Thanks. Great idea to space them out