I have a spreadsheet with members details in and I've added a few columns showing:
Col Q: Paid 2008 (blank if No, 1 if Yes)
Col R: Show Paid Yes or No (shows a Yes or a No)
Col S: Date Joined (only entered if new and joined during 2008)
Col Z: Subs owing from 2008 (if existing members and owe last yr's subs)
Col AA: Subs to Pay in 2009 (a standard £35 or pro rata if joined during 2008)
Col AB: Pro Rata (if they joined other than at the beginning of 2008)
Col AC: EA Sub? (based on a yes/no column, to add £5 if yes)
Col AD: Total to Pay for 2009
So the formulae (or data entered) for the above columns are as follows:
Col Q: Paid 2008 - blank if no, 1 if Yes
Col R: Show Paid Yes or No - =IF(Q2=1,"Yes","No")
Col S: Date Joined - a date entered in format d/m/yy
Col Z: Subs owing from 2008 - =IF(R2="no", 35,0)....I want a default 35 in here
Col AA: Subs to Pay in 2009 - =IF(S2="",35,"Pro Rata")....says that if there is no date which indicates they are an existing member, they will pay the standard 35, otherwise they'll be paying a pro rata fee...................
It all works fine until a certain row (27 actually) - then I get a VALUE! error which points to the Date Joined field. Now I've tested that value against the vlookup table and it returns the correct data, so why doesn't it do it in my spreadsheet????? I've tried changing the formats on the cells; clearing all data; entering a different date, but from that row in the spreadsheet down to the last row, it simply won't work! I've looked at each formula in each cell on the rows where it is working and the rows where it isn't and I can't see anything different.
What else can I look for? It is SO frustrating as it does what I need it to to do but only for a third of the spreadsheet!!!!
The attached workbook contains a macro ("Transfer_Data") that copies values from one cell to another when the "Update Progress" button (near cell A3) is selected. It works perfectly fine in this workbook but when I try the same code in another workbook, which looks and operates exactly like this one, nothing happens.
Is this due to a security setting or missing code line items?
As I understand it hyperlinks set in a spreadsheet should work regardless of where you save and/or copy the spreadsheet i.e. as long as the directory location of the location / file the hyperlink is referencing doesn't change (notwithstanding, the user must have appropriate access rights to the relevant directory locations)
However, the situation I have is the spreadsheet was generated and saved to directory location A (intention being this would be the 'master' yearly template) and the hyperlinks set, all of which work when you open the spreadsheet in this directory location. But when I copy the spreadsheet to directory location B, which is intended to be the yearly 'register' location for the spreadsheet, none of the hyperlinks work? When I hold my cursor over a link in the copied spreadsheet, in directory B, I do not get a 'pop-up' path like I do in directory A and when I click a link I get an error message:
Microsoft Excel The address of this site is not valid. Check the address and try again.
As stated above the documents the hyperlinks relate to have not moved, they remain in the same template directory (i.e. the link path has not altered).
I have a vlookup that returns an #N/A error due to the table array originally being formatted as text. The data is simply a two or three digit number.
I have tried everything I can think of to get the table array to equal the lookup value.
I formated cell to number. I tried tab delimated/finish. I tried typing a 1 in a different cell and doing a paste special then multiply. I tried copying to a different worksheet. I just cannot get it to work.
VLOOKUP will only work to a set limit number of text characters in a cell. So I can search for the value "*orange*" if the cells contain a reasonable amount of text, but as soon as this exceeds some limit, it returns BLANK. I've also tried using INDEX/MATCH with same problem. Is there a workaround or alternative function?
This will work and return the text in cell A2 A2: The quick brown fox jumps over the lazy dog. Orange. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog
This will return BLANK A2: The quick brown fox jumps over the lazy dog. Orange. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog. The quick brown fox jumps over the lazy dog
I have two worksheets and VLookup is not working between the two worksheets. I made sure that type of text is same . COntent is trimmed to avoid any blank spaces. If i put the formula for the same sheet range i get the value but for another worksheet i m getting #NA error.
I use Vlookup all the time but this is one has be stumped. On the attached spreadsheet I trying to lookup the description on sheet 2 (lookup data) and return it to description on sheet 1 (orig data). I have the both fields formatted as text so I'm not sure why it's not returning the value?
I have the following formula that has ceased to work in excel. I don't think there are any errors in it so I'm unsure as to why the link no longer works. Neither of the files I'm using has moved location on the server.
I have 3 worksheets for 3 separate managers, each sheet has a column which uses a COUNTBLANK function to work out the number of days off an employee has had off in the last two weeks:
Now the formula works perfectly on each worksheet, however I also have a master worksheet (which is identically set out and formatted to the 3 manager sheets) which uses a vlookup formula to pull the data from all 3 worksheets into one at the beginning of the workbook.
The problem I've come across is that the COUNTBLANK formula doesn't work on the master worksheet - I think this has something to do with the vlookup formula entered into the cells which I consider to be blank, but excel doesn't?
I've tried using a separate vlookup formula to pull the data from the 3 managers worksheets and this does work unless an employee appears on more than one managers' sheet - then the data from both worksheets is added together.....
Is there another function I could use to display the information I need to?
I have a column of numbers I want to look up, and a column of results to the right I want to spit out. So I have the vlookup formula in column G, H a copy of that, except it's showing the formula instead of result. Same thing with I and J. So you can see the actual formula.
I've manually found the first two numbers and colored them red and green... but I can't seem to get the formula to work.. As you can see, it's only able to find the negative numbers, but not any of the positive numbers.
I tried to test the cells if they are equal or not, and they seem to be equal!
The IF statement is designed to check a different value in the source spreadsheet against one in the destination sheet which should always match for the desired values, but this doesn't seem to make any difference to what is being returned - I'm still getting the first match instead of the appropriate one.
Is there a better way to force the VLOOKUP to return the value from the correct match and not just the first one?
I'm trying to compare two very large files to find some that are in one AND also in the other. I'm using =VLOOKUP(C2,Parts2!C:D,1,FALSE) and my results are #N/A for all of them. I have formatted both columns as 'General' because there can be letters and numbers in the serial number, I have also tried formatting as text to no avail. I have confirmed no preceding or trailing spaces or other special characters.
I am however, able to locate values in both documents using Ctrl F. I'm totally puzzled because I've always been able to figure out what's causing my Vlookup issues when I have them but this time I'm not able to.
I am trying to get this tricky formula to work with vlookup and if error. The problem is that excel will not recognize the formula when I try to drag it down, but instead repeats the same numbers over again. I've tried some combinations using vlookup, rows, and if error, but it doesn't seem to work. So I'm trying to drag down the formula from g3:g99, to reference either cells b8:99, or whichever value I enter into f1, on down to b99.
I am trying to use a =vlookup(and( type formula. Excel accepted my syntax, but the formula is not working. Here are the details:
=VLOOKUP(AND(E2,O2),'[SOURCE DATA FOR PROFILES.xlsx]Sheet1'!$B:$G,6,FALSE)
The values from cell E2 and O2 are both present on a row in the SOURCE DATA spreadsheet, and column 6 is the last column in my table (both values appear before column 6, but not next to each other).
The formula returns #N/A. Under those circumstances, shouldn't it return the value of column 6 on the row where those two values are present together?
Do the columns containing the values have to be next to each other in the table for this to work? Does the data type matter (one is a number, the other a street address)? The value to be returned is a number.
I am trying to create a single cell formula that returns the min value of an array returned from a vlookup function. This is part of a more complex solution that I am trying to implement, but I think I have narrowed my problem down to this issue, so I have created a very simple example to demonstrate.
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 a sheet set up with names. I have 2 other sheets with spouses and children that belong to the first sheet. I want to insert a formula that will look up the correct spouse (based on a member number) and put in it the column. Same with the children. I tried the vlookup function, but it did not return the correct result.
Our company has a shipping report spreadsheet that has the job number in column A and the ship date in column F. I want to track other things on a separate spreadsheet. So far, we are only down to row 1440 so in order to account for future jobs, my code on the other spreadsheet is:
Also, the jobs are arranged by ship date, not by number order.
This has worked perfectly for months but for some reason, it will no longer recognize job numbers greater than 1331. The format is the same between the working and non-working cell. Is there a max limit for vlookups that I am not aware of? If this was a sudden failure, I could nail it down but there has to be an underlying reason I'm not seeing.
Here is a sample of my spreadsheet: Ship Date 1325 12/27/13 1326 3/10/14 1327 1/4/14 1328 1/31/14 1329 3/3/14 1330 1/22/14 1331 1/15/14 1332 #N/A 1333 #N/A 1334 #N/A 1335 #N/A 1336 #N/A 1337 #N/A 1338 #N/A 1339 #N/A 1340 #N/A
I have an Excel 2003 situation I cannot resolve. In one workbook, I have a table of multiplication factors based on a numerical general ledger code. On another sheet, I have a data extract including that code and a cost. In the latter sheet, I need to multiply cost by the appropriate factor based on the GL code - IF Code=X, then cost*factor.
Should be simple, but I have too many codes for a nested IF statement. I also tried a VLOOKUP, but since the codes are numerical, Excel interprets them to be in the thousands, while my factors are 1.xxxxxxx, and since the data being looked up thereby appears smaller than the keys, VLOOKUP will only return #N/A or ?NAME, depending on whether I try to nest it in an IF or not. I also have several thousand records per month to which this needs to be applied, so going manual is not an option.
The calculating is done in column E, the original cost is in column AB, the key (GL code) is in column R, and the GL code range with factors is on the !Factors sheet (column A=Code, B=Description, C=factor).