Formula in Excel adjusted automatically

Microsoft Excel 2003 (full product)
December 18, 2009 at 06:45:02
Specs: Windows XP
If I have a column of number. And a few cells below the last cell with value will calculate 90% (the last cell *0.9) of the last cell. From time to time I will insert a few more rows and add some new values. How can I be sure that the cell with the formula will automatically adjust to calculate the new last cell with value?

Note: The number of empty cells in between the last cell and the cell with formula could vary.




After insertion:




See More: Formula in Excel adjusted automatically

Report •

December 18, 2009 at 08:02:00
One way is with a VBA WorksheetChange macro.

Let's say your data starts in A1. Let's also assume that there are no blanks within the range that contains the data, as per your example.

Try this:

Pick the cell that current holds your *.9 formula.

In the Name Box above Column A, enter myCell and hit enter.

Right Click the sheet tab and choose View Code.

Paste the code below into the pane that opens.

Whenever there is a change in Column 1 (A) it will start in A1 and find the first blank cell. It will then place =.9*[the address of the cell above the blank] into the cell you named myCell. Since the reference for the cell named myCell will change as the number of cells above it changes, the formula should update to follow the last piece of data in the range.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 1 Then
   For numCells = 1 To Range("A:A").Rows.Count
    If Range("A" & numCells) = "" Then
     lastCell = numCells - 1
     Exit For
    End If
  Range("myCell").Formula = "=.9*" & Range("A" & lastCell).Address
 End If
End Sub

Report •

December 18, 2009 at 10:14:58
This is exactly what I'm looking for except for one more little problem. I have more than 1 column that needs that. Right now, I need this for Col. C, D, E, F, G, I & K. Is there a easy way to apply this to more than one column?


Report •

December 18, 2009 at 10:43:57
I've come up with some non-VBA ways to do what you want, but the list has to start in row 1.

If the list starts in a different row, finding the ever-changing last cell in the list becomes a little more challenging.

Let me know...

Report •

Related Solutions

December 18, 2009 at 11:00:24
It actually starts in row 11 and it has value up (down) to row 27 at the moment. The formulas are on row 36 (for the time being). And this applies to col. C, D, E, F, G, I & K.

HTH and thanks.

Report •

December 18, 2009 at 11:02:35
Will all of your columns contain the same number of entries or will the length vary?

Do they all start in Row 1?

I guess I need a little more detail as the layout of the data in order to provide an efficient solution.

Report •

December 18, 2009 at 11:23:37
From row 16 and down is where they all (columns) started having data. I don' think where it starts is important. All I need is the formula to calculate 90% the value of the last cell that has data (a few rows above the formula cell) in each column. When I insert new rows, there will be new values entered into a row across all the columns. There won't be any blanks. HTH.

Report •

December 18, 2009 at 11:43:34
re" I don't think where it starts is important"

You may not think it's important, but I do.

There are various methods of finding the ever-changing last cell in a range and depending on where the data starts helps determine which of those methods will work best.

I assume that all of the data also ends on the same row. Is that correct?

Report •

December 18, 2009 at 12:07:48
Yes, they all end on the same row.

Report •

December 18, 2009 at 12:47:21
I'm going to work on a formula based solution later, but for now, try this if you want.

Name the cells that contain the *.9 formula based on the column each one resides in.

e.g. for the code below to work, use myCell3 in Column C, myCell7 in Column G, etc.

Then try this code:

Private Sub Worksheet_Change(ByVal Target As Range)
 On Error GoTo Done
'Turn off events so they don't fire while the code is running
  Application.EnableEvents = False
'Determine if change was in Columns C - K
    If Not Intersect(Target, Columns("C:K")) Is Nothing Then
'Find row number of first blank cell in column
     For numCells = 16 To Columns("C").Rows.Count
        If Cells(numCells, 3) = "" Then
            lastCell = numCells - 1
            Exit For
        End If
'Build Range Name and place formulae in Named cell
     For myNum = 3 To 11
'Skip Columns H and J
      If myNum = 8 Or myNum = 10 Then GoTo noName
         Range("myCell" & myNum).Formula = _
             "=.9*" & Cells(lastCell, myNum).Address
    End If
'Turn events back on
 Application.EnableEvents = True
End Sub

Keep the following code available. If the code above halts after Events are disabled and never gets to the Application.EnableEvents = True line, it will won't fire the next time you make a change to the worksheet. This often happens while you are setting up and testing new code.

By running the short piece of code below, you'll be able to enable events and get back on track.

Sub ResetEvents()
  Application.EnableEvents = True
End Sub

Report •

December 18, 2009 at 13:19:18
What can I say…it's awesome. It's exactly what I've been looking for. I'll keep the extra code just in case.

Anyway, I can't thank you enough for all your help. I do appreciate it.

Report •

December 18, 2009 at 15:58:45
Another question:

Even though you aren't interested in Columns H and J, are the cells in those columns filled with data, just like the C, D, E, etc? At least 1 blank cell below them, just like the others?

If so, I think I have fairly easy formula based solution.

If not, I think I have a doable formula based solution.

Let me know.

Report •

December 21, 2009 at 06:15:52
Col. H & J happens to be the sub-total across the rows. But I do not need to have a formula cell (to calculate the discount) below them like the other columns. They all do have at least 1 blank cells below them.

Report •

December 21, 2009 at 08:26:04
I know you don't need the formula at the bottom of H & J but the reason I asked whether those columns would always be "full" - exactly like the other columns - is because I want to use all of the data in a COUNTA function.

If there will be empty cells in H or J between Row 16 and the blank space at the bottom, then it will throw the count off and make the solution just a bit more manual.

Let me know which one of these you have:

This is perfect:

	F	G	H	I	J
16	3	6	7	7	6
17	5	7	6	6	8
18	6	6	5	5	7
19	7	5	4	4	6

This is just OK (note the blanks in H & J)

	F	G	H	I	J
16	3	6	7	7	
17	5	7		6	8
18	6	6	5	5	7
19	7	5		4	6

Report •

December 21, 2009 at 10:17:28
Sorry I was tied up with a project and couldn't respond as soon as I'd like to. Anyway there won't be any empty cells across any rows under col. C-K. There will be empty cells (under col. H & J) only on the row where the formulas are.

I tried to copy and paste portion of the sheet but it's too wide. So hopefully I've explained it clear enough.

Report •

December 21, 2009 at 10:43:15
Try this:

Select your data table, from C16:K?? where Row ?? is the first blank row below the data. You need to have the blank row selected.

Enter a name for this range in the Name Box. I used myData for the formula below.

In Column C, in the cell where you want the formula, paste this:


Drag this across to Column K and then delete the formula in H and J.

When you insert new rows into the table, they have to go below Row 16, but above the first Blank Row. Selecting the Blank Row and using Insert Copied Cells should work fine.

Here's what's happening:

- The Named Ranged (myData) includes the Blank Row.

- If you insert rows inside the Named Range, they become part of the range.

- COUNTA(myData) counts all of the cells that contain data in myData.

- COUNTA(myData)/9 tells us how many rows are in myData since there are 9 columns. (The total number of pieces of data in a completely populated range divided by the number of columns gives us the number of rows. That is why I needed to know if every cell in the range contained data. If there are any blank cells in the range - other than the bottom row - the count will be wrong)

- COUNTA(myData)/9)-1 give us the number of rows to use in the OFFSET function.

- OFFSET(C16,(COUNTA(myData)/9)-1,0) gives us the last cell that contains data in myData.

- OFFSET(C16,(COUNTA(myData)/9)-1,0)*.9 multiplies that cell by .9


Report •

December 21, 2009 at 11:29:32
It works fine in regards to inserting rows. But it will not calculate properly when I enter new data. It seems to ignore the new data.?? I wish I could attach the file or paste portion of it to show you but without any luck.

Report •

December 21, 2009 at 12:15:40
It may appear to ignore the new data until all the cells above the blank row are full.

As I mentioned in my previous post, all of the cells above the blank row in all of the columns have to have data in them in order for the formula to work correctly. If there are any blank cells within the Named Range the COUNTA function will not return the correct number of rows.

If you can't live with that, I'll send you an email address via PM to which you can send the sheet and I'll see what I can do.

I will add that I'm winding down for the holidays and will be spending more time with family and friends than with this forum. Your spreadsheet probably won't be at the top of my holiday activities list. I'll work on it as time allows.

Report •

December 21, 2009 at 12:33:03
The one you sent last Friday (Response #9) is working fine. You don't need to spend any more time on this. I wouldn't want to take you away from your family during the holidays. I do appreciate all your effort.

Thanks again and Have a Merry Christmas!

Report •

December 21, 2009 at 12:43:02
Happy Holidays to you and yours - either now, or whenever your particular holidays may be. ;-)

Report •

Ask Question