# Thread: Auto Calculation based on cell? (2002 SP3)

1. ## Auto Calculation based on cell? (2002 SP3)

This is going to be a weird request so bear with me. I've attached a sample file which is one sheet out of more than 100 where you will see some current fees. What someone here would like is to have the current amount overwritten based on an increase of the Consumer Price Index (CPI) ONLY IF they select it. What they are trying to do is do a 'pick and choose' the fees they want increased instead doing them all. The 'mockup' I came up with is on the second sheet but I really have no idea how to go about doing this. They DO NOT want a formula in another column. I am baffled so all help will be appreciated...

The updated fee would consist of the increase in CPI which I made up to be 4.5%, plus the original fee.

Thanks.
Pooja

2. ## Re: Auto Calculation based on cell? (2002 SP3)

Here is one way to do what you want:

1- Enter 1.045 (1 plus 4.5%) into any empty cell.

2- Select that cell and then select Copy from the Edit Menu.

3- Select the cells that you want to increase by that percent.

4- Select "Paste Specisl" from the Edit menu.

5- In the Operation section of the resulting dialog box put a tic mark next to Multiply.

6- Press Enter.

7- The selected cells should be increased by 4.5% and you can delete the value you entered in the cell in step 1.

3. ## Re: Auto Calculation based on cell? (2002 SP3)

You could use a macro to do this. I'd call it from a command button.

Sub UpdateEm()
Dim lngRow As Long
Dim lngMaxRow As Long
Dim dblFactor As Double
dblFactor = 1 + Range("I5")
lngMaxRow = Range("B65536").End(xlUp).Row
For lngRow = 8 To lngMaxRow
If UCase(Range("I" & lngRow)) = "Y" And _
Not Range("G" & lngRow) = "" Then
Range("G" & lngRow) = dblFactor * Range("G" & lngRow)
End If
Next lngRow
' Optional - clear all "y"s to prevent double updating
Range("I8:I" & lngMaxRow).ClearContents
End Sub

See attached version. (I used a command button from the Forms toolbar)

4. ## Re: Auto Calculation based on cell? (2002 SP3)

Perfect! You solved it as usual. Thank YOU much!

Pooja

5. ## Re: Auto Calculation based on cell? (2002 SP3)

I suggest that you are taking an approach that will be error prone; in that the user will inadvertently select a cell and change the price when not wanting to. This will give rise to a second macro to undo the error. My recommendation:

<UL><LI>On a sheet by sheet basis place a "y", "r", or leave blank the cells in column I
<LI> A single macro will operate as follows on each cell in column H
<LI>If cell In is blank no action on cell Hn
<LI>If cell In is "y" and cell Hn is not filled with the same colour as the CPI Rate; add the CPI increment to cell Hn, and fill cell Hn with the same colour as the CPI rate.
<LI>If cell In is "y" and cell Hn is not filled with the same colour as the CPI Rate; take no action on cell Hn
<LI>If cell In is "r" and cell Hn is filled with the same colour as the CPI Rate; remove the CPI increment from cell Hn, and remove fill colour from cell Hn
<LI>If cell In is "r" and cell Hn is not filled with the same colour as the CPI Rate; take no action on cell Hn[/list]Let us know if this will satisfy your needs. If so a simple macro can be developed to implement the scheme.

Also:<UL><LI>Will the CPI index show up on each sheet, or just one?
<LI>Will the CPI index vary in value from sheet to sheet?
<LI>Will each sheet have the same data type assigned to any column? That is, will we be discussing columns H and I on all sheets?
<LI>Will you want any indication of when a given cell was last updated by the CPI?
<LI>Will you want any indication of the value that was last applied to a given cell when updated by the CPI?[/list]

6. ## Re: Auto Calculation based on cell? (2002 SP3)

That would EXCEED what they want done and they'd love it! Thank you.

Will the CPI index show up on each sheet, or just one? - I would actually have them show up only on ONE sheet which would be hidden.
Will the CPI index vary in value from sheet to sheet? - Yes, between three values.
Will each sheet have the same data type assigned to any column? That is, will we be discussing columns H and I on all sheets? - No, they actually vary depending on the sheet. For example, on one the fees are reflected on Column B, on another they're in Column E.
Will you want any indication of when a given cell was last updated by the CPI? - Yes, please.
Will you want any indication of the value that was last applied to a given cell when updated by the CPI? - It would be helpful to see but is not completely necessary. We would still have the old workbook to compare against.

Thanks again!

7. ## Re: Auto Calculation based on cell? (2002 SP3)

Hi pooja

My assumptions are provided below. Please either concur, correct, or provide the appropriate information.
1. <LI>Column H would not be hidden, and my previous post should have referred to columns G and I.
<LI>All visible sheets will have data subject to processing.
<LI>Only one sheet will be hidden (that containing the CPIs)
<LI>How will I determine which CPI is applied to a given sheet?
<LI>Each sheet requiring processing will contain "Amount of Bail" at the head of the Bail column (G7 in the example you provided.
<LI>Each sheet requiring processing will contain "Update Y/N" at the head of the Update Control column (I7in the example you provided
<LI>Columns to the right of the update control column will not be used by the end user. (I'm intending to store some data there)
<LI>The colour representing the current update will cycle through three or four colours
<LI>The colour representing the current update will be displayed on the cell identifying the last update
There may be other points of clarification once we go a little further.
I strongly recommend that we get rid of the "Merged Cell" feature wherever it appears. If this "Heading" information is contained within the same cells on all sheets, we can reformat it with a macro.

8. ## Re: Auto Calculation based on cell? (2002 SP3)

Hello Don,

1. Column H would not be hidden, and my previous post should have referred to columns G and I. - Concur
2. All visible sheets will have data subject to processing. - Concur
3. Only one sheet will be hidden (that containing the CPIs) - Concur
4. How will I determine which CPI is applied to a given sheet? - If we can have a default value of 2.17% and then input the others, that would be great. Otherwise, I can send you the giant workbook and specify which sheet has what.
5. Each sheet requiring processing will contain "Amount of Bail" at the head of the Bail column (G7 in the example you provided. - It would not. There are various headings. For example: Trash Collection, Building Permit, etc
6. Each sheet requiring processing will contain "Update Y/N" at the head of the Update Control column (I7in the example you provided - Concur
7. Columns to the right of the update control column will not be used by the end user. (I'm intending to store some data there) - Concur
8. The colour representing the current update will cycle through three or four colours - Concur
9. The colour representing the current update will be displayed on the cell identifying the last update - Concur

There may be other points of clarification once we go a little further. - Whatever will work. Thank you much for helping me out!
I strongly recommend that we get rid of the "Merged Cell" feature wherever it appears. If this "Heading" information is contained within the same cells on all sheets, we can reformat it with a macro. - Works for me!

pooja

9. ## Re: Auto Calculation based on cell? (2002 SP3)

Send the file; and I will treat it like a crossword puzzle--amuse myself with it--over the weekend.

Place the CPI mapping information on the hidden sheet if you can.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•