Auto-link to another worksheet when text is entered in cell

[OZover]

Hello!

I need help setting up a worksheet that forces a link to another worksheet when text is entered.

For instance, I will have several columns in the first worksheet (Sheet 1). For each entry, a tally ("X") will be added under the applicable columns. Most of the column headings are pretty straightforward. To keep things uncluttered, I want one of the columns to have an auto-link (?) feature so that when the user adds an X in this one column (we'll call it Column D), the user will then be auto-linked to another worksheet (Sheet 2) in the workbook. Sheet 2 will have room for more information regarding Column D, Sheet 1.

Is this possible? Please help!

[JBeaucaire]

Hi OZover. Welcome to the forum.

Let me answer each and every question you'll ever ask in terms of "is this possible" with "yes, that's possible". Now that that is settled....

To get usable answers, though, it would be fastest if you show us. The main benefit of this forum is that you're allowed to attach sample files. That simplifies things SO MUCH.

So, just mockup a clear sample workbook showing the exact data layout you're using and your desired results, point out where the results came from if not obvious in your sample data. You'll mock everything up manually so we can see the goal. I'm sure we can recommend workable formulas or macros from there.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

[OZover]

Okay, great!

Here's my mocked up sample.

Sheet 1/"FY2010" has all the data that the user will be entering. I'd really prefer for the 1's to be Xs but that seemed to be too much trouble based on my requirements.

The user will enter the record in A:E and then tally in F:K based on their data. I want (for G ONLY) the spreadsheet to force the user to another sheet/"UCAs" upon entering text ("1" for a tally) in Column G.

As you can see, I've mocked up data in the UCAs sheet. In addition to forcing the user to the UCAs sheet, I want the data from A:E on the same row as the entered 1 in G that forced the link to be transferred here as well. You can see that I mocked up that data here, highlighted in yellow. Then I'd want the cursor to be in G of that new row on UCAs so that the user can begin typing more data (ie see how I left the newest row empty in G:I as this is what the user would see).

I have formulas set up for J & K, so they're fine as is. And the user would fill in H and I after G, so that's fine too.

So to recap, I want:
(a) to force-link to UCAs upon entering a "1"/text in G on FY2010; and
(b) to auto-fill A:E on UCAs accordingly (based on A:E of row that 1 was entered for G)


Any help would be great. Thanks!

[JBeaucaire]

So, if I were to put an "x" or a "1" in G25 then you would want instantly for Excel to switch to the UCA sheet and display A25:E25 from the first sheet now added to the bottom of the UCA chart and ready for you to work on?

Are you going to want this same functionality with the other columns?

[OZover]

That's correct. I only want that functionality for when text is added to G in the first sheet. But yes, I want A#:E# transferred to the second sheet automatically as well when text is entered into G for that row.

[JBeaucaire]

Sorry, I thought I had posted this back to you, my bad. This is a worksheet_change macro and it goes in the sheet module for Module1.

Please Login or Register  to view this content.

============
1) Right-click on the sheet tab FY2010 and select VIEW CODE
2) Paste the macro (given above) into the window that opens
3) Close the VBA Editor and save your sheet.

Now make a change in column G from G12 down and one of two things will happen:

1) If the item does not already exist on the UCAs sheet, it will be added to the bottom and formatted like the others.

2) If the item already exists on the UCAs sheet, you will be taken to that line on the UCA sheet for reference.

[OZover]

Thanks so much!

I followed your directions and pasted into the "View Code" window. Once I entered my first "1" in G on FY2010, I was forced to the UCA sheet but got an errror message about Debugging. I clicked Debug, and now the macro won't work anymore. I tried erasing it in the "View Code" window and repasting, but still nothing. Is it tempermental or did I do something wrong?

Sorry! And thanks in advance for your continued help with this.

[JBeaucaire]

Well, I'm intrigued. I can't get the macro to debug like that since I put an errorhandler into the macro, it shouldn't do that at all. Here's your sheet back with the macro already installed.

If it really is doing that, I'll need more specific information about what row your "x"ing and what the value in column C is and ... well, I guess I need you to post your sheet with instructions on how to duplicate the problem.

===========
NOTE: The reason you can't get it to work after you DEBUG is that macros are turned off at the top of the macro and back on at the bottom of the macro. If the macro does not complete, which it should with the error handler in it, then you will need to turn macros back on.

Press CTRL-G in the VBEditor to open the immediate window.
Type this into the bottom of that window on a blank line and press enter:

Please Login or Register  to view this content.

[OZover]

Hello! Happy Holidays!

That seemed to work! I just have to make sure that I instruct everyone to ALWAYS enable macros when opening the workbook.

One more kind of silly question - and it's no big deal if there's no easy solution - but when I get forced to the second sheet after entering text in Column G on the first sheet, my cursor appears in the first column of copied information. Is there a way to get it instead to move to the end of the copied cells and appear in Column G (UCAs Sheet - just a coincidence that it's the same column letter) of that copied row?

Thanks so much for your help so far!!!

[JBeaucaire]

If I read that correctly, then maybe change this:

Please Login or Register  to view this content.

==========
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and add [SOLVED] to the start of the title.


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)

[OZover]

I'm sorry! It worked for a big and then I got the debug error window again.

Here is my EXACT spreadsheet. When you type a "1" in Column G on the first Sheet, it was forcing me to the second sheet and then I got that debug message. I also got something about a circular formula and I got a dotted circle around the newly added cells on the second sheet.

Is any of this making any sense?

[JBeaucaire]

I just "x"d my down the entire column and every value transferred as expected.

I did notice one small change you should make:

Please Login or Register  to view this content.

[OZover]

I just tried on the copy on my computer and nothing transferred. So then I opened the copy that I had uploaded for you, and again nothing transferred. I made the change you mentioned, and I've been "Enabling Macros" when I open the document, so why isn't it transferring for me?

I'm really baffled. I got an error message again this morning about the Debug thing and it said that my action would stop the Debugging for the future. I thought that was good?

[JBeaucaire]

Dude, I feel for you. I d/l'd your file again, opened it, put an x in column G and it immediately fired. The macro is fine, your sheet is fine. The problem must reside on your setups somewhere.

My system asks me about enabling macros on every macro-file I open. This is a good thing. I enable macros, and when errors do occur, I choose DEBUG so it takes me to the code and highlights the line of code it stopped on. I don't know of a way to turn off debugging forever, if that's what you're experiencing.

Sorry, the issue you're having is program-related and I can't imagine what it could be from here. I don't want to encourage you to make security setting changes, but you may have to.

[OZover]

Thanks for your help!

[JBeaucaire]

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and add [SOLVED] to original thread title

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)