Add Number 1 In Adjoining Column When Existing Column Has Amount Highlighted In Green Or Red

Dec 27, 2012

I have a spreadsheet with sales which I colour green when they are outstanding and red when they are despatched. I would like to automatically add a 1 in an adjoining column when the cell is green (eg outstanding) or zero when they are despatched so that it totals up the number of outstanding orders.

a b

172.95 (this column would be green)
024.00 (this cell would be red)
124.00 (this cell would be green)
132.25 (this cell would be green)

View 3 Replies


Find Column "Amount". Insert Column Next To Amount

Feb 22, 2008

I need some code to do the following.

Look at worksheet 1. Find column "Amount". Insert column next to amount.
For all highlighted cells in column "amount" insert text "R" in adjacent (inserted) column.

View 9 Replies View Related

VBA Automatic Default Colour Change To Red Or Green On Existing Workbook?

Mar 7, 2014

I am looking for a vba where by the default colour of the font for new text typed in any cell of a workbook change to green or red and the default black automatic becomes secondary. The existing text in the workbook in its existing font colour must not change i.e stays in black or red.

View 1 Replies View Related

Column To Take Out Percentage Of Column A Up To Set Amount?

Nov 8, 2013

column to take out a percentage of column A up to a set amount. So B takes out 100% up to $40 then C takes out 100% up to $20 etc.

how I can get my spread sheet do accomplish this.

View 8 Replies View Related

How To Get Dates Within A Column On A Table Highlighted Depending On Value

Oct 31, 2013

I'm trying to get dates within a column on a table highlighted depending on their value.

I know how to do this using today's date, but I am having issues in using a separate set date as the parameter.

For example;

I want all values in a table in =K7:K72 that are more than 30 days older than a value set in A3 to be highlighted red.

This should be quite easy, but every time I enter anything the whole column changes colour when this is incorrect.

View 1 Replies View Related

Sum The Sales Revenue Column Of The Attached Per Month Into The Yellow Highlighted Cell @ The Bottom

Sep 30, 2009

is there a formula i can use to sum the sales revinue column of the attached per month into the yellow highlighted cell @ the bottom?

View 2 Replies View Related

VBA - Generate Random Column Values From Existing Column Values?

Aug 22, 2013


I have Column C and D filled with numeric temperature and humidity data. I need next 16 rows (8 more set) to be filled with randomly generated dummy data by the values exist in C and D. Same numbers but randomly distributed.

View 2 Replies View Related

Add Amount In Column

Apr 1, 2009

Trying to add a formula in my spreadsheet that will add every amount in column AQ8:AQ200 if the date in AQ8:AQ200 is march.

The format for the date is 3-12-09.

Here is what I'm trying.


View 6 Replies View Related

Adding Row And Column Amount

Jan 29, 2014

I need a formulas to add the row & column amount , see the attached example sheet.

Row and Colums.xlsx‎

View 1 Replies View Related

Cell Value In A Column Less Than Specified Amount

Sep 29, 2009

I just need a warning box that indicates a user has entered a cell value under 200. Here's what I have but it's not working.

View 2 Replies View Related

VBA To Round Amount In Column C

Sep 16, 2013

I thought it would be simple code. I have column C with amount such as

C1 : 3712.76
C2 : 277.91

When VBA runs, i want value to be rounded to display 3712 and 278 in C1 and C2 respectively.

Sub RoundC()
Dim N As Long, v As Variant, I As Long
Dim wf As WorksheetFunction
Set wf = Application.WorksheetFunction
N = Cells(Rows.Count, "C").End(xlUp).Row
For I = 1 To N
v = Cells(I, "C").Value
If IsNumeric(v) Then
Cells(I, "C").Value = wf.Round(v, 0)
End If
Next End Sub

This code working, but it end up value to 3712.00 instead of 3712 only. I do not want cell to display additional zero's.

what I am making error here?

View 3 Replies View Related

Minif(s) - Lowest Amount In One Column

May 21, 2014

I'm trying to pull the lowest amount in one column if the values in 3 separate other columns agree.

View 3 Replies View Related

If Currency Amount Is Less Than $500,000 In Column I, Delete The Whole Row

Feb 16, 2009

If Currency amount is less than $500,000 in column I, delete the whole row. I would like a formula to do the following...

View 2 Replies View Related

Count The Amount Of Cells In A Column

Jun 10, 2009

I am trying to count the amount of cells in a column (Col H) containing data against a certain date which is displayed in another column (Col B)

Col B................-...........Col H
13/06/09.................FIRST LAST
13/06/09.................FIRST LAST
20/06/09.................FIRST LAST

So that in the sheet next to it it would appear:

13/06/09 - 2
20/06/09 - 1

The dates don't appear in row order and the sheet has a filter on it so I am unable to select the Col H and just use COUNTA

View 11 Replies View Related

IF Priority Value Changes Then Move $ Amount To A Different Column

Oct 18, 2008

If the value in Column H changes then move the value from it's original location which would be either in J, K or L from there to the column corresponding with the value in H (reference 1=J, 2=K, 3=L).

So, in the file, if H3 were to be changed to Priority 3 then I want the value to move from J3 to L3 and if it were to be changed to Priority 2 then I want the value to move from J3 to K3. The formula needs to work on all variations ie priority 2 changing to 1 or 3 for the whole sheet. There is conditional formatting - would be nice to keep it but no big deal.

View 5 Replies View Related

IF Statement: Use The First Amount In Column P That Is Positive?

Apr 6, 2009

My Column P (Payback) has amounts ranging from negative to positive. In one of my calculations, I would like to use the first amount in Column P that is positive.
Depending upon scenario, sometimes this could be P4 other times it might be P17

View 3 Replies View Related

Count If Value Is Over Certain Amount And Another Column Contains Certain Text?

May 6, 2014

I have a worksheet where

Column D lists a service offered by a business, such as IMC, TC, Respite, etc

Column M lists how long the customer has used this service for in weeks, such as 4,5,3.5,6 etc

What I would like to do in another column is to count the number of clients who have used the service for 6 weeks or more broken down by the service type i.e. Count how many people have used IMC for 6 weeks or more.

If possible I would also like to be able to count those who have used a service for between 5.1. and 5.9 weeks too, again by service i.e. Count how many people have used IMC for between 5.1. and 5.9 weeks.

View 2 Replies View Related

How To Add Values For Amount That Are Higher Than Same Row In Another Column

Jan 21, 2010

I have two columns (let's say column A and C), i would like to add the values in column A only if they are higher than the ones in Column C in the same row... how could i do that?

The sheet looks something like this, i would like to add the values in the first column only if they are higher than the ones in the third column and then of course get the sum of them... i would also like to know how many were there in total, but i guess i could do that with a count formula i just need the parameters

$ 67,100.00 $ 110,000.00 $ 165,100.00 $ 146,034.00 $ 239,400.00 $ 220,100.00 $ 90,184.69 $ 147,843.75 $ 168,500.00 $ 51,011.25 $ 83,625.00 $ 95,100.00 $ 89,860.32 $ 147,312.00 $ 198,200.00 $ 159,121.36 $ 260,854.69 $ 26,000.00 $ 129,941.44 $ 213,018.75 $ 235,700.00 $ 88,869.38 $ 145,687.50 $ 95,600.00 $ 27,772.54 $ 45,528.75 $ 43,000.00

View 9 Replies View Related

Formula To Add Amount Of Rows In A Column

Aug 4, 2006

I know a bit about excel nut not a hugh amount! i need to know a formula that adds the number of rows used in a column. Does that make sense?? ok


i need a formula that will tell me there is 6 rows used in that column (not including the title).

View 3 Replies View Related

Formula To Add Amount Of Rows Used In A Column

Aug 4, 2006

I know a bit about excel nut not a hugh amount! i need to know a formula that adds the number of rows used in a column.


i need a formula that will tell me there is 6 rows used in that column (not including the title).

View 3 Replies View Related

Inserting Cells Into Existing Column?

May 2, 2013

Suppose I have done a spreadsheet of,say 100 entries in alphabetical order in a column and I discover that I have missed 6. Is it possible to move the existing part from where I missed below this to make room for more entries without losing any of the entries?

View 3 Replies View Related

Autoshape Resizes According To Amount Of Data In Column

Feb 14, 2009

I type in a "O" into the little grid I made and then the locations are placed into cells A34, A35, A36 and so on. The arrow I placed into column B. It works if I manually enter anything into A34,A35,A36 and it stays with the data. If I let the grid automatically populate A34, A35 and so on, it will not grow as the cells in A34, A35 are automatically populated. How can I make it do that one little thing? Sorry if I am confusing.

View 11 Replies View Related

Sorting Amount Column In Excel Sheet

Jul 23, 2013

I have excel sheet which is having following format

sr no | bank | branch | chk no | amount | party | bill no
1 | combobox1 | combobox1 | 123456 | 12000 | combobox1 | 123456
2 | combobox2 | combobox2 | 12346 | 2000 | combobox2 | 123456
3 | combobox3 | combobox3 | 12345 | 900 | combobox3 | 123456
4 | combobox4 | combobox4 | 13456 | 7000 | combobox4 | 123456
5 | combobox5 | combobox5 | 3456 | 18000 | combobox5 | 123456

When i try to sort amount column it need to sort and change with corresponding row but it is not happening so what should i need to do for that ?

View 2 Replies View Related

Counting Amount Of Colored Cells In Column?

May 7, 2008

I have a column (L18 - L32) which consist of 15 cells.6 of these cells are colored(different colors) and the rest are blank.Im looking for a formula which will be able to count the amount of colored cells in my column.

View 9 Replies View Related

Autofilling Rows In A Column Using Existing Row Data

Mar 9, 2009

I have a column with blank rows inbetween rows with data.

Column Heading

Is it possible to autofill the blank rows with the row that has a value above them, have it skip the next row with a value, recognize the next blank cells and fill them in with the previously skipped row, and so on down the line? How might I go about doing that?

View 4 Replies View Related

Count Amount Of Times / Show Up In Column And Sum It At The Bottom

Apr 29, 2012

I am trying to count the amount of times 4, 5, and "11-5" show up in a column and sum it at the bottom. I tried


As an array and keep getting N/A. So im not sure where my problem is.

View 6 Replies View Related

Count The Amount Of Entries Based On The Date In A Column

Jan 17, 2009

I have a spreadsheet containing 10,000 + entries.

Each Entry is Dated within Column D2:D10786 in this format - 1-Nov-08 (example).

Lets say i have a cell on another sheet Cell A1 and in this Cell i want it to Count how many Cells contain the dates from Nov-08 in my Date column..

View 9 Replies View Related

Amending Existing Code To Add New Column To Macro Output

Jun 4, 2014

Attached is my sample data Sample Data_Combine Sheets.xlsm.

What the code in the attached sheet does is that it combines the data in Sheets Set1 and Set2 into the worksheet named "Combine".

Both input sheets share same columns. Code I use is as below:

Sub BuildPivotData()
Dim wrsht As Variant
Dim i As Integer

wrsht = [{"Set1", "Set2"}]
For i = 1 To UBound(wrsht)
Sheets(wrsht(i)).[a2].CurrentRegion.Offset(1).Copy Sheets("Combine").Range("A65536").End(xlUp)(2)
Next i
End Sub

I want to amend the output in such a way that when the macro is run , in the "Combine" worksheet an additional column is added at the start(col A) with header as "Category".

The category should be nothing but the input Sheet name i.e Set1 or Set2.

Here is an example ... Output Sheet.xlsx.

View 3 Replies View Related

Automatically Generating New Column To Existing Excel File?

Nov 20, 2013

Suppose there is an existing ms excel file. There are some data inside, taking 5 columns. Here is what I want to do:

I want the excel file to automatically generate a new column, the 6th, when the file is opened, say double-clicked.I want the 6th column do some summation based on the indexing/flag information int he 4th column, and the values from 5th column. For example: Suppose 4th column has binary entries (1 or 0). If the entry is 1, then pick the entry in the same row in 5th column, and do the summation for all this kind of values (values in 5th column with a 1 flag in 4th column)

View 2 Replies View Related

Generate A Random List In A Column That Adds Up To A Fixed Amount

Aug 17, 2008

i had a basic spreadsheet for my incomings and i got wiped with my hd exploding!! i know how much the total was i just need a way of creating a list of random amounts within a specified range over a year to give to my accountant.

View 13 Replies View Related

Copyrights 2005-15, All rights reserved