• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Best method to complete 1 xls set of calculations with an array of multiple values ?

LorraineT

LorraineT

New Member
Hello everyone, I've searched the forum (and joined Excel school too! :)) to try to find a way to automate this task. I have a simple xls sheet with picklists that performs very simple calculations (multiply, divide, %) with a set of variables inserted manually.

This sheet is publicly available at this link (and uploaded as well, for your convenience). All the variables in the yellow-filled box of the sheet need to be either selected from the drop-down lists, or filled in = 13 variables in total.

The rest of the sheet is automatically calculated.

The final result - filled in yellow area, and subsequent calculations - must be inserted into a word file for submittal to authorities.

This is a semi-tedious task :eek: that i would love to find a way to automate. For example, I'm working currently on 9 products that are used on 9 different crops.
I am sure that there is a way to automate this in Excel - but i haven't figured out exactly how.
I noted the very interesting post with some VBA code to export data from Excel into a Word file - in my example there may be too many variables to do that.
But if, at least, i could automate populating the table with an established array of variables for each product...that would save me SO MUCH time!! as well as dozens of other people who slog through this document to prepare their submittals in Europe.
thank you to all for your wisdom.
 

Attachments

Hi Lorraine ,

I have not understood the following :
But if, at least, i could automate populating the table with an established array of variables for each product...that would save me SO MUCH time!!
Click to expand...
Which table are you talking about , and what is the established array of variables ?

Narayan
 
Hello Narayan, indeed I didn't post the array of variables that I would use. I upload one here. The table that i'd like to populate automatically is the one that i uploaded in my previous post, called UK_POEM_07.
 

Attachments

Hi Lorraine ,

OK. So what you are saying is that you have several products , each of which has its own values for the 13 variables ; you will put in these values for each of these products in what we can call the master file , which you have named OPEX INFO.xlsx

In the other workbook , which you have named UK_POEM_07.xls , you can have a drop-down list of the several products ; depending on the product that is selected , all the 13 variables can be retrieved from the master file ( OPEX INFO.xlsx ) and put in the appropriate cells in the range B3:H10 , doing away with all the existing drop-downs ; is this correct ?

Narayan
 
I don't completely understand your requirement, but if you are wanting to loop over some variables, this might get you some ideas.
Code: 
Sub LoopThings()
Dim ProdNames As Variant
Dim SubNames As Variant
 
ProdNames = Array("Apple", "Banana", "Candy")
SubNames = Array("Acid", "Baking Soda", "Coffee")
For p = 0 To UBound(ProdNames)
  For s = 0 To UBound(SubNames)
  Range("B4") = ProdNames(p)
  Range("F4") = SubNames(s)
  MsgBox "Check me"
  Next s
Next p
End Sub

Also, maybe because I'm just nosy, :) but I noticed your Word rate/day formula is rather bulky.
=IF($J$14=1,$R$5,IF($J$14=2,$R$6,IF($J$14=3,$R$7,IF($J$14=4,$R$8,IF($J$14=5,$R$9,IF($J$14=6,$R$10,IF($J$14=7,$R$11,IF($J$14=8,$R$12,$R$13))))))))

You can simplify this to:
=INDEX(R5:R13,J14)

which is easier to read, IMO.
 
here are the variables with some examples - the previous file i uploaded had only the variable names without any concrete examples - perhaps this is a better illustration.
 

Attachments

Luke M said:
Also, maybe because I'm just nosy, :) but I noticed your Word rate/day formula is rather bulky.
=IF($J$14=1,$R$5,IF($J$14=2,$R$6,IF($J$14=3,$R$7,IF($J$14=4,$R$8,IF($J$14=5,$R$9,IF($J$14=6,$R$10,IF($J$14=7,$R$11,IF($J$14=8,$R$12,$R$13))))))))

You can simplify this to:
=INDEX(R5:R13,J14)

which is easier to read, IMO.
Click to expand...

Thanks a lot! that is indeed much simpler. I am not the creator of this "UK_POEM_07" sheet - it comes from a British Ministry and was created in 2007.
 
NARAYANK991 said:
In the other workbook , which you have named UK_POEM_07.xls , you can have a drop-down list of the several products ; depending on the product that is selected , all the 13 variables can be retrieved from the master file ( OPEX INFO.xlsx ) and put in the appropriate cells in the range B3:H10 , doing away with all the existing drop-downs ; is this correct ?

Narayan
Click to expand...

yes that's correct! by removing the drop-downs, then it would be easier to automate from my "master file", I think.

thanks Narayan for your quick exploration of my question, i appreciate it!
 
Hi Lorraine ,

So can you complete the data entry for all the products in your master file ?

All that will be required in the file UK_POEM_07.xls will be a drop-down list of all the products , and the required formulae in the various relevant cells. This I can upload tomorrow , unless someone else can do it today itself.

Narayan
 
Hello Narayan
thanks again for having a look at this! when you replied, I was away from my desk to collect my family members from school and work + dinner etc...
What you suggest to do sounds great! i'll prepare the master list.
The one I posted was just a fictitious example.

Is there a way to automate the insertion of each set of variables into the UK_POEM_07 file? with a macro for ex that would select each set of variables in turn, then "save as" a separate pdf? That is ultimately what i need to produce, I have uploaded a file to illustrate what the "end result" looks like.

In fact, I have 2 other worksheets that conduct similar calculations - each one requries a slightly different Master List of variables. , and I've also uploaded the other worksheets that I need to use in a similar manner.

I would like to be able to use this same approach for the other worksheets as well.

I have some additional questions for you:
Will I be able to modify the drop-down lists in the UK_POEM_07 file if I need to?
I ask this because
- I'd like to be able to use this again in the future, and the master list variables might change if I add other products to it
- after submittal of this information, sometimes I'm asked to modify some of the variables. Consequently i'd need to be able to modify them in the drop-down lists.
Thanks again for your support! :)
 

Attachments

Last edited:
Hi Lorraine ,

I was trying to get your work done yesterday , but realized that Data Validation drop-downs do not accept external references ; I tried using formulae to link to the external file and have a copy of the product list in the UK_POEM_07 file , but it does not work when the external file OPEX_INFO is closed.

Is it possible that instead of the product data residing in an external file , it can be made a part of the UK_POEM_07 file ?

I will be away for a couple of days , and will not be able to reply to your questions for some time.

Narayan
 
Thanks very much Narayan for your efforts! yes i think that it would be ok for it to be incorporated into the UK_POEM_07 file.

Sorry to hear that you will be gone - I should have asked my question earlier. but will see what you find when you return. Meanwhile i will prepare these as I usually do.
But i'm still interested in an automated solution, to use for the near future.
thanks again, kind regards, Lorraine
 
Hi Lorraine ,

You are in safe hands. Luke ( Luke M ) has agreed to take a look. Since he has already posted once in your thread , he can continue with your latest uploads.

Narayan
 
Excellent! many thanks to Luke as well.
I will upload then my actual Master Variable File so that both of you can see what might be possible
 
Hi Lorraine,

Apologies if this is something Narayan already figured out, but is the task to:
Iterate each drop down through all the choices and save a pdf at each interation?
Do we need to do anything with the green boxes?
 
Hi Luke
no problem for me to re-explain a bit, i'm happy that you are available to examine this!
[by the way, I discovered another cell where i could replace the formula with your "INDEX" suggested formula - the "Volume of surface contamination" cell formula. I'm very proud of myself :cool:]

The file i've uploaded called "ExtractPage1.pdf" shows you what the finished document looks like.

Yes it would be iterations through a series of selected variables, with a pdf produced for each iteration.
Yes, the green boxes must also be filled in, they are "free text" i.e. there is not an imposed list of parameters that one must choose.
All the green and yellow boxes are filled in order to obtain a completed calculation.
However, it is NOT a systematic iteration through all the drop-down list possibilities. The drop-down parameters are selected versus the "best fit" for the planned product use. (this is where experienced humans like me are actually useful for this exercise!)

Note, also, that there are 2 of this type of calculation sheet: one for liquid products (called "Liquid Concentrate Formulations tab" and one called "solid concentrate formulations" tab). The type of formulation leads to a slight difference in some of the drop-down parameters.

Note in the final value at the bottom of each finalised calculation in "extractpage1.pdf" upload: there is a value expressed as "% AOEL".

The objective of these calculations sheets is to obtain a "% AOEL" value that doesn't exceed 150% approx. The modifications of variables in the green box, or a change in the selection of the dropdown lists, can help reach this objective.
I will complete 1 calculation with my own "master list variables" for 1 product, and upload it so that you can see perhaps more clearly.
Thanks again for your help with this! Additional upload to come soon
 
I'm afraid my brain is going a little slow today. :(
I can see from that latter file that one or two of the variables get changed between worksheets (and a different name). Do the other variables need to be changed as well, or just those?
Looking at the table in "UK Liquids Pomfruit", I am interpreting that as taking a column and those should be the variables to use? Is that true? If so, do we need to do anything else with the specific product, or move on to next product/column? If the former, I think the problem is figuring out how to get the code to input things from this table into the correct sheet location, and then saving the pdf. If there is other variables that should be iterated through (possible to make sure AOEL% < 150?), then I'll need you to tell me.

Apologies if I'm missing the forest for the trees.
 
You must log in or register to reply here.
Back
Top