IF text string equals certain criteria then specified calculation

[davemojo82]

I have 5 cells with drop down validation lists for the different types of window that we offer. From this list I will select the options and then input some dimensions in cell dimension fields. I then have a cell that gathers the text string from the 5 drop down cells.

My aim is to be able to calculate glass sizes from the text string and the dimensions. Each of the possible text strings will require different calculations and will define the sizes that I need to order. I know the equations that I will need for each of the options but cannot think of how to make them dependable on the text string cell.

I am looking for some way of using IF function to say if the text string returns as certain text then there is a certain calculation to calculate from the dimensions for the glass sizes that I need. I dont know if I've explained it too well nor even if its possible but if it is possible I will find a way as I have done some pretty exceiting things with excel in the past.

Any help would be very much appreciated. I have attached my idea to try and help explain.

Kind regards,

Dave

[pike]

hey davemojo82
you can modify and create new funtions to populate the values

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

just add a module to workbook and add codes

[davemojo82]

I am not familiar with macros nor VBA script, this is why I often try every single route in excel formulas first. I think I'm describing my problem incorrectly and I need to be more logical with it.

Sorry for the inconvenience but could you have another look at what I mean please? I have re-attached an updated sheet that will hopefully show what I need to do.

Thank you for your support.

Kind regards,

Dave

PS. This sheet really would be a huge help and I know that I just need to ask the right questions so please bear with me!!

[pike]

no problems
Is it possible to select all the different combinations of the dropboxes?
If so can you make a total combinations list ,the outputs andtheir multipling factors?

we can just expand the User Defined Fuction to calculate the desired results

[davemojo82]

Unfortunately the drop down boxes will be dependant on each other and will need restrictions depending on the other selections. This was my next question but you've beaten me to it!! lol

It sound like you have a plan though?... Please divulge?!!

Kind regards,

Dave

[pike]

nothing special but if you know all the combinations of the dropboxes and the desired multiplier
then use a big User Defined Function like

Please Login or Register  to view this content.

where dropboxi, dropboxii, dropboxiii, dropboxiv are the dropbox cells
and inputi is the first input

[davemojo82]

If you have time, I would really appreciate some guidance on how and where to input the below advice. If not do not worry I will research at home later.

Thank you again for this.

Kind regards,

Dave

[pike]

the idea is to reuse functions one to four

and five and or six to add values

Please Login or Register  to view this content.

with
=A2-Five(four(B11,three(B10,two(B9,one(B8)))))


the generated numbers can also be use to match index a list/table
but we will need a list/table of the values

[davemojo82]

I have realised that I know little about excel programming and user defined functions, this is all going straight over my head and the more I research them the more confused I get.

I have opened a module and cut and paste your UDF into Visual Basic but that is really all I know how to do. The function doesnt work because it needs more work but for me to better understand it I need to see something working so that I may build on it. I can normally persevere with things like this but I dont seem to be understanding the concepts of UDF and I cant visualise how it is meant to look.

I'm thinking that this is a little out of my comfort zone and although I really could do with completing this project, I would understand if you wanted to leave any further development due to the possible size of the required UDF and my lack of knowledge.

Thank you for your support to date.

Kind regards,

Dave

[pike]

if you make a dummy table/list of the final factors I can direct you as the code/functions will be relatively easy

its upto you

[davemojo82]

I have given the individual options from the drop boxes a number (in a seperate column) and am now writing very large IF function for each variable. This makes it a lot easier for me to see how it will all work. When I have finished this maybe I can show you and we can see if a user defined function is possible?

Thank you again for your support, I will be in touch once I have completed the above.

Kind regards

[pike]

sounds good