Data Table from Spreadsheet Application

HydroGeo

New Member
Joined
Nov 14, 2013
Messages
7
I am using a couple of spreadsheet applications using Excel. The applications require inputs for certain parameters and then output predicted values. I want to vary the inputs and create a data table showing results for each scenario. Can I do this using easily a combination of macros and “what if” scenarios?

For example, one application predicts the concentration of a volatile chemical in the air inside a building. Inputs include various parameters, such as the chemical name, concentration of the chemical in the soil, soil type, soil moisture content, exposure duration, building dimensions, etc. Some parameters can populate automatically based on other inputs. For example, a default soil moisture number is based on the soil type. Let’s say I keep all other inputs the same and just want to run the application for several chemicals. I have a table with the chemical names and concentrations. I want run a routine of some sort that will input each chemical name and concentration into the application, calculate the results, and then tabulate the results for all the chemicals.

I tried simply using a data table and copying the cell that shows the results, but it gives me the same value for all chemicals. Obviously, this approach does not substitute the chemical name and concentration for each scenario. I can’t copy the actual overall equation that provides the result because it is complex and based on numerous cross-linked equations within the application.

Just running the application for a list of chemicals and their concentrations is the minimum I would like to achieve. It’s tedious to go back and enter each chemical individually. Even better would be the option to vary other input parameters, too. For example, given a certain concentration for benzene at the source, I would like to know the results for various scenarios using a range of soil types.

I hope I explained this properly. Any help would be appreciated. I’m a scientist and not a programmer. So, I was hoping to do this using built in macros and Excel capabilities rather than writing a script. I don’t know VBA or coding, though I am thinking I may have to learn them to get more out of Excel and/or Access.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
... you can extend them for more complex scenarios by storing your scenarios elsewhere and making one of the input variables a scenario.
Whuchoo talkin' about, CC?
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
I've not seen that, thank you. Bookmarked it for later perusal.
 

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
You're welcome. I find data tables quite useful and the techniques described in that post make them very flexible, especially where VBA is not used, or you want to break down complex formulae into a series of smaller, better documented steps and apply the results to a range of scenarios.

Anyway, hope you like it.
 

HydroGeo

New Member
Joined
Nov 14, 2013
Messages
7

ADVERTISEMENT

Thanks, everybvody. I figured it out. All I had to do was to set one of the input parameters as equal to a cell in the data table. I used the CAS number of the chemical as the key input. So, I set the CAS number in the application =FirstCell_in_my_data_table Concentration was based on a VLOOKUP using another table with CAS numbers and concentrations. Then I could run a one-variable data table to get the results for all of the compounds!
 

HydroGeo

New Member
Joined
Nov 14, 2013
Messages
7
Now that I have the data table working, I wonder if I can run a macro or something to print a the data entry table on the first page of the application for each chemical. So, it would enter the chemical data, calculate the result, and then print the data entry table to a PDF. If that is too complicated, I think the only thing is to make a ton of copies of the data entry worksheet and the results worksheet, link things using VLOOKUP, print all worksheets and then delete everything I don't need. Seems like a lot of work. Must be an easier way.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,915
Messages
5,598,850
Members
414,263
Latest member
sherrcha

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top