one time calculation for a cell

  • Thread starter Thread starter sowetoddid
  • Start date Start date
S

sowetoddid

This is a tough one...

Sheet1: contains 5 columns with empty cells that need to be filled wit
quantities of emissions. Each row represents a different emissio
scenario.

Sheet2: contains the gas speciation (percent quantities of eac
pollutant in a gas stream) in cells A11 to A25. Also, cell B1 contain
the volume of gas.


Problem: A quantity of emissions inputted in row1 of sheet1 i
calculated from the values inputted into sheet2. The 5 columns in row
will automatically be filled.

However, when a second emission event occurs and row2 must be computed
the numbers in sheet2 will be changed to represent a new gas release.
This will correctly calculate row2 but will also change row1, making i
incorrect.

How can I perform a one time calculation for row1 (save it in a way)
and the next time perform a calculation in row2 without changing th
previously computed row1 values?


Thank you!
 
You could use a macro to place your formulas and then turn into values or
just use the macro to create the values from within the macro itself.
 
As soon as row1 has its data, Copy row1 and(in place)Paste Special>Values

Gord Dibben Excel MVP
 
You can change your formulas to values once the calculations are done.

To preserve the values, copy the 5 cells, then do a Paste Special... back on
top of them, and choose Values.
 
I did not think of doing it that way, but I think I will run int
problems if I do.

Let me describe this again, maybe a little better...

Cells N41 through Q41 contain emission quantities for VOC, NOx, CO, an
SO2. Each emission is calculated with different equations and numbers.
But, from row 41 to 42 to 43, etc. the equations do not vary.

The user inputs his data into several different sheets, and it is to
much data to set out line by line with each new row. i.e. The use
cannot list the data for row 41 emissions all on row 41.

The problem is when the user moves from row 41 to 42 and updates th
data with new emissions, all of the emission numbers from row 41 ar
change. Row 41, 42, 43, etc. all reference the same cells.

I do not think the copy and paste special method will work because onc
an operator inputs the data and it is calculated and repasted, then i
is final. It would not be possible to correct data mistakes an
recalculate.

Please enlighten me
 
What would be the appropriate VB command to create a command button tha
completes the calculations. Next to each row (41, 42, 43...) there ca
be a button labelled calculate. If the button corresponding to row 4
is clicked, then its values will be changed. Otherwise they wil
remain the same.

For example...to make it simple

GasEvents!N41=ReleaseWorksheet!H26
GasEvents!O41=ReleaseWorksheet!H14
GasEvents!P41=ReleaseWorksheet!H16
GasEvents!Q41=ReleaseWorksheet!H13
GasEvents!W41=ReleaseWorksheet!H40
GasEvents!X41=ReleaseWorksheet!H42
GasEvents!Z41=ReleaseWorksheet!H41
GasEvents!AA41=ReleaseWorksheet!H43

When I click calculate next to row 41, the values in column H should b
populated into the corresponding row 41 cells

When I click calculate next to row 42, the same cells in column
should be populated into 42, without changing what is already in 41.

But, if I go back later and put in new values for row 41, I shoul
still be able to click calculate and refill the row.


Hopefully that helps. It seems that would work if I could get hel
with the Visual Basic.

Thanks
 
So, I would create a command button and place it in the "calculat
column next to row 41, another next to 42, and so on.

Would it be best to pull up the visual basic command screen and inpu
code, or is it possible to record a macro that would do this
 
Back
Top