Need help with a big project

**emailsbecker** · 02-24-2010, 10:21 PM

Hi everyone. I need help with something I've been tasked with at work. Although I have some ideas, it's pretty complex. First I'll lay out the design concept, and then I'll talk about specific strategies and formulas to achieve it.

***************************************************

Layout:

Right now we have an Excel spreadsheet that has 15 tabs on it. Each tab has an area where you enter some variables, and then formulas on that tab use the inputted data to create instructions to complete one task. Overall we wind up creating documents of up to 10 different tasks, with each section being an individual task created on its own tab.

Because the tasks we deal with can use any combination of the output from the 15 tabs (in any order), currently we manually go into each tab that we need, fill in the data, and then copy & paste the resulting instructions into a Word document.

The problems with this are that (a) there are a number of tabs that use the same data and having to input the same data in more than one place is labor-intensive, (b) each place the data needs to be re-entered is a new opportunity for entry errors, and (c) we've seen errors where the sections are being copied into the Word document in the wrong order. I've been asked to look into making a master sheet where we can input the data one time, select a couple of drop down menus to define what sections we need (and in what order), and have Excel put together the data from each tab in the order specified.

The easiest way I can think of doing this is to insert 2 tabs at the front of the workbook. The first tab will collect any variables that would be used across all tabs, and then have 15 drop down menus saying:

Paragraph 1:
Paragraph 2:
Paragraph 3:
...
Paragraph 15:

In each drop down menu I'd have to give the user the option of calling any of the 15 tabs, to ensure that they can be put in any order needed (plus a "Not Used" option). In the field to the right of each drop down menu I would have an if/then statement that gives a link to the tab chosen in the drop down box. The user would click the link, fill out the needed data on that tab, then click another link on that tab that brings them back to the front tab. Go down the line and fill out options as needed.

As the user is going through each drop down menu and doing what they need to do, on the second tab in the workbook the final product is being built. I would have pre-formatted header verbiage, and then I would need some kind of formula that looks at the first drop-down menu selection from the first page and sees what the user selected - it would then pull the created instructions from the specified tab. Then another of those, and another, for each drop down box on the first page that is used. And lastly, some footer verbiage.

***************************************************

So. How to do it.

First I need to create a hidden area of 15 fields, each one being the name of a tab. I would use this to create my drop-down menus on the first page. Then I'd need to create another 15 fields, each one having a hyperlink to one of the 15 tabs. Then I create an if/then section that looks at the drop-down menu selection and fills the field next to the drop down with the matching hyperlink, based on the users choice. I'm wondering if there's an easier way to do this than nesting 15 if/then statements... if there is, please tell me!

The formulas on each individual tab are already working, so all I need to do is add a hyperlink back to the first tab. That's easy.

On the second tab is where I'm really stumped. I know I need to do an if/then statement to look at the selection on the first tab. If the selection is "A", pull the data from Tab A, if the selection is "B", pull the data from Tab B. But the resultant instructions created on each tab are not all the same length. Some are 2 or 3 lines long, some are 25 lines long. I suppose I'd need to use some sort of range command, but how do I allow each option 3-25 lines of space and have it formatted nicely? I suppose I could put them all 25 lines apart and have the users delete the extra lines in Word as needed, but it would be nice to present a finished product to just copy & paste.

All help will be appreciated!

Thanks

**teylyn** · 02-24-2010, 10:34 PM

Hello emailsbecker,

welcome to the forum. However, your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

**emailsbecker** · 02-25-2010, 07:30 AM

Hi Teylyn. I'd be happy to change the title to something more fitting, but I don't know how the Excel terminology to describe what I'm looking for in that last paragraph. If you could give me the keywords you'd like to see I'll update the title.

Thanks

**teylyn** · 02-26-2010, 11:00 PM

This seems like a big chunk of work. Why don't you break it up in smaller tasks and ask for pointed help task by task. That way, it will be easier for you to find a suitable title for each thread, and the volunteers here would not be so overwhelmed by the sheer complexity of it all.

I for one, glazed over in the second paragraph and did not read the rest.

**emailsbecker** · 02-27-2010, 08:06 PM

Here's the paragraph I'm talking about:

On the second tab is where I'm really stumped. I know I need to do an if/then statement to look at the selection on the first tab. If the selection is "A", pull the data from Tab A, if the selection is "B", pull the data from Tab B. But the resultant instructions created on each tab are not all the same length. Some are 2 or 3 lines long, some are 25 lines long. I suppose I'd need to use some sort of range command, but how do I allow each option 3-25 lines of space and have it formatted nicely? I suppose I could put them all 25 lines apart and have the users delete the extra lines in Word as needed, but it would be nice to present a finished product to just copy & paste.

...if you can tell me what the techniques are called that do that, I'd be more than happy to open a separate thread for it. Without knowing what to call it though, I'm stuck with "need help with something I don't know what to call it."

Thanks!