• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Go to a designated cell in a specific worksheet

M

MalR

Member
Hi Gurus. I have an Excel file which contains a number of worksheets. One worksheet has a database which goes across the page and I add to it regularly with new customer information. It is large and contains a series of repeated blank template tables. It is in a specific worksheet named "database". Currently when I add new customer information I go to the worksheet called database and I put XXX at the top of the next blank entry. I use Ctrl+F to find the XXX. Each time I enter customers I shift the XXX cell along to the next blank section of template ready for the next entry. It would be speeded up if I had a macro that I could click on to do this.
So, what I want to do please is have a macro that would go the worksheet "database" and find the cell "XXX" in that worksheet.
Thanks guys
MalR
 
It might take a long time to scan a large workbook, Somendra.

MaIR - are you adding new informaiton at the bottom of the database each time? And is your database by any chance an Excel Table? By that, I mean the kind of table where if you select a cell in it, and the 'Table Tools' contextual tab appears in the ribbon? Or could it be turned into a Table? (I use the Ctrl + T keyboard shortcut for that)

IF so, then it will be very easy to code up a macro that adds a new row at the bottom of the table, and at the same time makes that cell active.
 
Why use a macro to go to a particular place in spreadsheet? I think using a hyperlink is more appropriate.

Something along the following (which needs to be entered using ctrl+shift+enter) might work for you.

=HYPERLINK(MID(CELL("filename"),SEARCH("[",CELL("filename")),SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))+1)&"Sheet2!B"&TEXT(MATCH(TRUE,Sheet2!B:B=C1,0),"General"),"Customer "&C1)

This formula will look in sheet 2 column B for a match to what is in cell c1 in the sheet it is in and then generate a hyperlink to that cell. i.e. you click on the cell this formula is in and it will jump to the cell in sheet 2 column b which matches the value in c1.
 
Why use a macro to go to a particular place in spreadsheet?
Click to expand...
In some circumstances, a macro would be easier. For instance, maybe you want to instantly jump to the data sheet from anywhere in the workbook. Assigning a keyboard shortcut to a macro would make more sense in that context than making users click on a hyperlink.

In other situations, a hyperlink would be more appropriate.

Jake, any chance you can upload a sample file - I'd like to see how your formula works.
 
Jeff

I agree that a macro may at times be more appropriate given the flexibility aspects you mention. I was merely trying to give an additional possibility that might work in the present occasion.

Attached is example file using the hyperlink formula.
 

Attachments

  • chandoo example.xls
    24.5 KB · Views: 8
That's a nifty example.

I guess another way to do this would be to assign a named range to the cell you want to jump to - Destination - and just manually insert a hyperlink from the ribbon, that points at that Named Range. Then when you shift the cell with the named range, the hyperlink will point to the new address on account of that named range.

See attached.

Not that there's anything wrong with the previous example...it's a great solution.
 

Attachments

  • Hyperlink example.xlsx
    79 KB · Views: 6
Guys thanks for your quick replies. Much appreciated.
Jake I think your idea has merit but I need to explain my situation so you can advise more on it. This should also answer Jeffrey's questions.
We are a tourist park. Our booking program is an Excel program. Our major worksheet is essentially a calendar which lists names and booking details in all our cabins in a calendar format. We call it the booking worksheet. We work mostly in that worksheet.
Our database worksheet contains repeated templates (each 7 cols by 33 rows). It contains the customer's details (name, address, email etc) and it calculates the pricing for their stay. Sections of it get exported to MYOB and it also links to our email program.
Each new customer gets their information filled out in an identical template table. When we get a new booking I need to find the last entry in this database worksheet and add the new customer's details to the next blank table. It all works well but we are always looking at speeding up the booking process.
We use a macro to Go to Todays Date. Because it has an icon in the Developer tab we can quickly go to todays date from anywhere in the booking worksheet. In the booking worksheet we also use a hyperlink to take us to the database worksheet for each individual customer.
Jake I am not sure how I could use a hyperlink to take me to the next vacant table in the database because I could be anywhere in the worksheet. My initial thinking was as Somendra suggested and use a macro. It would be in the Developer tab and would therefore be accessible from anywhere in the worksheet.
Problem is I am hopeless at building macros. I usually copy them and install them abut I cannot find one that will do what I want.
In summary for most of our working time we work in the booking worksheet and as it is a calendar the curser can be anywhere in that workbook. I can now see that I could use a hyperlink by placing it in an active cell near 'Today". I could use the Go to Today macro to get me to "Today" then click on the hyperlink to get me to the end of the database. I would have to re-edit the hyperlink each time I do a new entry but that would work. It would be reasonably simple and quicker than my current process (CTRL+F).
The other thought was to use a macro to find (say) the cell that has XXX which is at the top of my next blank template. I would need to shift it after each new entry to the next blank table.
I would be grateful for any suggestions. Your input has helped me to think of alternatives that I would not have previously envisaged.
kind Regards
MalR
 
MaIR - can you upload a sample workbook? It's much easier to help if we can see something similar to the file concerned.
 
I need to sign off on this one. Thanks for all the input guys. Because I need to be able to access another worksheet from anywhere in a separate worksheet I settled on two options. Cntrl+G and the one I am currently using, a hyperlink. In both cases it requires setting a target cell in the required worksheet. This target must be changed as the sheet grows but it is now a lot quicker to reach the part of the table that I require. Hope this makes sense.
Thanks again. Project closed.
MalR
 
You must log in or register to reply here.
Back
Top