Excel Spreadsheet...Complicated formulas!?
How would I create/enter this formula?
I have 5 rows on the top of my spreadsheet. I have 10 blank rows below the 5. The formula I'm trying to create is once information is typed into those 10 blank rows, for the top 5 rows to be updated when specific information is entered. (i.e. if the blank row has inputted the date 12/16/2006, I want one of the 5 rows to start counting how many rows have that date typed in. Also, I want to start adding dollar amounts together if they are within a specific date range.) How do I begin doing this?
More Questions From This User See All
Answers & Comments
Verified answer
All you need is putting the formula in these top 5 rows and make them look like they are empty when there is no info in the 10 rows.
Now, I assume that you want to type the date in a column, and the money amount in another one.
If so, it is easy, use the famouse IF function
=IF( SUM( A6:A16)=0, "", OFFSET( A5, COUNT( A6:A16), +5))
This is just a sample to show the last cell in column A if it is not empty, and show empty string if they are all empty.
Mail me here in Y! Answers for more info
Enjoy my profile, I am the VBAXLMan
The only way to accomplish this is through VBA code. The code would be triggered by any cell change. This code will look for the cells that were changed to be within the 10 blank rows and if so, do the update that you refer to.
This code must reside within the sheet module and must have the following heading to detect a cell change - the range refers to the cell that was just changed. VBA Help provides good examples on how to code this.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
If you put the formula in a1 and different dollar amounts in the 10 rows below them than this will work for you. In box a1 type in =sum(a2:11) and hit enter and type some numbers in the boxes below and you will see the top box update automatically.
You need to post the formula and explain the exact steps you took. Just in case: When you want to edit a cell, you can select the cell and press F2 to get into edit mode. After you're in edit mode, the arrows will take you around within the cell. If you want to insert another cell reference into your formula using the arrow keys, press F2 again and then the arrows will move the selection and put the reference where the cursor was. Each time you press F2, you will switch from edit mode, to cell-reference-insert mode. If you have some of your formula selected when you move the selector in cell-ref-insert mode, you will replace whatever was selected.