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

 

[tbloch]

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

#VALUE!

Excel 2007

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

Yes! That does help! In my case, value b4 would be if I used b from frist drop down menu and 4 from second drop down menu. I would used the value from D2 in my exp(A^(t*B)) where D2 would be the B value.

I'll try to throw up the equation and table if I can figure out how.

 

[dave3009]

Well you see how it goes together, you can return these to a visible or hidden cell and perform your calcuation from there, or you can just nest them in the EXP function which would do just fine.

I think it's probably more efficient just to go with return to cells first then perform EXP function, Excel should only calculate a function that changes, if they are all nested in one another the calculation will happen in full each time a value is changed.

 

[tbloch]

Well you see how it goes together, you can return these to a visible or hidden cell and perform your calcuation from there, or you can just nest them in the EXP function which would do just fine.

I think it's probably more efficient just to go with return to cells first then perform EXP function, Excel should only calculate a function that changes, if they are all nested in one another the calculation will happen in full each time a value is changed.

Ah, makes sense.

I guess I should also mention that I am using 2003 if that changes much.

Thanks for your help!

 

[dave3009]

No it shouldn't change anything. Let me know how you get on, I had a hard day so I'm off to put my feet up but I'll check in from time to time.

All the best (and I should have welcomed you, so welcome to the forum )

p.s. if you want the board HTML Maker PM me your email address and I'll ping it across to you. Or to give credit where credits due pm Richard Schollar

 

[tbloch]

Will do! Thanks again.

Now if I can figure out how to insert a table, hehe.

 

[dave3009]

HTML Maker will do that for you, nice and easy one click operation then paste

 

[tbloch]

HTML Maker will do that for you, nice and easy one click operation then paste

Well, actually, I mean making a table in excel. I'm having a very hard time just to make a table to look up values from. I guess my excel skills really werent all that great.

 

[tbloch]

I ended up using the VLOOKUP function and it's working really well for what I need. I'll prolly have a few more questions for ya, but thanks for your help!