Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
expanding custom formatting without removing existing cell formatting? | Excel Worksheet Functions |