Creating a worksheet based on conditions

[tbloch]

Hello all, and thanks for looking.

I consider myself fairly proficient with excel, but then I tried to take on this task. I am fairly new with this type of excel coding, but I have coded with several other programming langauges in the past and think I can handle it with a few pushes in the right direction.

I want to create a macro that when I hit a button, I create a worksheet, possibly from a template, and add it to the workbook. I would like to select different options using 3 drop down menus on the same page as the "create" button. Based on these conditions, I would like the macro to create a sheet and highlight/display values off of the template.

I think I could do this with a lot of "if menu1 = 1 and menu2 = 1 and menu3 = 1 then copy this template to the end of the workbook" statements. However, this seems like it would take a lot of if/then statements and lot of templates (one for each possibility). I would think it could be done simpler.

Any help would be great.

Thanks

 

[dave3009]

Hide all your template sheets and remember which one's which

Then use the dropdown boxes to give some kind of unique identifier in a hidden cell (you could use the form controls and link it to a cell return a number between 1 and however many items are in the dropdown). From this unique identifier you could use a Select Case statement which would identify the sheet to copy. Macro would execute in microseconds.

Would this kind of thing help?

Dave

 

[tbloch]

Hide all your template sheets and remember which one's which

Then use the dropdown boxes to give some kind of unique identifier in a hidden cell (you could use the form controls and link it to a cell return a number between 1 and however many items are in the dropdown). From this unique identifier you could use a Select Case statement which would identify the sheet to copy. Macro would execute in microseconds.

Would this kind of thing help?

Dave

First, thanks for your quick reply!


Hmmm... that could possibly help. I wanted to try to avoid the use of the hidden templates, but that prolly is the best way. Two of the choices have 5 conditions and 1 has 6, so that is what, like 150 possibilites?

If I follow you, you are suggesting that I make a cell the value of the first menu (say 1-5) and the another the value of the second (1-5) and a 3rd cell the value of the last menu (1-6). How would I combine these to make a unique value in a 4th cell that I could relate a case select to?

Thanks

 

[dave3009]

How different are the templates, if they are pretty standard and it's just the highlighting that's different then you could use the Select Case to provide the highlight which might be more efficient.

So lets say you have 5 template sheets, one of the Unique IDers would choose the sheet, the next UID would choose some highlighting etc.

Really you want to try giving out as much info as possible, I think creating a fully fresh sheet from scratch each time you run the macro will test the users patience so ideal you want it to be quick and seemless by getting as much basic stuff hidden out the way and save redoing it everytime.

Bear in mind that you could use a macro one single time to create your 150+ permutations.

EDIT:Actually rereading that you need to tell us what each of the combo boxes will do to the template, so you might need 3 select case statements.

 

[tbloch]

Well, I just typed a really long explaination out, and lost it b/c it thought I wasn't logged in or something. I'll get back to you in a bit.

Thanks

 

[dave3009]

At the login there is a box that says 'Remember Me' check it or the site will kick you out after a defined period of time. If you are on a public computer or one you don't feel safe staying logged in then just type up your reply in Notepad or Word and paste it in to the reply box.

 

[tbloch]

At the login there is a box that says 'Remember Me' check it or the site will kick you out after a defined period of time. If you are on a public computer or one you don't feel safe staying logged in then just type up your reply in Notepad or Word and paste it in to the reply box.

Well, I would be the end user for this excel workbook. The way it is done know seems so tedious and could be a lot nicer I think.

Currently, the workbook has several templates set up and I select a lighting fixture category (1-6), a room cleaniness (1-5) and I copy this worksheet to the end. I than enter room dimensions into cells and predefined equations in cells calculate values needed to be used with the category and cleaniness type to find a value. This value is currently found by looking up on a table or grpah, but I know the equation, so I thought that it would be much easier (and accurate) to let excel do this for me. One of the equations is something like exp(A^(t*B)) where A and B are coeffiecients found by looking at a table and knowing the fixture category and room cleaniness level. Also, the fixtures light distrubution pattern (3rd box, choices 1-5) would be used with ratios found with the deminsons to find another value from a table.

What I wanted to have, was a "start" worksheet where, one would select category, cleaniness level, and distrubution type and enter the dimensions and press "create" and have a macro set up to calculate all the values and display them.

I suppose a template isn't really needed for each case, just maybe have the case select insert (based on the UID of the possiblities) "you are using x category with y distrubtion type, in a z room." It seemed a lot simplier when I was thinking about it then when I actually tried to start coding it.

The end result: Several of the values calculated/looked up (using the user choices and input) are then multiplied together and displayed. This is done to document calculations.

Hope this made at least a little sense and hopefully I didn't forget to add much the second time around typing it out, hehe.


Edit: quick fix on a couple typos.

 

[dave3009]

Are the overall calculation functions going to change depending on the selection or is it just the values to be calculated?

Sounds like a single template feeding from three tables (one for each combo selection) via VLOOKUP would do, then your macro would copy this as display it as values only.

 

[tbloch]

Are the overall calculation functions going to change depending on the selection or is it just the values to be calculated?

Sounds like a single template feeding from three tables (one for each combo selection) via VLOOKUP would do, then your macro would copy this as display it as values only.

Hmm... I'll look into that vlookup function. The calculation functions don't change, just certian coeffcients. Those are looked up from a table depending on the choice in the drop down menues.

 

[dave3009]

This example may help but as I am working blind......

Lets say you have a table to look up from

Excel Workbook
	A	B	C	D	E	F
1	Table One	1	2	3	4	5
2	a	2954	1303	2129	1300	2776
3	b	1750	2427	2422	2632	1844
4	c	1704	2072	1373	1609	2806
5	d	1860	1354	2624	1251	2895
6	e	2264	2561	2854	2636	1687
Table_One

Excel 2007

Then more like this for the values, from this you want to look down column A for a value then across row 1 for another value from this you can select a value from the desired table at the desired point, such as:

Excel Workbook
	A	B	C	D
1	Table	Row	Column	Value
2	Table_One	b	4	2632
Master

Excel 2007

Cell Formulas
Range	Formula
D2	=INDEX(INDIRECT(A2), MATCH(B2, INDIRECT(A2&"!A:A")), MATCH(C2, INDIRECT(A2&"!1:1")))