Formatting
Thanks for trying. Dave's formula was just what I needed.
Les
"J Sedoff" wrote:
I couldn't figure out how to do it directly in Excel, but you can create a
VBA function. This creates absolute references to those cells.
If you wanted it dynamic, you would have to change the thisMonth = ... line
so that it did not reference Range("A369") explicitly, but rather wherever
you are storing each month's date. Furthermore, the references to each range
(ie: Range("I373"), Range("I375")) would need to change to reflect where you
are in the sheet.
Sub ConvTxtToRef()
Dim thisMonth, myCell As String
'Takes the month's three letter abbr
thisMonth = Format(Range("A369").Value, "mmm")
'myCell = "JULExp" for July, "APRExp" for April, etc
myCell = thisMonth & "Exp"
'= Expense Amount / JULEXP
Range("I374").Formula = "=" & Range("I373").Address & _
"/" & Range(myCell).Address
'myCell = "JULNetInc" for July, "APRNetInc" for April, etc
myCell = thisMonth & "NetInc"
'= Expense Amount / JULNetInc
Range("I375").Formula = "=" & Range("I373").Address & _
"/" & Range(myCell).Address
'myCell = "JULGrInc" for July, "APRGrInc" for April, etc
myCell = thisMonth & "GrInc"
'= Expense Amount / JULGrInc
Range("I376").Formula = "=" & Range("I373").Address & _
"/" & Range(myCell).Address
End Sub
I hope this helped!
Jim
PS: if you are having trouble with making it dynamic, just reply to this
post and I'll see what I can do.
"WLMPilot" wrote:
Within the budget worksheet for each month, I compare individual expense
against total expense to get a percentage. There are also two other cells
that contain totals that each expense is compared against.
I name those cells using the first three letters of each month: JULExp,
JULNetInc, and JULGrInc.
The upper-left cell of each budget has the date of the first day of each
month entered, but the display is mmm-yy, ie Jul-08.
I would like the formulas I use to pull the first three letters of the month
from that date cell.
EXAMPLE:
A369 = 7/1/08 -- displayed as Jul-08
I373 = Expense Amount
Formula in I374: I373 / JULExp
Formula in I375: I373 / JULNetInc
Formula in I376: I373 / JULGrInc
Thanks,
Les
|