If i have a table like the one below.
A1 B1
1 Snot
0 Cheese
0 Pie
0 Bacon
1 Tree
0 Breakfast
1 Hair
0 Tidy
1 Box
How do i use vlookup to give me list like below
A1 B1
1 Snot
1 Tree
1 Hair
1 Box
I have 2 spreadsheets with SSN's in different formats. One has xxx-xx-xxxx the other has xxxxxxxxx. The cell format is set to "special" / SSN in both spreadsheets. However when you look at the data in a given cell one has dashes separating the numbers and the other does not. I have Trimmed the spaces off both sets of values with no success. I tried to strip out the dashes and concatenate it back together as well. When I sort the values numerically I can see that there are matches.
View 3 Replies View RelatedI'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'm having trouble comparing two lists of clients with vlookup. The problem seems to be that the dates are in two separate formats. I'm combining the information to create unique identifiers (=A1&B1&C1).
example:
A1 B1 C1
Eric Reilly 01/04/85
comes out as: EricReilly01/04/85
A1 B1 C1
Eric Reilly 1/4/1985
comes out as: EricReilly31051
I am trying to obtain price of an item using vlookup() function on the identifier “0003128” (7 digits) stored with “custom” format “0000000” in one worksheet. However in other worksheet, the identifier is stored as “000312” (6 digits) with “text” format without the last digit ‘8’. I tried using the left(A,6) function on “0003128” but instead of returning “000312” it returns “3128” and I’m unable to use the vlookup() function.
View 6 Replies View RelatedMy problem is that my VLOOKUP formula will not return any data when it doesn't like the format of the data it's looking up.
Example: I have a spreadsheet that displays revenues earned by assets.Every month I export a table of data from an accounting software program with (a) asset numbers, (b) invoice date, and (c) monthly revenues.Then I copy the data into Tab 2 of my spreadsheet.On Tab 1 of the spreadsheet there is a table that lists Assets 100 through 120. Column A has all the asset numbers.Each month it varies as to which assets earned revenue and which one's did not. Usually between 10 and 15 assets earn revenue in any given month and about 5 do not earn revenue.On Tab 1 there is a column with VLOOKUP formulas that looks up the asset number in column A of Tab 1 and points to Tab 2 where the data that was exported from the accounting software program is located.Let's say that in July 2012 that Asset 1001 earned $35,000.On certain months, the VLOOKUP formula looks over to tab 2 and "returns" the $35,000 revenue with no problem.On other months, it will not return anything, apparently it does not like the formatting and does not "recognize" the asset number.
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 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 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 a Vlookup which I want to modify so that it can become dynamic as the table array part of the vlookup will change.
So the basic vlookup is as follows:
=VLOOKUP($R$3,ATTRIBUTION_FACTSET!$M$60:$P$73,2,0)
but the data I am looking for wont always be in the range M60:P73.
So I tried to make it dynamic by doing the following:
=VLOOKUP($R$3,INDIRECT("ATTRIBUTION_FACTSET"&"!M"&U1&":P"&V1),2,FALSE)
The idea being that U1 and V1 would be numbers that can change so in this case U1 would equal 60 and V1 would equal 73
This vlookup is giving me #N/A and no matter how I modify it I cannot get it to work.
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'm trying to analyse all of my bank statenments to see where all my money went!! I've got online banking so I thought it would be a relatively easy process of copying and pasting each month into excel.
Well, that in itself was easy, the problem is with the formatting of the financial numbers. Excel doesn't seem to recognise them as numbers, so at the moment I can't do any manipulation with the numbers. I have tried everything such as:
Format Cells > Number > Number and Currency
Copy and Paste Special > Values only
Copying into Notepad and back into Excel
Even if it appears to have changed the numbers to 'number' or 'currency' formatting by right justifying the numbers, if I try to add up the numbers using the SUM function, it doesn't want to know.
For some reason nothing seems to work - what am I missing?
For an example, I have attached a snippet of the spreadsheet with my starbucks transactions.
I have a sheet using VLookup to find EMail and Web addresses. I can get the address to show up but not as an active URL address. Is it possible to have the address "active" so I can click on it and activate the EMail or Web Site?
View 5 Replies View RelatedI'm trying to do a Vlookup on a file that gets automatically downloaded to the computer from a website. The data is in lots of different data sets, like so:
Loans to countries
Mar
Apr
May
Jun
Loans to banks
Mar
Apr
May
Jun
Every month a new row of data gets added to each table, meaning the start and end cells of the array also shift each time.
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 RelatedI'm currently trying to vlookup the same data from 2 different sheets. Here is the code i've tried.
View 4 Replies View RelatedI’m trying to develop a workbook which holds monthly data on loan information. It tracks the interest and balance on the loan. I want the first page to have a table displaying the interest payments for every individual tab. When I was brainstorming the idea, I was considering a sort of Vlookup function to find the tab the account is on and then a further function, possibly another vlookup which connects the month to that month’s interest payment. Can anyone help me figure this out?
The attached spreadsheet is obviously simplified, there are well over 30 tabs. But I would like it to, ideally, search the account number column, search the workbook for that account number, and then when on that page use the month at the top of the first page and retrieve the interest payment and put it back in the cell. It’d also be great if the formula can be transferred between workbooks. I’m not sure if that makes sense; basically if I were to copy that worksheet into the next months book, I would like that the formula read those tabs instead of becoming obsolete due to references from the first workbook.
I have data in a pivottable which I want to compare with another table. The lastcolumn+1 of the pivottable needs to get data from another table. I have this for an example:
View 5 Replies View RelatedI have a spreadsheet (Need Data.xls) that needs to be filled out with a couple columns of data.
This data lays within 338 spreadsheets which have many items and may only have 2, or 3, or 50 that belong on my Need Data.xls spreadsheet.
I have a tab in Need Data.xls named "DIR" which has a list of 336 excel files that need to vlookup'd into.(not a separate file) They're all setup with this format:
I am receiving a run-time error with following code. The error message is "unable to get the VLookup property of the WorksheetFunction class". I only receive the message when the lookup value is not found in the table.
I thought adding the "False" command at the end would return an "N/A" but it didn't. Is there anything I can add to avoid this error?
I'm taking a spreadsheet that I produce each month and creating a year to date spreadsheet in the same format. I'm using a vlookup to find the campaign name in each sheet and add up the totals. This works fine but sometimes a camapign ends and so the vlookup for that month will produce an #n/a value so will reduce the whole sum to #n/a.
The VLOOKUP + VLOOKUP + VLOOKUP I was using that produced an #n/a is shown below.
=VLOOKUP($A6,'[Margin by Site Net April 2008.xls]Brighton'!$A$5:$F$26,2,FALSE)+VLOOKUP($A6,'[Margin by Site Net May 2008.xls]Brighton'!$A$5:$F$26,2,FALSE)+VLOOKUP($A6,'[Margin by Site Net June 2008.xls]Brighton'!$A$5:$F$26,2,FALSE)
To get round it I've added in an IF statement combined with ISERROR as shown below. It works but is looking quite messy. Is there an easier way to do this ? (the formula below is from the cell below the one above so the look up value is one cell down)
Does anyone have a workaround for the following error "Excel Could Not Save All the Data and Formatting" Error Message (http://support.microsoft.com/kb/215783). I am applying validation to many, many cells
View 4 Replies View RelatedI wasn't sure how to properly title this post. I actually have a couple of questions. The main one is on how I can code my VBA so that for the range A11:N80 I have specific conditional formatting code, and then for O11:AB80 I have different conditional formatting code, and so on for 12 sections.
The conditional formatting reads column L (in the first section) for one of 6 different words and depending on which word is read, it fills the row from A to N with the corresponding color. I have this working, but I do not know how to do the same with with section O11:AB80 and so on. The actual formatting within each section will be the same but the data is different so different lines will be filled in. Here's my code so far:
I have a userform set up to create a new row on a worksheet and enter data that was filled out on the work sheet. Some of the data comes from text boxes which put in name, rate, division, received date, and loss date. The other info is taken from checkboxes and designed to put either an X or N/A, or just leave a column blank. Basically, what I want to do is figure out, when I make a new row, how to format that row to have borders around each cell, make columns 6-29 bold, and the cells in columns 6-29 colored with a gray background. If you need a dummy worksheet to better describe what I'm looking for, let me know. I haven't made one up yet.
View 4 Replies View RelatedHow do I keep the number format to general and change 0 (zero) to "-"
I dont require a formula soution but rather through formatting cells.
The following image shows the problem I am having with my formatting in excel. There's nothing in the cell featured to make the cell length longer, but the combo box still does what is shown in the image. any idea how to prevent this? it looks messy for an end user!
View 9 Replies View RelatedI am using Excel 2007.
I've created a database template that my partner and I will have on our laptops to use in the field. It is viewed in Page Layout.
When it is on my laptop, I show 30 rows on page one of the first worksheet. But on my partner's laptop it shows 29 rows. This messes up some of the formatting.
It isn't because he has changed anything. I sent my copy via email and a flash drive and opened it myself, and the problem still exists.
I am trying to use Vlookup to find out the min and max sales of the products shown in the spreadsheet. I have used Vlookup before but only by itself and with 2 columns.
View 5 Replies View RelatedI am trying to create cell to calulate the total cost from a column is a pivot table. I thought i had done it but then realised that it doesn't account for dupliate suites that occur.
I thought that using VLOOKUP might work for this but not sure how to do it.
What I am looking for is to say (for pivot table sheet) if cloumn b contains arable then total cost =395, however if column b contains arable and forestry then total cost = 395, however if column b contains arable and urban then total cost = 420..... but for all possible cases.
At the moment if column b contains arable and forestry total cost = 520