Formatting
 

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

Formatting
 

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

Formatting
 

For the first expression:

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



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

--

Dave Peterson

Formatting
 

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.

"Dave Peterson" wrote:

For the first expression:

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



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

--

Dave Peterson

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

Formatting
 

Thanks! Exactly what I need.

Les

"Dave Peterson" wrote:

For the first expression:

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



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

--

Dave Peterson