"If-Then"...

[Nicholas]

I'm trying to set up something like an if-then set up, however, with a Drop-Down-Menu.

Essentially a 6-Tab file, with only ONE tab to actually be viewed. The other five will consist of the actual input data for the 'behind-the-scenes' work (data entry) that I will be doing. The 'MAIN' tab for actual viewing will be for everyone else's use. On this 'MAIN' tab there is a drop-down menu listing 16 viewing options from the 5 other tabs respectively (3 options per tab and one 'overall' option).

Once a person selects an option from the drop down menu (Let's say "View Tab 1, #1 out of 3") it should auto-populate the selected fields on the "MAIN" Tab with all the information from Tab 1 #1 out of 3. Another problem is: this information ("#1 out of 3" on Tab 1) isn't a single cell, but a range of roughly 30 cells, meant to populate another 30-cell-range on the "MAIN" Tab.

Can anyone help?

I really appreciate you all reading this!

[broro183]

Hi Nicholas,

I'll have a play & see what I can do but it would be much easier if you upload a zipped workbook as a starting point for people to help you.

Also, I don't know the layout of your workbook but I'm curious, do you need separate sheets?
Could you copy all the data onto a single sheet so it becomes a "database" with one set of headers with extra columns added stating which sheet/area it came from?
If so, once you have a "database" you can create a Pivot table (poweful tool with built in drop downs) on a separate MAIN sheet & use this with the column identifying the source sheet as a "page field" for viewing. Check out Debra's Pivot Table pages listed on her index page http://www.contextures.com/tiptech.html
starting with http://www.contextures.com/xlPivot01.html

btw, there are ways of consolidating different sources into a Pivot Table rather than consolidating all your sheets into one - but I haven't been able to make these work for myself...

Rob

[broro183]

hi Nicholas,

Here's a very quick example file that uses the Indirect function in conjunction with drop downs.

hth
Rob

[Nicholas]

Hello Rob,

Thank you very much for the fast reply! - I see what you mean about the 'quick mock up' - It's very like what I need, and the separation between "Sheet" (1 through 5, + the "Overall" option, (or "1+5")) and sub-"Option" (1-3) is *perfect*, and in fact much better than my original idea of one drop-down with 16 sub-options; likewise, the 'single sheet, database' format, rather than 5 separate pages in addition to the 'Main' page is a much better idea. My train-of-thought throughout initial conceptualization had been "simplicity" and "avoiding confusion" for others who would be working with this tool, however, I completely overlooked the fact that the only tab anyone but myself will be viewing is 'Main'!

I've gone ahead and attached the formatting so far of what I'm looking for. Obviously it's only the facade, as there's still no real 'coding' content for it to fall back on.

[broro183]

hi Nicholas,

Sorry for the long delay in getting back to you.

I haven't tried redesigning your file to a single sheet database style b/c it looks like this will be a weekly template & info can/will be changed on a weekly basis rather than being added to continuously & what you've got seems fine.

The attached is just an updated version of your facade using the same principles as I put in my mock up.
I haven't got the formulae right for the "Overall" option b/c I'm not exactly sure what it should be summing up - is it the total for the week for that particular pull?


hth
Rob

[Nicholas]

Not at all,

Thank you for being so helpful with this!

Your reformat looks perfect. As to the "overall" option in question: the plan was to have it populate with the weekly total; however, there's no real need to play with that any further, as I can just as easily add a separate tab solely for this purpose.

Thanks again for being so helpful: I had been trying to figure this all out on my own but I think I'm a little out of my depth on this until I've had more time working with Excel.

[broro183]

hey Nicholas,

Thanks for the feedback - I'm pleased I could help :-)

As the saying goes, If it doesn't kill you it can only make you stronger! ;-)

Progessive gradual overload is the only way you'll find out more & improve - so keep it up. I suggest making the [F1] key your first port of call for specific questions (as they pop up) & then Google/asking questions on the forums. The more you investigate before you ask the more you're likely to learn & hopefully you'll be surprised at how quickly things seem to be "in your depth" & then become too shallow.

Here's a link to useful links on this forum:
http://www.excelforum.com/showthread.php?t=620254
& a list of some Excel MVP's:
http://www.mvps.org/links.html#Excel

Rob