Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Hyperlinking between sheets

    Hi all...I have a quick question on hyperlinks....I am trying hyperlink a rather large range of cells in one sheet to their corresponding cell in a second sheet. The number of cells is from row 13 to row 2872. I have attached a sample of what I am working on....I need C5-C25 on Sheet 1 to hyperlink to E5-E25 on Sheet 2.......C5-C25 have formula in each.....I am looking for some way to put hyperlinks in C5-C25 'all at once', rather than have to do each cell individually (b/c the actual workbook has about 2869 cells.........I have tried copy & paste special but that doesn't allow for it.....I tried just copying formula down but it doesn't pull any hyperlink with it.....I have tried using the =Hyperlink formula, but it won't work.....any ideas? Thanks.
    Attached Files Attached Files

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,205
    Thanks
    14
    Thanked 331 Times in 324 Posts
    Use the hyperlink function (it works for me):

    =HYPERLINK("[hyperlinking cells.xls]Sheet2!E"&ROW(Sheet2!E5),IF(Sheet2!E5>0,"","."))

    Note that if you don't display any text (as you do by giving a null) it will not hyperlink those cells. If that is what you want that is fine, otherwise use a " " instead of "" and the space will allow a link....

    Steve

  4. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Steve....thank you for this...I don't knwo what you mean when you say "If you don't display any text", but I will try it both ways to make sure it does what I require.....thank you

  5. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,205
    Thanks
    14
    Thanked 331 Times in 324 Posts
    The display part of your formula [IF(Sheet2!E5>0,"",".")] will display a period if the value in Sheet2 is >0 and will display a null string ("blank") when the value is <=0. When a null string is displayed (Sheet2 value <=0) there is no hyperlink in that cell. Only the cells that display a period (those which have sheet2>0) will hyperlink.

    If you want all the cells to hyperlink use:
    =IF(Sheet2!E5>0," ",".")

    Steve

  6. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Steve...thanks for the info on hyperlinking....I have a couple of questions:
    (1) will using the =HYPERLINK code make the file really big? I am trying to use the =Hyperlink formula so that I can hyperlink from, say,'Available time'L437 (room 5000 on Oct 26) to 'Rooms'E5750 or 'Rooms'J5750 and enter data without having to scroll to get there.

    (2) I am also trying to incorporate OFFSET into the hyperlinking code....the attached file will show you what I am doing....I have several 'rooms' and they have different numbers of 'time slots' for each morning and each afternoon. The example I posted the other day had equal numbers of slots for each day in each room, and so showing available time and hyperlinking was much easier. The sample I attach to this post works differently: in this sample, I can book something for Room 1000 on Jan 6 by entering booking info in row 27, and when I want to close off booking on the morning of Jan 6, (eg room 1000, with 4 slots), I will put an 'x' in J28. I can book in the afternoon of Jan 6 (enter data in row 29) and to close the PM, I also put an 'x' in J30. If I want to close the whole day, I put an 'x'x in J28 and J30.

    Jan 11 has 6 slots/day and so to close off the morning, I put an 'x' in J41; to shut off the day, I put 'x' in J41 and J44.

    Because each room has different numbers of slots (and even different numbers of slots in a week---eg: room 4000 has 15 slots on Mondays, and only 3 on other work days), I can't simply devise a formula to use on the "Available time" worksheet and then copy down for all rooms from row 13-14333. There is a lot of offsetting here but I cannot figure out the correct offsetting formula., I have been trying to use 'helper' rows (like you showed in a different post a few weeks/months back) but I can't get the rythym of the offset (if I can say it that way). And so, I need some help with the proper OFFSET on the "Available time" w/sheet (and helper column D) to be able to 'close' rooms on any given day (morning, afternoon. Thank you.
    Attached Files Attached Files

  7. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,205
    Thanks
    14
    Thanked 331 Times in 324 Posts
    1) I don't think using hyperlink will be any more large than other formulas, but I have not used it extensively.

    2) Not sure what you want to do. But if your offset formulas are all unique and can not be copied, I don't see the point. I would try and create a formula that you can copy, rather than creating uniquely for each row.

    I still have no idea of what you are trying to accomplish with this scheduler. Before you can start programming, you need to understant the step-by details of what will be done, what can go wrong, etc, so you can tell excel/VBA what to do in each of those cases. If you can't explain even the concept the me, I don't see how you can even hope to start being able to tell excel/vba the details.

    As I have alluded to before, you seem to focused on the details even though you don't seem to have an understanding of the basic mechanics of what you want. Start with the big picture and the concept. Break that down into different tasks, then sub-tasks. Then when you understand those sub tasks, you can start worrying about how to do the tasks and how the data should be setup to achieve that goal. Then start worrying about the details of programming those tasks. You still seem to be putting the cart before the horse...

    Steve

  8. #7
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Looking at the workbook that I attached, I was trying to do the following:

    Some of the 'rooms' have the same setup (eg: 4 slots per day) and others are different.(eg: 15 slots on Mondays and 4 slots on T,W,T,F). Within a 'room' the set up for that room from week to week is always the same (eg: Monday has 10 slots, T,W,T,F have 6 slots) and that goes for each week in that particular room......that will be the case for each week in a year for that room. Another room might have a different setup (eg: 4 slots/day for each day of the week) but the setup doesn't change from week to week.....I was hoping that I could at least come up with a formula for each room (that's only about 12 formulas) and set it up for, say, Jan 3-7, 2011 and simply copy it down for the rest of the year for that particular room........the formula would show me when a room has been closed in the morning or afternoon etc .I might need a formula for M-T-W-T-F but once I have that, it will work for all weeks in that room. So, I will take another attempt to devise formulas and come back here to report on my progress...thanks.

  9. #8
    4 Star Lounger
    Join Date
    Jan 2004
    Location
    Ulaan Baatar
    Posts
    518
    Thanks
    1
    Thanked 42 Times in 40 Posts
    Forgive me if this is not relevant to your particular situation, but meeting room booking systems is something I've repeatedly come across through a long career, much of it in IT / IS.

    Almost every organistaion feels its meeting room booking requirements are unique, and many have a go at writing their own application. Some succeed at first, only to find that a seemingly trivial change in the requirement leads to weeks of programming effort.

    Most conclude - earlier or later - that it is easier to adopt (and maybe adapt) one of the zillions of applications out there (some are free, many are inexpensive) than to write and maintain an in-house application.

    It is interesting to see how commercial developers have tackled this quite elaborate challenge, and most have concluded that the right starting point is either a Database (to correlate a lot of data that needs to be sliced in many ways eg by room, by day, by facility - AV, catering etc - and to manage multiple access issues) or Outlook (to exploit its native calendaring and notification functions).

    It is also often found that access to a booking system is as important as the system functions themselves and web-enabling is commonly sought at purchase-time or once a booking system is adopted.

    Excel isn't particularly suited to the particular functions reqiured, and the web is littered with requests on the theme of "I'm writing a meeting room booking system in Excel and I don't seem to tbe able to . . . ". Of course it CAN be done - the question is SHOULD it be ?

    I hope you read this in the constructive spirit in which it is written - what I am trying to say is that you have chosen a very difficult path down which to achieve a worthwhile result.

  10. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,205
    Thanks
    14
    Thanked 331 Times in 324 Posts
    I understand about the different number of slots, but fail to see the relevance to the question, but I suppose this is due to the fact I don't understand what you want the formula to be in each of the cells...

    Steve

  11. #10
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Thank you for that, MartinM,...I appreciate your interest and I accept what you say in the spirit intended...and of course, I appreciate Steve's patience.....I have learned a lot from Steve and others on this website and continue to learn. And, I don't want to monopolize time in these forums.....I think of the expression (you don't know what you don't know) and I think it applies here b/c as a relative newcomer, I have only scratched the surface of Excel---so, some of my questions are pretty general (and lacking in specifics)---I don't know the extent of Excel's capabilites and so I often don't even know what question to ask; so, I sort of throw stuff out there (without too much discipline) hoping that someone will understand what I am trying to do etc etc...I ahve to learn to be more specific etc....and, as you suggest, perhaps I need to also look at something like Access (OUTLOOK would be easier but it isn't used where the users are).....anyway, thank you to all of you for your help and pateince...I think thta I can take what Steve has previously posted re using OFFSET etc and make something that might fit the bill...and if I do, I will let you know. Thanks again, enjoy the weekend.

  12. #11
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    HI Steve.....I have been thinking about this some more and I think that I can take what I have previously learned from your posts re: hyperlinking and OFFSET and come up with a set of formulas for each of the 12 rooms that will allow me to track when a room is booked, as well as hyperlink from one sheet to another....I am going to work at it and will let you know how it turns out....thanks, as always....bfn

  13. #12
    4 Star Lounger
    Join Date
    Jan 2004
    Location
    Ulaan Baatar
    Posts
    518
    Thanks
    1
    Thanked 42 Times in 40 Posts
    Thankf for the reply, and keep experimenting - that's really how everyone learns.

    And you'll find that your VBA skills transplant quite well to Access if you decide to experiment there too.

  14. #13
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Good morning...I am still working at this, now trying to combine HYPERLINK and OFFSET....I attach a new sample WBook. Here is where I am so far...

    In my sample Wbook there are 3 rooms; room 1000 has 4 slots on M,W,T,F and 6 slots on TUE...room 2000 (and room 3000) have 4 slots per day (and in the actual WB, there are about 11 rooms, with different slots/day). Anyway, I want to be able to go to the Rooms worksheet and enter an 'x' in column J to close off booking into that room (eg: an 'x' in RoomsJ14 closes off the afternoon of Jan 3; an 'x' in RoomsJ41 closes off the morning of Jan 11....an 'x' in RoomsJ41 and RoomsJ44 will close off the whole day of Jan 11)....that part of ths formulas is working fine, and because there are hundreds of rows, I am using OFFSET to accomplish that.

    However, I want to combine it with HYPERLINK so that, for example, if I see time available in room 2000 on the morning of Oct 24 ('Available time'K433), I can click 'Available time,K433' and jump to 'Rooms,J2010' (rather than have to manually return to 'Rooms" and scroll down to J2010 (again, because there are 11 rooms & thousands of rows in the actual Wbook). I am using a combination of Hyperlink and Offset on the 'Available time' worksheet, using helper columns (D and E).....the time consuming part of this is to manually do the formulas for Jan 3-7 and also Jan 10-14 (using OFFSET)but that is only 20 cells for 11 rooms. Then, I will copy the block of Jan 10-14 down to the bottom. The problem is the first part of the OFFSET that is nested in the HYPERLINK formula (starting at row 23). Helper column D (22)is the number of rows from Mon AM (or PM) to the following Mon AM (or PM) in room 1000 and helper column E (20) is the number of rows from Mon AM (or PM) to the following Mon AM (or PM)...the reference to the helper column in the first part of the formula always remains at D23 for room 1000 (or E23 for room 2000), but in the 2nd part of the OFFSET, it changes with the row. If I go to, say, row 100 and manually change D23 to D100 (in the first part of the formula), the hyperlink works perfectly...if I leave it at D23, then it will hyperlink, but to the wrong cell. If I can re-write the formula so that D23 (for room 1000) or E23 (for room 2000) in the 1st part of the OFFSET formula will change (like they do in the 2nd part of the formula) then it will do the job properly ... any suggestions as to how to re-write the 1st part of the HYPERLINK formula? Thank you.
    Attached Files Attached Files
    Last edited by dmcnab; 2011-08-14 at 13:55. Reason: Have now attached correct file...sorry.

  15. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,205
    Thanks
    14
    Thanked 331 Times in 324 Posts
    Instead of a formula like:
    =HYPERLINK("[newtest.xls]offset(rooms!$J$14,D23,0)",IF(OFFSET(Rooms!$J$14,D 23,0)>0," ","."))

    where the hyperlink contains variable text that must be edited, use something like:
    =HYPERLINK("[newtest.xls]rooms!$J$"&14+D23,IF(OFFSET(Rooms!$J$14,D23,0)>0," ","."))

    which can be copied...

    Steve

  16. #15
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Just on a side note, what version of GroupWise are you using? Can you create accounts?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •