What-If Scenario

**ORWS** · 04-24-2017, 04:00 AM

All,

Been trying to find answers via Google but no luck so far.

I'll try to keep a complex case very simple by coming up with a generic example. In my Workbook X, Sheet 1 is a form with 4 input cells: A1 to A4, and 8 output cells: B1 to B8. The calculations to populate the output fields based on the input fields take place in Sheet 2 to Sheet 5 using a multitude of formulas and look-up tables. For every calculation, all four input cells (A1 to A4) have to be filled and all eight output cells (B1 to B8) will give a result.

In an entirely new Sheet I would like to make a simple table which will eventually contain about 1,000 records. Each record is a result of the calculation in Sheet 1. Columns A to D in my new table will be the input for cells A1 to A4 in Sheet 1. Columns E to L should give the output of cells B1 to B8 in Sheet 1.

Seeing as I am working towards 1,000 records I want to automate this instead of manually copy pasting the 8 output cells in a record. How do I pull this off? For multiple reasons (the complexity being one) it is currently impossible to incorporate the logic that populates Sheet 1 B1 to B8 in every potential record in the new results table.

**Richard Buttrey** · 04-24-2017, 04:31 AM

Hi,

The way I'd approach it is to create a named range of 12 cells on the same row.

Then all you need is a single line macro along the lines of

Please Login or Register  to view this content.

Now all you need to do is identify something that will trigger the macro. Perhaps a button which you click, or maybe a SheetChange event macro that first checks to see if a new record is ready to be added.

Incidentally would you change your location to something meaningful please. Most of us do inhabit Earth so it tells us nothing and knowing your location is sometimes helpful when we may need to consider time zones or regional settings for dates, list separator characters and other stuff

**ORWS** · 04-24-2017, 04:39 AM

I'm sorry, didn't know the Location field served that purpose. Updated accordingly.

Thanks for your suggestion, I'm going to take a look at it.

**Richard Buttrey** · 04-24-2017, 06:31 AM

Originally Posted by ORWS

I'm sorry, didn't know the Location field served that purpose. Updated accordingly.

Thanks.

**ORWS** · 04-24-2017, 07:30 AM

Been messing around with what you've said but I think I did not make myself clear..

My aim is to populate the 4 input fields in those 1,000 records manually and have the calculation populate the 8 output fields.

**Richard Buttrey** · 04-24-2017, 07:37 AM

If I've misinterpreted your data/requirement I still think the technique I mentioned or a small modification will be able to be used.

However upload your workbook, or at least a representative subset of it, and manually add some results. Clearly explain what data you started with and which cells contain the results.

**ORWS** · 04-24-2017, 08:40 AM

Unfortunately I cannot share (a part of) the actual Workbook due to its content so I've made a very simplistic representation of the issue I'm facing.

Sheet1 FORM:
The white cells are the input fields, the grey cells give the output based on calculations done in Sheet 2 FORMULAS.

Sheet 3 RESULTS:
By populating the 4 input columns myself, I'd like Excel/a script to automatically input those 4 input variables in the calculation on Sheet 1 FORM and subsequently copy the 8 output variables of Sheet 1 FORM into the 8 output columns of the table on Sheet 3 RESULTS.

Perhaps the biggest restriction is that I cannot change anything on Sheet 1 FORM and Sheet 2 FORMULAS, except for the input fields on Sheet 1 FORM, I can have a formula or script populate those fields instead of populating them manually. So to put it simple, I need something to:

- read the 4 input variables off a record in Sheet 3 RESULTS
- paste them in the 4 input fields in Sheet 1 FORM
- read the 8 output variables off Sheet 1 FORM
- paste them in the 8 output fields in Sheet 3 RESULTS
- repeat the above for all records in Sheet 3 RESULTS that I provide 4 input values for