I don't think that what you want can be done with a formula. However, it's
quite simple with a sub in VBA. I've included code below for a sub named
'BackPoster.'
If you want to use it do the following:
Open the VBA editor - press <Alt> and <F11> simultaneously while in Excel.
Find your project in the 'Project - VBA Project' box.
Click on your project name to select it.
Click <Insert> on the main menu at the top.
On the sub menu click 'Module. This will add 'Module1' where you will paste
the code mentioned above.
Paste the 'BackPoster' code in the module. Lines starting with a single
quote ( ' ) are comments within BackPoster's code and are placed there in
case you are interested. Comments will normally be in a green color unless
you changed it on your computer.
Return to Excel by clicking the Excel icon in the tool bar near the top of
the screen.
To use BackPoster
Select a cell any place on any worksheet.
On Excel's main menu click Tools > Macro > Macros > BackPoster.
BackPoster will move up 9 rows and left 8 columns then will find the first
empty cell in the current column where it will post your system's date. It
will then move right 1 cell and post the work 'Apple.' It will then move
right 5 additional cells and post "30" as text. It will then return the
focus to the cell where you started.
If you want to post a word other than 'Apple,' go to the code, find "Apple"
and change it to what you want. Make sure it's in double quotes.
If you want "30" posted as a number instead of text, go to the code and find
the following line:
Selection.NumberFormat = "@"
Place a single quote in front of it, thus: 'Selection.NumberFormat = "@"
BackPoster has row and column validation in it. It informs you if, when you
try to use it, you are too close to the top of the sheet or too close to
the left side. In either case it will not let you continue and will end the
sub.
BackPoster has error handling. It exits gracefully if you run out of room in
a column or experience an error I haven't anticipated.
Sub BackPoster()
Dim CurRng As String 'Holds address for starting cell
'Error handler below to end this sub gracefully if an error is encountered.
On Error GoTo ErrorHandler
'Get starting cell address
CurRng = ActiveCell.Address
'Make sure at least 9 rows above starting place
If ActiveCell.Row < 9 Then
MsgBox "Not enough rows above.", vbOKOnly + vbInformation, "To close to
top ..."
Exit Sub
End If
'Make sure at least 8 columns to left of starting place
If ActiveCell.Column < 8 Then
MsgBox "Not enough columns to left.", vbOKOnly + vbInformation, "To close
to left side ..."
Exit Sub
End If
'Turn off screen updating to speed up large worksheets
'Show wait cursor
Application.ScreenUpdating = False
Application.Cursor = xlWait
'Move up 9 rows, left 8 columns
ActiveCell.Offset(-9, -8).Select
'Find first empty cell in the current column starting
'at range selected above. Stop on first empty cell.
Do While ActiveCell.Text <> ""
ActiveCell.Offset(1, 0).Select
Loop
'Enter today's date per system date
ActiveCell.Value = Date
'Enter text in first column to right of date
ActiveCell.Offset(0, 1).Value = "Apples"
'Move 5 cells to the right, select
ActiveCell.Offset(0, 5).Select
'Format cell as text
Selection.NumberFormat = "@"
'Enter numerals as text
Selection.Value = 30
'Return cursor to starting cell
Range(CurRng).Select
'Restore screen updating and default cursor
Application.ScreenUpdating = True
Application.Cursor = xlDefault
Exit Sub
ErrorHandler:
Select Case Err.Number
Case Is = 1004 'App defined error. Occurs if run off bottom of sheet
MsgBox "All cells in the current column are full.", vbOKOnly +
vbCritical, "An error has occurred ..."
Range(CurRng).Select
Case Else 'Unexpected errors
msg = "Error number: " & Err.Number & vbCrLf
msg = msg & "Description: " & Err.Description & vbCrLf
MsgBox msg, vbOKOnly + vbCritical, "An error has occurred ..."
End Select
'Restore screen updating and default cursor if error encountered
Application.ScreenUpdating = True
Application.Cursor = xlDefault
End Sub
.