these are two tables i have to compare.Sl No
Type
1
NORMAL
2
NORMAL
3
NORMAL
4
QUAD
[Code] ........
Sl No
Type number
1
1
2
1
[Code] ........
where,
1=NORMAL
2=QUAD
3=BI-DIRECTION
i want to compare between these two table based on the condition writen in RED. IF there is a mismatch i want to highlight the values in the first table.
I have 2 worksheets - one is a monthly update, the other is last month's data.
I want to highlight the changes on the monthly update sheet using conditional formatting.
I named the columns as ranges on the "last month" worksheet, and then used =Match(A1,Jan,0) (wheras Jan is the range on the "last month" worksheet where A1 resides)
What formula do you use to say "if A1 does NOT match a value in range "jan", then format it"?
I would like to compare each cell in a spreadsheet to the cell on its right.
If the numeric value of a cell is less than the numeric value of cell on its right, I would like to highlight the cell on the right light red.
Maybe a better way of putting that is if the value of a cell is greater than the cell on its left, highlight it light red (The problem I foresee with this formulation is in the first row where there are no cells to the left).
it's possible to set up conditional formatting to compare values between two identically-formatted (i.e. same row and column) pivot tables?
I have two pivots from two different sources and I want to be able to visually identify discrepancies between the two. Both have dates as the row labels (same date range) and categories as column labels (some categories may not necessarily be present on both pivots but the format of the labels - i.e., the names of the categories - is consistent where there is overlap)
I've tried doing this with 'traditional' CF but whenever I manipulate the pivots (i.e. by selecting / deselecting an item in a report filter), the movement of the columns trashes the formatting and I have to set it all up again.
Figured, given the structure of the tables is essentially the same, there might be a smarter way of doing it (by referencing the pivot fields in some way)
I'm working on a report that uses part numbers in column J, starting in J6. I'd like to highlight the entire row if a certain p/n from a lookup table is in J. (the table is in the sheet "Testers" from A2 to A42 and the relevant p/n's are in column "A") I went to cond format and in use a formula to determine formatting I entered:
=vlookup($J6,Testers!$A$2:$A$42,1,0)
And then selected a fill format but whan I apply it does not work. I tinkered with the $ to ensure it jumps to the next row but stays in the column but I cannot get this to take.
I need to format cell D1 to have the fill color be red if any cell in D2:D21 is blank. I've tried
=VLOOKUP("", $D$2:$D$21, 1, FALSE) but that returns #N/A (there is one blank cell in the range at the moment).
I really don't want to use =OR($D$2="", $D$3="", ...$D$21="") if I can avoid it.
I have one .xls file that contains an inventory list of all the products that I want.
Unfortunately this .xls file does not contain the prices for these items. I have another .xls file that contains SKU numbers and the prices related to those SKU numbers.
Is there a way to run every single SKU in the first file against the second file and, when a match is found, take the entry in the price column of the second file and place it in the first file so that my first .xls file contains all my products with prices?
I included sample data.
i have a formula in a cell:
=IF(ISERROR(VLOOKUP($D11,PRMC!$O$15:$P$1048576,2,FALSE)),"",VLOOKUP($D11,PRMC!$O$15:$P$1048576,2,FALSE))&IF(ISERROR(VLOOKUP($E11,PRMC!$O$15:$P$1048576,2,FALSE)),"",VLOOKUP($E11,PRMC!$O$15:$P$1048576,2,FALSE))
The first half of the vlookup searches for data and returns a dollar amount if true The second half of the vlookup searches for another data set and returns a dollar amount if true...
Both amounts could be the same, so I cant use a formula based on numeric value
the conditional format must determine which vlookup is returning the result
in other words I'd like to conditionally format (grey fill) if the first vlookup returns data and conditionally format (blue fill) if the second vlookup returns data
Probably have to "use a formula to determine which cells to format"
I have two tables. Table A has 13 columns and 641 rows. Table B is in a different tab within my excel file but is only one column with 216 rows. Starting with Table A I need it to look up for the value in Table B. If the the value is matched, meaning they appear in both tables, I want the cell highlighted in Table A.
View 7 Replies View RelatedI am using excel 2007, and trying to incorporate a VLookup into some code. The VLookup is comparing dates, which I think may be part of the problem.
View 3 Replies View Relatedi'm building a spreadsheet where there is a list where users will input work shifts (those shift can be, for example, "M", "N", "T", "Me", "Ne", "Te"). i use two different columns to retrieve (using vlookup) values for M, N, T and Me, Ne, Te shifts. those values are in two different tables (one table for M, N, T shifts and another one for Me, Ne, Te)
for the M, N, T shifts column i use:
VLOOKUP(A1;$values.$A$1:$D$3;IF(WEEKDAY(G3)=1;4;IF(WEEKDAY(G3)=7;3;2)))
for the Me, Ne, Te shifts column i have:
IF(ISNA(VLOOKUP(A1;$values.$A$67:$D$69;IF(WEEKDAY(G3)=1;4;IF(WEEKDAY(G3)=7;3;2));0));0;VLOOKUP(A1;$values.$A$67:$D$69;IF(WEEKDAY(G3)=1;4;IF(WEEKDAY(G3)=7;3;2));0))
this seems to works fine, but only for one shift per day. the problem is that workers may work more than one shift per day. is there a simple way of, in one column, vlooking up the values for every M, N, T shift, every day ignoring any Me, Ne, Te and the opposite in the other column?
I have a problem regarding month formatting
I have 12 columns, Jan 2013 - Dec 2013.
I need to set a format in which if I enter a num eg 15 on the Jan Column, format comes as "15-Jan" format
or If i type 20 on the march column , it comes as "20-Mar" format
I would need it in Date format, because I would be comparing dates later on.
Is it possible to use multiple Vlookups in one macro?
Currently running a vlookup of computer names between multiple sheets, i need to be able to match product name and computer name between sheets.
For example (Sheet 2 column B) compare it to (sheet1 column B and C)
if it gets a match then vlookup to (sheet 2 column A) to (sheet 1 column A)
Spreadsheet attached : excelhelp.xls
I have two separate columns of text and want to identify similarities/matches. vlookup false is good for exact matches but vlookup true is too approximate - is there a way to hone vlookup true to match first 4 or 5 text characters rather than just the first? If vlookup no good for this what is best function for identifying approximate matches in two separate text lists?
View 2 Replies View RelatedI have four sheets with a lot of pivot tables on each and my problem is that whenever I hit Refresh the formatting of the pivot tables is always changing. It seems that this is something of an issue in Excel 2010 and that they only way to truly get around it is to re-format the pivot tables upon refresh with VBA code.
Any resource or thread I can use to learn the commands to format the pivot tables?
I am getting better with VBA code but seems to always get stuck on trying new things because I do not know the commands.
I have a spreadsheet with two tables in it. Table 1 contains some data consisting of two columns both populated with data (where 1 = a, 2 = b, 3 = c and so on) and table 2 consists of two columns one column contains data and initially (numbers) column two is blank. Now I need a VLOOKUP that will look at the second column (red letters in the example) in table one, and where the same value (number) appears in table 2 populate the corresponding letter.
I do not wish to use filters as the table is extremely large. I have attached an example
I have tried to put together three simple formulas for a very simple problem. I wanted to string together three vlookup formulas which were as follows;
=vlookup(F4,prices,6)+vlookup(D4,floorpremiums,2)+vlookup(G4,aspectpremiums,2)
The first lookup would work but as soon as I added another it would result in #n/a. I tried adding the suffix "false" after column no. but that did not work.
On the second lookup I did mix text and numbers e.g the first row in the column had a "G" in it and the rest were numbered 1-10. The second two vlookups were on a seperate sheet but in the same workbook.
I have checked the named ranges and they work fine when done individually but it seems to be the combining which messes things up.
I have a few tables on a worksheet, and to search these tables i am using a VLOOKUP, however i want to be able to search for other than just the one field! but still pull up all of the data. If i were to do the same tables in access i would be able to query all fields and then recieve an output of those that match, is there any way to do this in excel
Note I am only into basic VB. Other question i have is, on a VLOOKUP you only return the one record, is there anyway of searching a table of data and returning all records that hold that value in a specific field, like applying a filter to them. or am i just being extrememly dumb and missing something simple?
compare two different data in different sheet i am confused of volookup how do this work.
View 1 Replies View Related(Core starting issue solved but other threads were created to continue with trouble spots.)
I'm terrified to use VBA just yet, so right now I'm determined to highlight values in my spreadsheet just using conditional formatting.
I have 5 plus 1 columns of data. For example,
A B C D E....F
3 2 4 5 7....2
4 6 1 3 4....1
4 7 4 5 8....4
2 1 5 3 9....1
I successfully applied a MIN formula to compare between columns on each
row and display in F the MIN value. Notice each cell going down F has the
minimum value in analyzing across each row.
Now, I *also* want to apply a formula on the whole F column to compare these resultant MIN values
and give me the MINIMUM value between those as well, so now I'm comparing across
rows. Got it? Now then.....I want the ENTIRE row that has this MIN value in F to be highlighted, because that will be the best choice for the application of what I'm doing. Did you understand this paragraph. It might have been confusing, but I think I said everything correctly, so read it carefully.
I've tried all sorts of "Formula is", "Cell value is", dollar sign this, dollar sign that, no dollar sign this, <, >, =, highlighted ALL the block of data, highlighting JUST the column across the rows of which will be compared, only highlighted one cell, but nothing has worked.
Is my problem that you can't have 2 formulas in one cell? So then I thought I should ADD another condition, but nothing works.
Let me throw in one more element that I did not state. If anywhere down the F column there is a 0, I don't want that to be my minimum. I want it to evaluate and give me the minimum value greater than 0.
Please read everything I wrote carefully. It might be confusing, but I hope someone has an answer, because it sure has frustrated me.
Now, I'm thinking that VBA programming may give me answers, but I am so terrified of macros, I don't know how to begin that. I have this impression based on the many sites I've looked at on the internet that many people do macros and it's quite easy, but it's funny to me that I am so scared. There's something about me freaking out that I won't have a period or a quote in the right place and I'll stress my day away over it.
[1] compare (row) ranges across two columns with an unequal number of rows (column A [number] to column C [number])
[2] save each result of [1] where there was a match in column B
[3] for each row where there was a match (now stored in B), compare the value from the same row in column D (date) with the same row of column F (date), and store the result in column E
[4] count and message box the final number of matches from column E
Data Example:
A B C D E F
----- ----- ----- ----- ----- -----
12345 match 12345 11/1/2011 match 11/1/2011
77777 34345 ... ...
A and C numbers match, so check same row date in D that does match date in E, place "match" in E, then count and display.
Sub other()
Dim range1, range2, range3, range4, range5, range6 As range
Dim x As Variant, y As Variant, z As Variant, a As Variant
[Code].....
Okay, so I have this project for my accounting computer applications class. I'm making it so much arder on myself than what it probably should be. But anyways, this project is a four year plan spreadsheet for the business/accounting division of the school. Inside the spreadsheet is a page that has 15 different tables, representing 5 years with 3 semesters each, fall spring and summer. On the requirements for the major chosen, I would like it to automatically recognize if the class has been placed in the semesters tables and recognize all of the information needed. The only problem is, a single vlookup only allows one table per cell but I need all 15.
I've heard of using the if statement along with these but I've only heard of it being done with a few tables and not 15, and I don't understand how exactly that works anyways.
I am wondering if there is a way of performing Vlookup across several tables? I have tried to create a formula myself based on lookup and nested vlookups, but am getting all tanlged up, especially as I need an If isna formula in there so that if there isn't a result, it displays a 0 rather than a #N/A.
See the attached. I do compeltely understand that it would be so much easier if all the data on the commission worksheet was in a simple 3 column table, but it has to be split as it is creating 3 tables. exactly what formula needs to go in cell D7 on the rates worksheet that I can drag down, which will check for those initials across all 3 tables? (The initials will only appear once on the table on the commission worksheet.
I have 3 different tables on a sheet which I want to do a vlookup on depending on the selection from a dropdown.
I created the ranges I want to use in 3 cells and tried to reference the cell rather than the range. Excel no likey:
=vlookup(B2,A1,2,FALSE) instead of =VLOOKUP(B2,D1:H14,2,FALSE)
Is there anyway I can do what I am trying to do?
I have a spreadsheet that records the date, specific sporting activities and cash award for participation in a round robin sporting event that was held over 52 weekends. I'm trying to lookup the cash award value of each event the person participated in by using a date range within the vlookup formula.
Each participant has their own spreadsheet which they are able to access via server, and each spreadsheet looks a bit like this:
-Column A contains the date of an event in which the person participated
-Column B contains a category number related to the type of event eg marathon = 12, hurdles = 15, relay = 18 etc. This info is manual input
-Column C contains the amount the person will be awarded by simply participating in the event. This is a lookup value and varies depending on the date the person participated, so I'm trying to incorporate date ranges and 'if' formulas into the vlookup
I'm from Australia so my dates might be backwards...
Spreadsheet:
A B C
1 02/01/13 15 (supposed to be $10)
The formula should read a bit like this:
if (date at A1 is within date range 1st Jan 2013 and 31st March 2013, lookup B1 in table 1 column 2), if (date at A1 is within 1st Apr 2013 and 30th Jun 2013, lookup B1 in table 2 column 2)...etc for each quarter
I don't know if the following is right
=IF(AND(A1>='01/01/2013'+0,A1='04/01/2013'+0,A1
A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
dust 1.xlsx
I am tired of doing the same amount of repetitive work, I would like to know if it is possible to acheive a "template" where I just drop a database pull and it negotiates where the data should fall.
When I pull data, I have the following values I need -
Part number
Date - which is by day, I need grouped by Monday - Sunday to seperate amounts ordered by week.
Branch plant - East and West
Order Quanity which is placed by day
QOH - Quanity of parts I have on hand
I need the days grouped by weeks, 7 days by date equal 1 week.
I need summing part number amounts due within that 7 days into one total.
I would need 4 columns
1) Demand due
2) Supply due
3) QOH
4)Difference
I need each branch to have their own sheet, for a total of 4 sheets... Supply and Demand for both plants.
What it boils down to is I need to know what the demand is per week, and the supply I have to meet that demand, broken down like the attached sheet.
Seems my sheet is too large due to the amount of data for the site, but here is the link to the LARGE File...
[URL] .....
Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.
Possible???
Is there "code" for different formatting in a spreadsheet so you can use an IF statement to do something like:
if(A1=blue background,"Yes","No")
or
if(A1=red text,"Yes","No")
etc.?? But replace "blue background" and "red text" with some sort of number code? I want to compile a list of the items that are formatted with certain background colors and/or text colors and then organize only those items into a chart.
Any VBA that I can put into a macro that will convert conditional formatting into fixed formatting..? So when the cell contents/formulas are deleted the formatting remains. Assume that the range I want to convert is A1:D200...
View 9 Replies View Related