Insert Values Based On Other Cell Value Reference

Mar 21, 2014

The first three columns of a spread sheet we use if function to insert values to these cells based on the next three column values. We have already done it using IF function. However, the same function should happen through macro by referring to the column headers.

Example 1:
If in column E header (E1) ‘Contract_Status’ and E2 cell value is ‘FAIL TO PAY’ then A2 value should be ‘No Owner’, B2 value should be ‘Terminated’ and C2 value should be ‘FTP’

Example 2:
If in column E header (E1) ‘Contract_Status’ and E3 cell value is ‘TERMINATED’ then A3 value should be ‘No Owner’, B3 value should be ‘Terminated’ and C3 value should be ‘TERMINATED’

Example 3:
If in column E header (E1) ‘Contract_Status’ and E4 cell value is ‘EXPIRED’ AND column F header (F1) ‘Contract_Renewal_Status’ and F4 cell value is ‘Cancelled by Customer’ then A4 value should be ‘No Owner’, B4 value should be ‘Renewal Cancellation’ and C4 value should be ‘cancelled’

View 9 Replies


Insert Number Of Rows Based On Cell Values?

Feb 20, 2009

Column B contains geographical Areas. Column C contains a list of business departments.

North Env
North Env
North Ops
North Sales
North Sales
North Sales
South Env
South Maint
South Ops
South Sales
South Sales
South Sales


The values and number of these departments will vary. I want to insert lines to sum the totals at the bottom of each geographic area based on the number of different departments. So, for this example, for the North three lines would be inserted. For the South, 4 lines would be inserted.

View 4 Replies View Related

Macro To Insert Row Based On Differences In Cell Values

Jan 8, 2014

a macro/code to accomplish the following:

I would like to insert a row if:

Difference in values in Column B are more than 2 .... OR ...Difference in values in Column C are more than 2

Value 1
Value 2


In the above table, rows would be inserted after specimen A, B, D, and E.

View 3 Replies View Related

Insert Rows & Repeat Values Based On Corresponding Cell Value

Jul 7, 2009

I have a list of 130 names in column A. I have a number value between 0-10 in column B (next to the name). I need to insert the number of rows defined by the value in column B, below each row that I already have (if the value is 0, then the row needs to be deleted). The inserted rows have to be filled with the name value from the row above.

For example - before macro:

Joe Bloggs 2
Adam Wilson 10
Peter Andrews 0
Claire Burrows 6

After macro:

Joe Bloggs
Joe Bloggs
Adam Wilson
Adam Wilson
Adam Wilson
Adam Wilson....................................

View 6 Replies View Related

Insert Pictures Based On Cell Values From One Specific Folder

Mar 14, 2014

where i have a products list and respective pictures in a folder. What is should do is to get the relevant pictures appear on the screen when i select the relevant name.

View 1 Replies View Related

Insert Extra Rows On Data Based On Values From Another Cell?

Jun 12, 2014

I need to create extra rows of duplicate data. The number of duplicate rows depends on the number of semi-colons in the 15th column (column O).

Sheet 1 is the original working file. Sheet 2 is the result of how the executed macro for the first 2 lines of data. The first line in sheet 1 has 7 semi-colons, so there should be 7 rows of duplicate data created in sheet 2. The second line in sheet 1 has 6 semi-colons, so 6 duplicate lines, etc.

View 3 Replies View Related

Excel 2007 :: Macro To Insert Rows Based On Cell Values

Dec 19, 2013

a macro to insert rows based on certain cell values in column A.

I have uto 300 rows of data. Below is an example of column A.



If (above the R) is an 8, I need to insert 2 rows above that R and directly below the 8.

If (above the R) is a 9, I need to insert 1 row above that R below, directly below the 9.

(Below the R there is always a minimum of 8 digits with the 9 and 10 being random).

I have excel 2007

View 5 Replies View Related

Insert Formula Based Off Changing Reference Cells

Mar 6, 2014

i need a macro which puts the formulas into the cells as per attached.

the number of rows between the "beam" can be variable
the number next to the beam will be variable.
the number of "beam" rows is vaiable

my thoughts where to do a find"beam" and refernce the cells address the create formulas off those points but dont have the skill to code this

beam macro.xlsx

View 2 Replies View Related

Insert Row (Based On Values In Range) With Sum Of Values For Other Columns

Apr 21, 2014

I have different reports, some have fifty transactions, others have thousands. My goal is to: Insert a new row every time the values in the "Account" column meet a certain criteria, AND THEN add the totals for the Debit and Credit Columns.

Let's say I start with a table that looks like this:



[Code] ....

I want to group the first four rows because Accounts 33010 and 33015 are in the same department. Same with 50050 and 500060. I want to then insert a row below the last row with "33015" as its Account #. And add the values for Debit and Credit. It'd look like this:


[Code] .........

Honestly, I have tried everything. Running a Macros with Relative reference does not cut it.

View 8 Replies View Related

Compare Values Based On Multiple Reference Values?

Sep 25, 2013

In sheet1, I have the borrowers and outstanding per employee. In the Area Avg sheet, I have the average borrowers and outstanding in each area as this is the standard for comparison (I got this through the subtotal function).

Now what I have to do is in sheet1, format(change the color) each cell under borrowers which is greater than the average of the relevant area. Since, (in my actual data) the name of the area may be duplicate in different regions, the reference value (average borrowers of the area) from "Area Avg" sheet has to be traced through multiple criteria which are: Division, Region and Area. The same also has to be done for outstanding but the solution can be provided for borrowers only.

View 1 Replies View Related

Insert Cell Reference In Linking Cells?

Oct 16, 2013

I want to link cells B1 thru B16 to Cells F1 thru F16 receptively.

However, at times I need B1 thru B16 to show other columns 1 thru 16.

I would like to type in the column designation in A1, ie. F,G,H,I.....and have the values of those columns shown in B1 thru B16.

View 2 Replies View Related

Insert Rows Automatically From Value Specified In Reference Cell

Sep 8, 2009

Can anyone help? I need to achieve the following involving the insertion of rows from a specified value reference within the worksheet to which the rows are being added.

The original data would look like:


The number of rows to be inserted under each entry is listed in column "C" (which is a count of the separate entries in column "B".

The output data following the application of the "solution" would need to look as follows:


Where the specified number of rows have been inserted underneath the original entry and the separate values in column "B" have been listed in consequtive cells, relating to their original entry in column "D".

Can anyone suggest code to achieve this outcome?

View 9 Replies View Related

Change Formula Cell Reference Based On Another Cell Reference

Jan 8, 2012

How do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.

1 Task___________Trigger_____________Days_____Due Date
2 Design begins__Proj OK______________10____10-Jan
3 Specs written__Design begins (A2)____5____15-Jan (D2+C3)
4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)

If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?

View 4 Replies View Related

Stop Cell Reference Changing When Insert Column

Jun 4, 2009

I have referenced data in two colums on a sheet A and B. Column A contains the latest data, each month i insert new column (moving column A to column B). However all of my references continue to follow the original data (eg will change from column A to column B). this happens despite using Absolute references. (=$A$1). Is there a way to lock these cell references to only ever display column A etc?

View 2 Replies View Related

Insert Cell Reference Into Macro For Data Filter

Nov 19, 2013

I want to insert a cell reference into the code below to replace the hard coded "100500" so the value in cell A1 replaces the criteria below. Is this possible ?

ActiveSheet.Range("$A$3:$B$17").AutoFilter Field:=2, Criteria1:="100500"
Range(Selection, Selection.End(xlDown)).Select

View 2 Replies View Related

Macro To Insert Data In Cell In Workbook1 For Reference In Workbook2?

Apr 4, 2014

I need to open a customer-submitted workbook which has a list of items in column B, marked by an 'X' in column A on selected rows. Each customer's list is tailored to them with, say, 25 rows each.

I have a master list with identical formatting but which has the data from ALL lists combined.

I want to insert an 'X' in column A of my master workbook for any matching items for which there is an 'X' in column A of the customer's workbook.

I could do this with a formula in column A of my master list, but each customer's workbook has a unique filename and I don't know how to make the formula use a changing lookup array, so I figured this is something that I have to accomplish with VBA.

View 2 Replies View Related

Stop Formula Column Reference Changing On Insert But Not Row Reference

Mar 6, 2008

A 'Days Attended' cell (N8) and a 'Days Absent' cell (O8). N8 needs to count the number of "Present" values there are on another worksheet. The other worksheet has dates across the top and names down the side.

When i use
=COUNTIF("Attendance!C9:Z9", "Present"),
and the next date comes along the formula changes to
=COUNTIF("Attendance!D9:AA9", "Present")

ie. the reference moves a column across - the new date's absent or present is not counted. Using =COUNTIF(INDIRECT("Attendance!C9:Z9"), "Present"). is no good because when i add a new name i need the row reference to move down as a row is inserted. ie. both person's formulas count the same row. So, my question: I need the columns to stay the same - C:Z (leyway for future dates) and the rows to change as i insert or delete people from the system.

View 2 Replies View Related

Insert Row And Values Based Upon Condition

Apr 4, 2014

I need a macro I can run that will insert a row whenever Column A changes (from PPN A to PPN B to PPN C, etc.) and that will insert string values into the Cells as follows:

In all cases, the insert cell values into column A (PPN) will be the value from the previous cell and column B (CPN) will be 'LABOR'

Here's image of before and desired after:



4AQ02LABOR -insert row and populate cells here
4AQ03LABOR -insert row and populate cells here

View 1 Replies View Related

Add And Multiply Values Based On Date Reference

Apr 27, 2009

I'm trying to calculate Interest accrued from a specific date. The problem is payments can be interest only which when used should keep the next months interest at the same rate instead of decreasing because the principal hasn't been paid down.

Im having problems when over the course of a number of payments they are principal + Interest and Interest only which requires at times to add up the same interest amount for 2-3 months in a row, then add up a different interest rate, etc. Additionally if the reference date is say on the 15th and the interest date is the 1st then for the last month I need to calculate the interest on a daily basis based on the current interest rate for that month.

The reference cell is H5, the Date ranges are J2 - J12, and the principal fields are: K2 - K12.

In the next post I will include the Amortization schedule.

In this example the interest is accrued at the same rate months 1-7, then decreased for months 8 and 9. so the total interest accrued should be eqaual to: $7487.74 (see next post for schedule).

Microsoft Excel - ZZ_Loan.Calculator-Update.1.1.xls___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutH6H7H8H9H10H12H13H14=FGHIJKLM1 v 1.0 PaymentApply DatePrincipal + InterestAdditional PrincipalInterest Only2 1/1/2009 833.333 2/1/2009 833.334Quarter Close Backward 3/1/2009 833.335 Close Date9/1/2009 4/1/2009 833.336 Days of Interest0 5/1/2009 833.337 Beg Principal Balance$100,000.00 6/1/2009 833.338 Paydowns$980.41 7/1/20091,321.51 9 End Principal Balance$99,019.59 8/1/20091,321.51 10 Net Principle Due$3,562.50 9/1/2009 829.2711 Interest Due & Accrued 10/1/20091,321.51 12 Interest Earned (cummulative)$7,350.66 11/1/20091,321.51 13 Interest pymts (cummulative)$7,350.66 14 Net Interest Receivable$0.00 TEST [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 10 Replies View Related

Insert Rows Based On Text Values In Cells?

Feb 20, 2014

I regularly run a report that comes out in a slightly different format each time depending on whether or not there is data for specific criteria. for instance (sample attached): criteria a, b, and c are in rows, 1, 2, and 3 in columns. a, b, and c are expected to repeat themselves several times. if there is no data in one instance of b then you may see something like abcacabc.

I'd like to loop through each row in column a, check to make sure that it is the correct value (either a, b, or c) and if not, insert a blank row so I can then copy the data to my final project without having to worry about formatting.

the example above would then become abca cabc

View 3 Replies View Related

Reference Cell Values In VBA

Nov 14, 2013

How do I reference value of a cell in VBA example..

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Range("$A$1:$XFD$10000").AutoFilter Field:=2, Criteria1:= _

I want to replace hard coded "NAME" with the contents of a cell in another sheet?

View 2 Replies View Related

Add Up The Values Across The Sheets From The Same Cell Reference?

Feb 21, 2014

I've got a workbook with about 1,000 worksheets in it and I need to add up the values across the sheets from the same cell reference. I can get this bit to work with this formula:


So this adds up the contents of cell BJ3 across all worksheets located between 1 and 2241. However I also need to count the number of times a zero appears (zeros only not blanks) so I thought this might work:

=COUNTIF('1:2241'!BJ3, "=0")

However it just returns the #VALUE error.

View 5 Replies View Related

Using Cell Values In Reference Formula

Jul 27, 2006

One of my longest running problems with Excel is how to use a cell value to point to a reference. If I have three sheets, 'Main' and 'Data 1' and 'Data 2'. In the Main sheet I'd like to be able to have a value of either 1 or 2 in cell A1. Then in cell B1, use the value of A1 to point to cell A1 in either Data 1 or Data 2 sheets.

View 3 Replies View Related

Insert Row - Based On Cell Value?

May 23, 2013

I require spreadsheet to insert row based on the Value in Column (C). If The value in column C is < 15 then insert Blank Row below the line.

find attached the speardsheet.

i.e. 01.00am has been booked for 14plt which is LESS than 15 therefore i require excel to insert row so i can book addtional delivery during that hour.



View 2 Replies View Related

Insert .jpg Based On Cell Value

Apr 13, 2007

I have some code that changes the colour of a cell based on the value of that cell, not sure how useful this is as I could just simply use conditional formatting.

I have a pivot table that is constantly changing values, the column is a result of a number of days...not really relevant.... however If the cell contains a value of greater then 7 I want to insert a .JPG of a lil stop sign, if less then 7 then a little green go sign will be insertedThis is what I have so far..just changes cell color.

Sub DoOnSelection()
Dim oCell As Range
For Each oCell In Selection
If oCell.Value > 7 Then
oCell.Interior.ColorIndex = 3
End If
End Sub

View 9 Replies View Related

Insert Hyphens In Between Multiple Values In Same Cell?

Sep 25, 2013

I've a set of 10-digit numbers within a cell, which need to custom formatted in a particular format.

i.e., '1234567890' should be custom formatted to '123-456-7890' (for this i'm using the custom format 000-000-0000)

However, when I have multiple 10-digit numbers like shown in the trix (click here), i'm not able to format them because they are all comma-separated.

Do LMK if there is a workaround

View 1 Replies View Related

Insert Rows Based On Cell Value

Jun 24, 2014

I'm working on a large table which has one unique product number followed the number of rows that I want to add below it (for other variants of the same product number).

The Data looks like this:

Column B Column C
12543 2
13456 2
19543 1

I want it to look like this:

Column A Column B

View 2 Replies View Related

Insert Prefix Based On Cell Value?

Jun 3, 2014

I have lists of numbers in column A. Based on the number in the cell, I would like to insert RO_ or RP_ before the number. I currently have two buttons for this, one named RO and the other RP and have simply recorded a macro of what I wish to do. However, instead of inserting RO_ or RP_ before the number the macro replaces the hole lot with "RP_10" or "RO_10" as this was the cell i recorded the macro on. How do I make it so it just inserts the Prefix rather than replace the contents of the cell.

Currently my code for one of the buttons is as follows;

[Code] .....

View 1 Replies View Related

Insert Rows Based On Value In Cell

May 16, 2011

Want to run a script to add rows beneath each row based on the value in one of the cells in that row and then move to the next row of data and do the same.

For example:

Cell E5 = 7, I want to Insert 7 rows beneath row 5, then move to the next row and insert the number of cells equal to the value in column E of that row.

View 6 Replies View Related

Insert Rows Based On A Value In A Cell

Jul 14, 2009

I need to create a macro that will insert exact number of rows based on the value in certain cell. e.g. value in cell F2 of sheet1 is I need to insert 3 rows down from cell F9 in sheet2 copying data in cells D9 and E9 to inserted Cells / rows.

View 4 Replies View Related

Copyrights 2005-15, All rights reserved