# Allocating Capital Expenditure Across Months

May 19, 2009

I have project's start date and end date and total cost. I want to spread the total cost across month on top using the total number of days[calculated using the end and start date] that project will run and the number of days that project is in that month. I have highlighted the area in yellow where I want formulas to work.

## Assistance With Budget Expenditure Forecasting

Jun 19, 2008

I am trying to use excel to forecast budget expenditure for the fiscal year, using actuals from months past + unknown (future months).

I have this formula in cell G2: =IF((worksheet A!G70>0),F2,IF((B2
>D2),(B2-C2)*F6+F2,IF((B2

## Balance Sheet: Take The Value In The First Cell Of The Expenditure Column And Subtract

Jul 13, 2009

I have is a cell with a begining balance and a cell witha current balance.

I also have a column in which each expenditure will be placed ina cell in that column in sequential order.

What i want to do is take the value in the first cell of the expenditure column and subtract it from the begining balance and the place the result in the current balance.

But after the first cell i will need to subtract each value from its previous value to continue counting down so to speak.

I am at a loss on how to move the last value which will also be the lowest value in the column to the cueent balance cell.

## Allocating Costs Across A Row

Oct 27, 2008

I'm developing up a spreadsheet to conduct a feasibility on a 50 unit townhouse development and I was wondering if there was any way of allocating costs across the project without physically typing in the data in each month? (96 months in cashflow!)

ie: - In a row headed "Roads & Drains" can I allocate the say \$500,000 worth of works across the months by using a formula or other methods?

My current method is very time consuming when I want to conduct a sensitivity analysis,

## Allocating A Value To Different Time Periods

Feb 4, 2010

I got the following table.

http://img163.imageshack.us/img163/2972/oldnf.jpg

What formula could I use so that the amount under STP premium would display at the right cell under different time periods depending on the time of the trade date, just like the following images?

http://img687.imageshack.us/img687/5720/newcg.jpg

http://img168.imageshack.us/img168/413/new1zk.jpg

## Allocating And Carry Over Order Quantities

Jul 16, 2014

I have a sheet which is just a list of product codes and a sum of every order placed for each code. i.e.
Sheet2  AB1Prod codeOrdered2123100345650478960

then I have another sheet which is the orders placed by our customers. These orders are to go out in 6 periods each of 2 weeks.
For various reasons the amount ordered each time won't match the periods.

The sheet beforehand will look like

Sheet1  ABCDEFGHIJKLM1Prod codeP1 REQALLOCP2 REQALLOCP3 REQALLOCP4 REQALLOCP5 REQALLOCP6 REQALLOC212327 20 35 20 12 11 345633 40 50 15 25 11 478915 20 20 20 20 10

What I need to do is look up the quantity ordered and then allocate the ordered quantity to the periods carrying over the remainder to the next period and so with the table above the result would be

Sheet1  ABCDEFGHIJKLM1Prod codeP1 REQALLOCP2 REQALLOCP3 REQALLOCP4 REQALLOCP5 REQALLOCP6 REQALLOC2123272720203535201812 11 34563333401750 15 25 11 478915152020202020520 10

Both sheets are sorted by the product code ascending and it doesn't matter if it is a formula or VBA based solution as I am already using both.

The number of product codes is currently just over 400 but will grow to about 550 by the end of period 6.

## Allocating Employee Names With Unique Reference Number

Oct 31, 2008

I have been given a database with peoples names that require a unique number to hide/replace the name to hide their identity for a meeting and to act as a ghost system.

The problem i have is that some individuals names on the list appear more than once so i cannot simply call Joe Bloggs number 3 as he can appear further down the list at number 120.

I was wondering is there is a way to do this without manually replacing each name with a number and making and logging the persons name that corresponds to the number.

as there are 600 names and some can appear up to 4 - 5 times!

## Allocating To Display The Searched Item In Textbox Dynamically

Aug 13, 2014

I am new to VBA macros and trying to create display the searched items in the TEXT boxes for e.g

I have the data in one sheet as below.

Messgae CodeProgram NameError Description
asdsasdsqwewe
789ARU100Praveen Code
789ujiikluiooqwe

[Code]...

i have created one text box so that user enter the data to search by giving Message code. My concern is

From the example,

If the user enter 789 in the text box, and click on te search button( On user form) then it should dynamically create displays 2 occurance in each text box.i.e if the 789 found in 1 col then it should create 4 test boxesto display Program name data and 4 text boxes to display Error Description side by side. if not text boxes if will be if we display in lables also.

## Conditional Format - Dates Greater Than 18 Months And 24 Months?

Dec 6, 2013

I have a date column (Column E: Date Entered) on my spreadsheet that I need to set conditional formatting on. There are two conditions:

1) 18 months from the date in the cell needs to be highlighted yellow

2) 24 months from the date in the cell needs to be highlighted red

## Capital Letters

Aug 3, 2007

I have a validation drop down box type thing... when I select the drop down, the lower-most option is highlighted as a default. How do I make it so that the upper-most option is highlighted first?

## Mid Find First Capital Letter

Nov 1, 2009

I require a formula which finds the he first capital letter within a cell and returns the text, but I need it to ignore blank cells and the symbol *.

## Elapsed Months (many Months Have Gone By Since Todays Date)

Jul 19, 2009

Cell A1 is a past date. In cell B1 I would like how many months have gone by since todays date. eg. Cell A1 = July 07, B1 would = 24 months.

## Add Months & Return Decimal As Years & Months

Jul 31, 2007

I am looking for a formula that will add months and return the year. E.g. if I add 1.05 and 1.07 I should get 3.01. i.e. 3 years and 1 month.

## How To Use RIGHT Formula To Show Value Until Capital Letter

Dec 29, 2011

I was wondering whether it is possible to find a formula in Excel that would extract (using RIGHT formula) surname from Name Surname cell despite surname lenght i.e.:

'John Smith' in one cell would give 'Smith' in the other
'Tom Let' in one cell would give 'Let' in the other

I have tried to combine RIGHT, FIND and PROPER (for upper case) in one formula but it does not work.

## Userform Textbox Input As Capital

Jan 22, 2013

Whats the correct code to use on a userform text box which when you type in it, it is all in capital even if you have not got caps lock on.

## Insert Comma Before First Capital Letter In Cell

May 10, 2014

I have many lines of text and I wondered if there is a formula so I can insert a comma before the first capital letter of each line? A small amount of text is below

What i would like is there to be a comma before the first capital letter so it reads

Is this even possible?

## Using CountIf Function - Capital Depreciation Schedule

Feb 10, 2013

I am attempting to create a capital depreciation schedule that gives us our total operating impact for a given calendar year. I can populate my depreciation based on the purchase month, and fill across the schedule the monthly depreciation cost, however, I'm not sure how to stop the depreciation after the item has depreciated fully (say after 36 months).

For example, if someone were to select March 2013 in B5, monthly depreciation should begin in E5 and spread across ONLY 36 months. If they were to select April 2013, the depreciation would begin in F5 and extend ONLY 36 months.

I think it might be possible to do this with an COUNTIF function, but it is not working for me.

## Create Space Between Two Consecutive Capital Letters?

Jun 30, 2007

How to create space between two consecutive capital letters?

like i have....
RPerry
RCCox

i want...
R Perry
R C Cox

## Code Not Working With Input Capital Letter

Jan 24, 2014

I have code that I just noticed will not work if user inputs a capital S. I have tried a few things but I can not seem to get it to work. Here was the original code:

If Range("H13") "s" Then

I tired this:
If Range("H13") "s" or "S" Then

## Create Space Between Two Consecutive Capital Letters

Jun 30, 2007

How to create space between two consecutive capital letters?

like i have....
RPerry
RCCox

i want...
R Perry
R C Cox

## Conditional Formatting With Capital/small Letters

Jul 17, 2009

I just want to conditionally format some columns with different colours. The problem is that I have some cells that have the value 'a' and others that have 'A'. These should be different colours, but I can't seem to get Excel to detect between the two forms.

## Force Text To Capital/Upper Case

Aug 29, 2007

Is it possible to make a spreadsheet so that everything that is typed into it is in CAPITALS? I have a need for that for a spreadsheet that I am using at work, but I remembered that in the title box above it only capitlaizes the first letter. I thought if it can do it with the first letter can Excel do it with all of the letters.

## Using Countif To Count Only Capital Letter (D) And Lower Case (d)

Jun 27, 2014

I am trying to set up a worksheet that shows shifts using a Capital "D" and a lower case "d" (one is for a 12 hour shift, the other for only 6 hours). Is there any way to set up a Countif formula that makes the distinction?

Jan 3, 2008

Is there any way of writing a macro so that specific cells are formatted to start with a capital letter and end with a full stop. Can you also write a macro to automatically spell check specific cells?

## Convert First Letter Of String To Capital Without Altering Formatting

Nov 17, 2009

Convert the first letter of a string to a capital without altering the rest of the strings format.

example: the activecell contains the string a1st (lowercase a, superscript 1st)

how can i achieve this in VBA?

Additionally, is it also possible to convert all of a string to capitals except the superscript characters

## Sort Values Which Has Numeric, Spaces And Capital Letter Characters

Oct 8, 2009

I would like to sort words or data which has the First capital letter, words which has spaces and words which contains number... i have attached a sample file.

## Add Space And Full Stop Between Consecutive Small And Capital Letters In Excel?

Apr 13, 2014

I have 1000s of rows of data wherein there is no space or full stop once the sentence ends and a new one starts. For example,

Original text: Bob is a boyJanice is a girl.
What I want: Bob is a boy. Janice is a girl.

## Convert Lower Letter To Capital Letter Automatically

Mar 2, 2009

When I type a single lower case letter into a cell, what formula or conditional formatting should I use to always convert it to a capital letter automatically?

## Sum Of Last 3 And 6 Months?

Jun 6, 2014

get the formula for the sum of last 3 and 6 values in a row.

Attaching the sample workbook for your reference

## SUM For Months

May 4, 2007

I have a spreadsheet which works out how much per annum our company makes. The total figure is in D2.

What I need is to make E2 work out how much it is for the current month e.g. if it's September, it would work out the monthly profit for 30 days.