Formatting

[WLMPilot]

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

 

[J Sedoff]

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.

 

[Dave Peterson]

For the first expression:

=I373 / indirect(text(a369,"mmm")&"Exp")

 

[J Sedoff]

Les, Dave has a much easier approach.. I was trying to do that in Excel but
forgot the Indirect function (I was confusing it with the Cell function)...

You might want some form of an absolute reference for your date
text($A369,"mmm") if your dates are always in the A column.

 

[WLMPilot]

Thanks for trying. Dave's formula was just what I needed.

Les

 

[WLMPilot]

Thanks! Exactly what I need.

Les

For the first expression:

=I373 / indirect(text(a369,"mmm")&"Exp")