VLOOKUP Formula Works But Not If Type In New Data In Cell?
Aug 23, 2013
I have a spreadsheet with a VLOOKUP formula that I have to send to a customer weekly. They are required to type a code in column E of what they invoice, and it pulls the rate into column G based on a rate table I have. The formula is: VLOOKUP(E2,Rates!A:B,2,0)
When I made the spreadsheet, the formula worked perfect. But when they type their codes in and send it back to me, it's messed up. All of the codes they've typed in do pull the correct rate. However, if I try to go to column E and type a different code on top of what they typed (a code that IS IN the rate table), it will tell me: "The value you entered is not valid. The user has restricted values that can be entered into this cell".
If I create a new tab and mimic the exact formula and columns as I am trying to work with, it works perfectly. But for some reason, once they've typed their data into my spreadsheet, something messes up even though the formula is perfect, dragged down, etc. All columns are formatted as text. And the range in the rate table is correct as well. When I try to mimic the formula in the other tab, if I click a cell in column E, there is a drop down list there showing all the available codes from my rate table to choose from. But on the spreadsheet that is messed up has no drop down list on the cells.
The HTML representation of range does not show the merging of columns L:M, N:O, Q:R and S:T on a row by row basis. Unmerging did not appear to make a difference.
I have some tabs that are color coded. What I want to do is:
1. Depending on the tab color on Sheet A, have it compare that color to a set of cell colors on Sheet B that have descriptors next to them, 2. vlookup the descriptor on Sheet B and 3. put the descriptor into cell A2 of sheet A
I have attached a sample of the issue. The formula is found in cell B49. If I enter a gravity of 2.76 or higher in cell B47 I get #N/A in cell B49. Why will it not read any farther over than 2.75. I have been starring at this for awhile now. I might look pretty dumb after someone else looks at it.
some call databases from my internal telemarketing team, which are all on Excel databases. These contain multiple contacts within the same organisation, with no "unique identifier", i.e. there is no information specific to individual records EXCEPT their email address - and unfortunately, not every contact has an email address, which would prevent using VLOOKUP, which is the only function I can use to perform this type of lookup.
I have five separate spreadsheets, plus one master database spreadsheet. I have added five columns to the end of my master database, and I would like to use each column to identify which spreadsheet(s) the individual records appear in - normally this would be possible by using a VLOOKUP in each of the five new columns, selecting a unique identifier, and using the VLOOKUP function for each separate spreasheet. However, without a consistent unique identifier, I do not know another function which would allow me to use multiple identifying info (e.g. "FirstName" + "Surname" + "Company") to perform this task.
Really simple data sheet: list of names with homerooms Lookup table: list of homerooms with teachers. (named "Range")
=VLOOKUP(E2,Range,2)
Some of the cells return the correct name, others #N/A.
As far as I can tell they are all formatted exactly the same; I've tried several different formatting options. The correct ones stay the same; the no data ones do not change either.
Changing the first row of the range to row 2, or adding the FALSE at the end makes it work. But why would it work in 2003 and not in 2007?
This is an *enormous* file, and this is just one example of a *load* of formulas that are returning errors (so far, all seem to be related to VLOOKUPs - at least the ones I've found so far). It's 25MB worth of complicated formulas, with external links to Access databases for the source data, just to give you an idea of the scope. So going through and trying to find and then change every error that might be occurring just because 2007 doesn't like it is going to be a complete disaster. You never find them all. It might be simpler to just downgrade back to 2003!
I have created a vlookup and it shows as a formula not as data. I can do 'text to columns' to correct it, but I need to drag this vlookup to lots of different cells, then change it slightly in each one.
Every time I make a change, it reverts to the formula and I have to do 'text to columns' again.
I am trying to use FIND and an array formula to find the position of text in a range of cells (A2 and A3 in the example) which could be one of a number of options (C1:D1 here). But the array formula throws up the following error: "A value used in the formula is of the wrong data type". The simplest illustration of the problem is as follows. The formula in B2 is
Code: {=FIND(($C$1:$D$1),A2)} and $C$1:$D$1 contain REF and ATM respectively. [/CODE] REF ATM 203047 05AUG 08.55 OKEHAMPTON ATM #VALUE! CO-OP GROUP 380611 REF 191 7553375222 BCC 22
We see that B2 has a #VALUE! error - wrong data type. But for some reason B3 is ok returning 22!
I receive monthly expenditure returns from different departments which I have to consolidate. The problem I'm encountering is that some departments submit their data as a monthly figure and some as the cumulative position. It would be useful if I had a formula that identified what data type was submitted and from that calculate both the monthly and cumulative figure.
If you look at the attached example I'd like to input a formula in columns I and J that uses the data contained in columns B to E to calculate the monthly and cumulative expenditure figures.
I have one column that contains an If statement formula and would like the next column to then work off of the first column (i.e. if that 1st column returns a value then then adjacent column uses that result).
What is happening now is that it is returning #value (because I guess technically the cell isn't blank?)
How can I identify a cell type at excel sheet (w/ VBA command) ? I find this command (from Access forum): http://www.thescripts.com/forum/thread601180.html but its working only with Access...
is it possible to restrict a cell in such a way that it is impossible to type data into it, but rather select data from a drop down, through validation?
I want to do a vlookup between two tabs on a date in cell A1 eg 01-Apr-08 The lookup needs to be on month and year so I'm using
=Month(A1) which gives an answer 4 in cell A2 =Year(A1) which gives an answer 2008 in cell B2 I then oncatenate the results (=A2&B2) to get a unique reference 12008 - works ok
On the second tab, I've repeated the formula, I get an answer of 1 and 2008 but when I concatenate, I get a #VALUE!
I've tried "text to columns" unchecked Protect on the cells worksheet is not protected
I have a warehouse of skids with multiple boxes of barcoded documents on each skid. The tab "Warehouse Inventory" has the range of barcodes in each box on a given skid (boxes are numbered, skids are lettered). Under the Search tab, I will be copying a list of barcodes into Column A, from another spreadsheet, and would like a macro, to autorun upon any changes made to the spreadsheet, that will do a VLOOKUP type search and reply back in Column B and C the skid letter and box number respectively
I am trying to conditionally format using AND() to reference if there is a value in the top line of a table and the title in the right most column for each cell in the table, the formula I am using works when i paste it into cells (i get the correct TRUE or FALSE for each cell in the sheet) but is not working as a format formula, I dont get an error but the shading does not happen.
But it doesn't work at all on one worksheet, half works on another, and works sometimes on another.
In the worksheet attached called workbook 4, it works a15 sheet 2, but not a10 sheet 2. But that was not always the case. In the worksheet attached justification copy, it works in a14 sheet 4 but not in a10 but for a good while it didn't work in both. In my own private doc it doesn't work in both cases.
Using Excel 2003 and am trying to do a calculation by customer, by service type. Attached is the workbook and I've tried numerous ways to do this. On the revenue calculation worksheet, I am tryng to calculate the type of service minutes from the data retrieve worksheet, multipled by the service rate on the rate table for the specifc type of minute by customer. I'd like to build it as a vlookup by customer and then by service type, but have run into difficulty.
It works great in xl2003, and it works properly in XL2007 until you try and change it. I changed the column F to Column D and now it gives me a #value.
Private Sub Zero() If Range("G8").Value = "" Then Range("G8").Value = "0" End If End Sub
I have this listed in as code for VBA. If I hit the "Run Sub/User Form" button on that screen, with the field G8 having nothing in it (having hit "Delete" just prior), it resets the field to "0".
But when I hit delete on the worksheet, the field just sits there......no zero, no nothing.
I have a nifty formula brought to me by one of the excellent members on this board. It works perfectly for what I want, but I must admit I cheated, I have a problem that was similar to the problem this formula rectified and I find myself not knowing how it works.
=IF('IIC Transfer Status'!O139="x",MIN('IIC Transfer Status'!K139+10-WEEKDAY('IIC Transfer Status'!K139-{1,3})), "" )
What Im wondering is if someone could enlighten me as to what is happening in this function, so Im not as blind to assume it is magic.
Eventually I need it to be customed tailored to work in this way: I have a column of dates, I want it to look at column A and if there is a date in it ADD 4 WEEKDAYS (business days) and return the resulting date.
i.e date in column a is march 19 plus 4 business days. Result in column B march 23.
If someone can explain how this string works that would be great, or if someone could just post a suitable function, that would work for now.
The problem i have is with the sum offset function. The formula i use is to sum up the totals for each page of an accounts document. At the top of each page there is a column with " £ p " in it and i use that as the basis of the formula. This formula is entered in the bottom of every page to calculate the total for each page.
This works all the time but when text is entered into column F (the column i'm adding up) it messes up with the formula. the formula will instead find the £ p but for a page 2/3 pages before the one i'm calculating on. This formula only faults when text is entered. Another thing; this excel sheet i'm working on is an output from a different computer program. it is outputted as csv file i think.
Like i said the formula works a treat except when text is entered. The obvious way around this not to enter text but that is not an ideal option. I cannot think of another way of calculating the total for each page although i'm sure another exists.
I'm having some trouble trying to get excel to input a formula into a cell. I'm still a novice at VBA right now, so I don't think my problem will be too much of a brain buster.
I want a formula in Cell A6 (and I already know it correctly works) in this format: =E6&VLOOKUP(I6,'FA-Fund Data'!B$1:C$2000,2,FALSE)&J6
I figured out exactly what I wanted to do and got it to work in a test excel sheet. However, when integrating it into the actual workbook I wanted, I was unable to get it to work. So, I used the same cell references I need to work in my actual workbook and pasted it all back to the test excel sheet.
So, the test excel sheet has the working formulas up at the top, and a duplicate of what I need to work in the actual cells I need them to work in. Changing the shift start time should group any persons with the same shift togethor. I'm still fairly new, but I think the only portion that could have been changed is the portion that says 1:1.. I figure that is relative to the array so it shouldn't be changed, but not sure what else to do.
Here's an array formula that works:
[Code]....
Here's the array formula to be in the correct cells that doesn't work:
[Code] ....
How to to get it to work in the different cell area.
