I am trying to match by amount with 1 column to another in a different sheet. If Data in column A in worksheet 1 matches data in column b worksheet 2 then color the entire row in column A worksheet 1 red, I am matching by amount so it(amount in column a) can be within 1 $ of amount in column b worksheet 2 also color item in column b(worsksheet2) red if matches with column a in worksheet 1 both can have a threshold of 1$ difference
View 2 RepliesI am trying to summarize some of my data. I have attached file with Sheet "Data", and how it should look in Sheet "Result".
1. If on two rows content in A, B, C, D match exactly, to copy them on sheet Result
2. Also Copy E5 (it will always be the same if the first four cells match, need to include it only once)
3. Also Combine all F cells together with ; (first entry is Develop and Second entry is Test - then in Sheet Result it will become Develop;Test)
4. Also Sum the amounts in G
5. And then Copy all other rows that don't have exact match
There might be 3-4 rows that have exact match on A, B, C, D.
I need a little coaching on big data. I have two reports, one from a production system the other from the invoicing system. I need to reconcile the two reports to ensure our invoicing ties out to production. At this time I am focusing on 1 particular item code, but there are potentially hundreds of item codes.
I have 1 Item Code.
I have a around a hundred invoices. (Sample 100)
I have a few hundred different customers. (Sample 100)
I have several hundred lines of billing data. (Sample 650)
I have several thousand lines of production data. (Sample 40,000)
In the billing system, per invoice I might have 1 or more billing lines for this item, usually no more than 2.
In the production system, per invoice I might have 5 to 50 or more production lines for this item per invoice.
Example
Prod System:
30 lines of data has a run amount and a total run amount for each line.
I am looking for tools to reconcile this data. VLookup and Index/Matching is not cutting it. VLOOKUP does not move beyond the first line found. Index/Matching wants to add all the prod together. I can get subtotals easy enough. I have tried Concatenation, but when it takes multiple production data lines to equal 1 billing line, this does not work. I needs some thing that can look at the Qty on a billing invoice and tie it out to various lines of production data to identify which production jobs went on that line of the invoice.
Here is a sample. These are fairly easy to resolve, but others are not so easy due to number of production and billing rows.
Invoice number
Billing item
Est. Quantity
Actual quantity
Billable Qty
Sales order
Position number
Sequence
Agreement
Billing source
Description
Total Billable Qty
[Code] ..........
i am trying to do is match 2 columns data based on a condition ie i have data in column "b" of sheet called "balance" data is variable this column has unique indentifiers i want to look at another column and match the uniques identifiers in another sheet if the cell value in column "D" = "please investigate" otherwise do nothing, the (column f) it will be looking up too is in sheet called "hi- port" so if cell value in column "d" of sheet"balace"= "please investigate" match values that are same from colum "b" of sheet "balance" to that of column (f) sheet called "hi-port"
i would like to colour only entire row of sheet called "hi-port" when the data matches to other sheet ie from columns "f " too " b". colour can be any colour
igonre my ranges they are wrong just used as example
Sub start()
Dim oWs As Worksheet
Dim oRngCheck As Range
Dim oCl As Range
Dim cCola As Range
Dim cColb As Range
Dim rngIRd As Range
Dim rngACs As Range
Dim Match As Boolean
Set rngIRd = Range("a1", Range("a65536").End(xlUp)) 'define the rang
Set rngACs = Range("b1", Range("a65536").End(xlUp)) 'define the rang
Set oWs = Worksheets("balance") 'change to suit
'starts at b2 to llow for header row.......................
I have a row which contains a date header.
Under this there are multiple lines of activities each with a different end date and empty cells under the calendar headers so that people can enter how long they have worked against that project.
What I want to do is to compile a VBA coded which will cycle each line and at the intersection of where the end date and header match to colour that cell in a defined colour.
An example below:
Date
Colour
01 Jan
02 Jan
--->
[Code]......
I need a formula to calculate the amount due based on cumulative sales once a breakpoint amount is reached.
Example:
Breakpoint:
cum sales are > 500 pay at 3%
cum sales are >1,000 pay at 2%
month/ sales/ cumul sales/ amount due
jan/ 100.00/ 100.00/ 0
feb/ 600.00/ 700.00/ 6.00
mar/ 600.00/ 1,300.00/ 18.00
and so on...until the end of year.
I tried using an if formula by could not get it to work.
Im trying to set up an active running inventory sheet where: (A)the progressive daily sheet cells reference back to the corresponding master sheet cells fluctuating the master values, (B) the same progressive daily sheet cells reference back to a cummulative totals-cell based on whether I added or subtracted inventory. I want to make a copy of the blank "sheet 2" with all of the formulas and move it to the end of the workbook each day and enter new values which will reference back to the master sheet so that I can click on a date sheet and see an individual day's values or click on the master sheet to see the fluctuating inventory on-hand and the cummulative +/- totals of all days combined. I've got a couple hundred individual cells to reference. I've tried and tried but I can't make it work. Heres what I need to do:
I need to reference individual cells from "sheet 2,3,etc" back to a corresponding cell in a master sheet. But I need the values in each cell in "sheet 2,3,ETC" to increase or decrease the corresponding cell values in the master sheet. For example: If the value in the master sheet B5 is 200. Then in sheet 2, I enter +50 in B5, I need the master sheet cell B5 to increase by 50 to 250. I also need a way to decrease the cell value in the master sheet B5 if I enter a negative value -50 in sheet 2 B5. I also want to know if I can reference the same cell values entered in "sheet 2,3,etc cell B5" back to totals columns C5 for adding inventory or D5 for subtracting inventory in the master sheet where the master totals columns would reflect cummulative totals added or subtracted. For example: if the value in sheet 2 B5 is +50, then the value in Master sheet C5 would add 50 to a progressive total. But if the value in sheet 2 B5 is -50 then the value in master sheet D5 would add -50 to a progressive total.
For the below macro.
ABCDG
CodeTotalBaseNetA1 = 20%
A110083.3316.67A2 = 10%
A2350318.1831.82A3 = 7%
A3220205.6114.39A0 = 20%
A015015030
A1 is the code, take the total(B) and calculate the base(C) on the percentage given in the G, net = B-C. for A2 and A3 it is the same process.
For A0 the total (B) is equal to Base(C) and calculate the net on the given percentage in G.
I have been trying to get Excel (2007) to change the colour of tabs to match other tabs in the workbook
Using this code I get the colour code of the current tab
Code:
x = ActiveSheet.Tab.ColorIndex
But when I use this value in a procedure like this:
Code:
ActiveSheet.Tab.ColorIndex = x
The tab is a different colour!
I would like it to loop through all the charts on the "Graphs" sheet.
It works well but gives a "Run-time error '1004'" Application-defined or object-defined error afterward that I would like to get rid off.
VB:
Sub Colour_Chart_working()
Dim rngColors As Range
Dim rngCell As Range
[Code].....
Is it possible to search Excel by cell colour, i.e. font colour...and then change the said colour to another one?
View 3 Replies View RelatedI have a lookup that gives and RGB colour code in Cell A1; for example 186, 206, 140.
I'd then like Cells R10:V15 to fill with the RGB colour based on the result in A1.
A column of cells can be 1o different shades of green according to their value (achieved by using conditional formatting.) I want to arrange that at a certain time all the cells of a part of the row of a formatted cell are the same colour.
View 4 Replies View RelatedI am trying to subtract an amount from a cell until it reaches 0, then move and subtract from the next amount, and so on.
AmountFixed BudgetedResult53.50Subtracted 5 tell it hit zero1.30Subtracted remaining amount until zero43.8Subtracted remaining amount until zero3.8 was left over without hitting zero is good. Is there formula for this?
So I have a macro that colors the selected cells in a certain color. Now im looking for a code that will switch that color every 7th cell, the cells will only be selected horizontally. For example:
if the selected range is 10 cells, 7 of them should be in one color and 3 of the should be in another. But if the selected range is 15 cells the first 7 should have one color and the next 7 should have another and then the last one should have the first color again, and so on..
In a financial environment we have a calculator which uses iteration to allow for a cost being added to loan amount where the cost is based on the total loan amount. Iteration is set to 100 iterations with max change .001
On one PC the first time the calculator is opened it gives a particular (incorrect) result. If the input cells are cleared and the data re-entered, it gives the correct result. This only happens on one particular PC. Is there some other setting , other than the iteration setting, that would cause this?
I'm creating a worksheet which tracks pupils progress in our school. down the left there is a column to put in the level they came into our school on - eg 1C or 3B etc.. to the right of the level is a colour coded row which shows Pink, Yellow and Green - if the pupil makes it into the green part of their level by the time they leave then they are on target.
Basically I have a column of cells which use a data validation list which is linked to another sheet with a list of levels. What I want to happen is when I select the level - it fills the row to the right with the correct colours.. I've attached a picture to show you what i'm trying to do.
Reason being is - we might have 6 kids on L3, and 1 on L1 so the number of rows needed for each level changes - at the moment I'm having to copy and paste - which although is easy enough, it is a bit tedious when you have a whole bunch of pupils to put on the grid.
I can't find the option in imageshack to use a thumbnail
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.
i want a cell to change to the colour green if 5 other cells on the page are green colour.
I want a cell to change colour to red if there are 1 or more red cells on the page.
How do I make this work?
I am trying to automatically change the colour of an oval in worksheet1 to the colour of oval3 in worksheet 2.
Currently oval3 (Worksheet 2) has the a vba code that changes its colour when you click on it.
I want a cell to be equal to another cell including the colour, conditional formatting as the colour will have no relevance to the cell value i.e. the colour will be set manually dependant on what stage of the lifecycle it is at
The original cell may have a date and be yellow and I want the target cell to be yellow with the same date, if someone then changes the original cell to be red the target cell should automatically change to Red
Is there an easy way to do this or do I need some VB script?
How can i sum up the amount of all "case" sold in the second month of 2009.. so the output should appear in C11 as i have already done manually.
View 9 Replies View RelatedI am having some trouble getting a formula to work. I am building a report that pulls figures from a pivot table in another workbook. I am using a vlookup with match function to get the column index to find the relevant data I want. Where I need to add two columns together I am using sum, with the vlookup & match formulas nested in them e.g.:
=SUM(VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("FAID",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("BPCM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMD",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE))
Where:
F13 = Employee number
Column C on the pivot 156 workbook is where the employee number is based.
The Match formula is then getting the column index from the column headings of the pivot table ie. "FAID"
This in itself works fine, as long as it finds a match in the column headings. This is where i get the error as in the above function "COMD" is not in the pivot table. However I need to keep it included as it may appear on a future pivot table. Is there a way of getting the sum function to complete even though later in the formula it can't complete the vlookup? So it will ignore it, or assume the value is zero if it can't find it? The formula probably needs to do this for all the vlookups as some headings may drop off in future pivot tables.
I am trying to match data from 2 independent sets, formatted slightly differently so not sure which function would work best for me. From the attached file, I am trying to match the date and time stamp (in cell A1) with that from the other data (in this example in cell E1) and return the data (from cell F1) to cell C1. So basically any date and time stamp before 04/03/09 04:00 will return a value of 44 (this value should appear, therefore in cells C1 - C30)
View 2 Replies View RelatedI have a WB with multiple sheets. I have a Feed sheet that automatically pulls data from the web. I have a Scores sheet that currently has all head to head matchups each week for an entire season. So I want to check the cells on the Feed! sheet to find a partial match and if partial match exists then copy cell that contents score from feed sheet to the score sheet next to the appropriate teams name. Currently I have to manually enter all scores each week for the rest of my WB to update.
I need to look at Cell A3 (on the Feed sheet) which has "New York Jets" then search for a partial/similar match on the score Sheet (which is NY JETS, in this case). NY JETS could be in column B (rangeB2:B257) OR column D (rangeD2:D257) BUT I need to search by row, not column, then once a match is found check the cell to the immediate right and only if the cell is blank copy data from the (!feed) sheet to that blank cell on the (!scores) sheet
This is what happens if working right =
look at cell A3 on the (!feed) sheet = "New York Jets" then search (!scores) sheet Column B and Column D by row for a partial match, finds "NY JETS" as match in cell D8, if cell E8 is blank then copies cell H3 from (!feed) sheet, and pastes to cell E8 on the (!scores) sheet, if cell is not blank continues search until 1st blank cell to the right of matching cell is found (as there will be mutiple matching cells with blank cells to the right but I am only interested in the 1st blank cell found, once found and data copied the process is done and then starts over with cell A4)
Look at cell A4 (!feed)= "New England Patriots" then search (!scores) sheet Column B and Column D by row, finds "NEW ENGLAND" as match in cell B8, if cell C8 is blank then copies cell H4 (!feed) sheet, and paste to cell C8 (!scores)
Once this is done it moves on to the next cell in the next row on the (!feed) sheet, A5, to find a partial match for the data in that cell. I need to continue the search for each cell A3 to A74, and if no match is found to move on to the next cell A6... (based on the way the data is pulled in from the web there are some blank cells as well as some cells that say Game Final, this data won't be on the scores sheets in column B or D).
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.
=SUMIF(AU8:AU200,MONTH=3,AQ8:AQ200)
I have a total amount of $52,000 and I want to show an equal amount in twelve
columns which represent 12 months from sept 2005-sept 2006. My calendar goes
for 3 years, jan 2005- jan 2008. How do I get this amount to show up in the
correct columns.
I want to sum income of my team by name without sort.
Example: I have as beloow in excel:
$ 10.91 Khear
$ 10.91 Khear
$ 10.91 Vichda $ 22.73 Linda $ 31.82 Linda $ 13.64 Vichda $ 10.91 Khear
$ 10.91 Khear
$ 10.91 Linda
$ 13.64 Virak $ 10.91 Khear
So I want to calculate name Linda, how much? Khear How much? without sort or separate for calculate.
How can I display the $xx.xx into a statement?
I have these $ amounts in column A:
99.12
107.65
14500.99
Have the result in column B as:
Please Pay Exactly$00000xx.yy
(where xx is tenth, hundreds, thousands and yy is the cent)
00000 is to prevent forgery
and to have the results in column C using same column A:
Please Pay Exactly$0000xxDollars and yycents
For example like Please Pay Exactly$000099Dollars and 12cents
or Please Pay Exactly$000014500Dollars and 99cents
I need a formula that will replace a dollar amount with an X. In other words...I have 10,000 cels and half of them have dollar amounts in them...I would like to replace them with the letter X without having to manually do it...
View 9 Replies View Related