Hyperlink not working - probably an ID10T issue

    I needed to select all cells on Sheet1 between C5 and BE44, so I created a 'C5:BE44' hyperlink.

    But when I added a column of data to D4, the hyperlink became invalid unless altered to read 'C5:BE45'.

    BE44 is actually empty. Could I put a name in that cell, like WOW, and have the hyperlink go there regardless of its location in the page?

    I tried 'C5:WOW' but it says 'invalid reference'.

    Thanks for reading :)

    Please provide some more information.

    1. What method did you originally use to create the hyperlink? A "HYPERLINK" formula in a cell? The Link button on the Insert ribbon? VBA? Something else?
    2. Please explain "I added a column of data to D4." Do you really mean that you added a column of data to a single cell?


    Grits ain't groceries

    Eggs ain't poultry

    And the truth value of certainty is null.

    mdenino: Sorry, didn't see your answer until now.


    My data currently occupies columns C to BE and 40 rows from 5 to 44.

    I periodically add new data* to column F (not D, as I'd said earlier), so next time I do this, the last column at far right that contains data will become BF. After another data input to column F the last column will become BG. And so on.

    So the hyperlink would need to be constantly modified to read 'C5:BF', then 'C5:BG', etc, etc.


    I created the hyperlink in empty cell C51, by right-clicking it and selecting 'Hyperlink', then following the usual instructions.

    Now, clicking the link selects all the data from C5 to BE44 and I can change the data order as I wish with the A-Z modifiers in columns D and E.


    *By 'adding a column of data', I meant that I will add data to between 5-8 cells in column F, leaving the other cells empty.

    Quote


    *By 'adding a column of data', I meant that I will add data to between 5-8 cells in column F, leaving the other cells empty.


    It sounds like you are selecting column F and inserting a new column, which shifts column F and beyond to the right. If so, I believe what you'd like is easily accomplished with a named range.


    1. Select the range that contains your data (Originally C5:BE44)
    2. In the name box above the column headers, type the name you'd like to give to the "flexible" range.
      Alternately, name it using the Name Manager, which can be accessed via Formulas > Define Name or Formulas > Name Manager > New
      (Via keyboard: Alt-M-M-D or Alt-M-N-N ... or Alt-I-N-D-N, which hearkens back to pre-ribbon days!)
    3. Construct (or edit) your hyperlink in cell C51 as before. You will notice that your newly named range will be shown as an option.

    Now, anytime you insert a new column anywhere from column D to BE, your named range will grow by one column. (If you insert a column before C, your named range will instead shift to the right.) The same holds true for inserting a partial column, as long as it includes at a minimum rows 5 thru 44.


    Hope I have understood your need correctly, and this solution works for you. Let us know.


    Grits ain't groceries

    Eggs ain't poultry

    And the truth value of certainty is null.

    Love 1

    mdenino: My apologies - again. I missed your last post - again :rolleyes:

    I haven't tried your suggestion yet but I think you've nailed it.

    I won't be back at my computer for a few days (covid19 grr) but as soon as I do I'll try it and let you know what happened.

    Many thanks! :thumbup:

    mdenino: I tried your suggestion but couldn't make it work. I probably misunderstood what you meant.

    So I've constructed two small example files to illustrate my problem.

    NumbersHyperfile.xlsx shows a worksheet that's up-to-date.

    Clicking the 'Select' link (D27) selects all the data from C1 through AC24.

    In NumbersHyperfile-2.xlsx I now want to add new data, so I've inserted a column, E.

    But the 'Select' link no longer works- it doesn't include column AD.

    What am I doing wrong?

    PS: Columns C and D are 'operators', used to order (via A-Z or Z-A) all the other columns after 'Select' is activated.

    I should have left them out, ignore or delete them if you like. They don't affect the matter at hand.


    Ooops: Attached the files twice :(

    I think you empty cells are confusing the Dynamic Named Range.


    I have used the HYPERLINK Function and converted the data to a Table. A Table requires a Header Row, but I have hidden this so your data looks the same. Next I renamed the Table to Data.You can add Columns & Rows and the hyperlink will work dynamically


    The HYPERLINK Function is =HYPERLINK("#"&"Sheet1!Data","Select")


    If you aren't sure of working with Tables then post back.


    This might work with a Dynamic Named Range, but you need the hash as above.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login