Hello, Does anybody know if it is possible to use VLOOKUP on a range of cell that have been filtered such that only the visible universe of filtere cells forms the source for the VLOOKUP? Let's say I have 10 data items in a column and my filter give datapoints 1, 3, and 7 (with the rest in the hidden rows in filtere mode). I wish to VLOOKUP only from datapoints 1,3,7 (since they meet th criteria I am interested in) and return null values for othe datapoints (2, 4, 5,6,8,9,10) since these don't meet the filte criteria I'm interested in. Obviously, I could separate out everythin...

I have the following data and need to extract only the data where the value = false The true / false is Column D with Column H A B C D E F G H 1227 1164 Prog03 ST9 1164 Prog03 ST9 TRUE 1228 1277 Prog02 ST7 1277 Prog02 ST7 TRUE 1229 1166 Prog03 ST9 1166 Prog02 ST7 FALSE 1230 1278 Prog02 ST7 1278 Prog02 ST7 TRUE 1231 1279 Prog05 STE 1279 Prog02 ST7 FALSE 1232 1280 Prog01 ST8 1280 Prog01 ST8 TRUE 1233 1281 Prog02 ST7 1281 Prog02 ST7 TRUE 1234 1282 Prog02 ST7 1282 Prog02 ST7 TRUE 1235 1175...

Hi, I am having difficulty formatting a cell which contains both text and a VLOOKUP formulae. I have an example of the cell below: ="Here lies the text before Vlookup " &VLOOKUP($M$20,sheet1!$B$10:$L$1445,8,FALSE)&" Here lies follow-up text" The VLookup should return a date but instead is returning a string of numbers. I'm sure there is a way of setting the format within the formulae using dd/mm/yy somewhere. Could anyone tell me how to do this? ...

Is it possible to set a VLOOKUP to retrieve values using a Date, then setting the date cell to =NOW(), then retain those values after the date. For example, I have a workbook that is used to keep track of arrivel times at a set location. Every sheet in the wookbook is the same location, one sheet for each day of the month. Column B has the vehicle numbers use on a specific day. Each day the drivers use a different vehicle. The vehicles are entered in a feeder sheet each day in a different workbook. My idea is to have column B of the location workbook retrieve the vehicle num...

I have this formula to look up a date in column A and then display what is in E. =VLOOKUP(TODAY(),Region!$A:$S,5,FALSE) Works fine if I only have one field for that date. Column A = Date Column B = Time How do I get it to display the most recent value in column E? if I have 2 date the same in column A Is your data sorted or grouped together so that all dates/times are in a contiguous range: 6/8/2009...10:00 AM 6/8/2009...10:45 AM 6/8/2009...12:00 PM -- Biff Microsoft Excel MVP "Steve" <ball.steve@gmail.com> wrote in message news:911e2c47-957b-4278-b5f6-736d7547fa51@...

Hello, I'm using the following Vlookup formula: =IF(ISNA(VLOOKUP(A4,'S:\Operations\Bloomberg\Investment Support\Dummies2Live\2010\May 10\[04052010.xls]Sheet1'!$A$4:$F$600,6,0)),"PRINT",0) This is linked to another spreadsheet - in this case 04052010.xls. Next day I will need to check file 05052010.xls and so on. To make life easier for my colleagues I wanted to add a cell with date where they can input the date and this will change the date in the formula (i.e. from 04052010 to 05052010) How can I link this formula to let's say cell A1 where I want to insert da...

I thought this was an easy problem, but: I have the following data, A1 B1 C1 D1 12 1 64 =IF(ISERROR(VLOOKUP(C1,$A$1:$B$12,2)),"",VLOOKUP(C1,$A$1:$B$12,2)) 14 1 14 1 21 1 22 2 43 2 20 2 32 2 25 3 33 3 37 3 67 3 The formula is giving me 3, but I want it to be blank. What did I do wrong? Thanks in advance. Hi vlookup actually has four parameters, the fourth is whether or not you want it to do an approximate match. If you omit the fourth parameter or leave it blank (which is w...

OK. I used validation to create a dropdown list of people's names on spreadsheet. My problem is that I need to link the people's names t bring data into a ceartian selection of cells. I Have been looking fo vlookup tips, but I'm afraid that won't work. Please help me!(I gues I need to make sure the old values clear out when I hit 'Clear' Too -- Message posted from http://www.ExcelForum.com Hi not really sure what you issue is but see http://www.mvps.org/dmcritchie/excel/vlookup.htm for some information about VLOOKUP -- Regards Frank Kabel Frankfurt, Germany &qu...

Greetings ! I have this in a Cell : =VLOOKUP(AL14,[LELTACS.xls]Ali2!$B$28:$B$77,1) But I want to replace the "77" with the value in Cell BD2 of the current sheet. Help ! RClay AT haswell DOT com Hi Robin try =VLOOKUP(AL14,INDIRECT("'[LELTACS.xls]Ali2'!$B$28:$B$" & BD2),1) note: works only if leltacs.xls is open! Frank Robin Clay wrote: > Greetings ! > > > I have this in a Cell : > > =VLOOKUP(AL14,[LELTACS.xls]Ali2!$B$28:$B$77,1) > > But I want to replace the "77" with the value in Cell BD2 > of the current s...

Hi there, I have a table with the hours that employees have worked (as shown below)and I need to sum all hours. There are numerous entries for each employee and so I need a formula that will look for the name in column a and then I need to sum all hours entered in column B next to that name. Please advise :-) Name B C D adelle 22.50 16.00 1.00 Alice 82.25 79.25 0.00 Alice 19.75 18.75 0.00 Alice 19.75 18.75 0.00 Aqsa 35.00 32.25 0.00 Chen 49.75 49.25 1.00 Chen 19.25 17.75 0.00 Try =SUMIF(A:A,"adelle",B:B) If this pos...

in cell h13, i want to use vlookup to figure out the price of a ticket. once i find the price of the ticket in the chart, i have to multiply it by b7. any help???? b7 is nbr of tickets b8 is ticket series b9 is the location of the seat cells a15:c20 is the price chart example series level 1 level 2 a $10 $5 b $15 $10 Fran The following will work if A15:C15 are headers in your pricing matrix, if not change the reference in the MATCH function. The IF(ISERROR...) is only added to stop #N/A errors, you can get rid of that if you're not bo...

-- TraciAnn In article <1DA3EB20-5F5D-4CA3-BBED-C42E3213BCD9@microsoft.com>, TraciAnn <TraciAnn@discussions.microsoft.com> wrote: If you're looking for an exact match, range lookup (the 4th argument of the VLOOKUP function) needs to be set at FALSE or 0. If you're looking for an approximate match, the values in the first column of the table array need to be sorted in ascending order. -- Domenic http://www.xl-central.com "Domenic" <someone@nospam.com> wrote in message news:someone-95A967.11320906042009@msnews.microsoft.com... | In article <1DA3...

When searching for Corvette, I want VLOOKUP to return all of the data t the right of the word Corvette. Traditional VLOOKUP formulas ar designed to only return 1 data value say " 2" cells to the right o however many to the right - but still only 1 cell is returned. I wan to return all 5 values to the right of the cell with the wor "Corvette". If Corvette is in cell A1. I want the data in cell A2, A3, A4, A5, A to be returned and entered into their own cells. I think it involve Columns(), but have no idea how it's used. Thank you -- Message posted from http://www....

(excel 2003) I have a data list to search and copy from - it's approx 150 rows x 12 clmns. I need to copy the data (entire row is fine, but only need 8 out of 12 clmns) based on the "Location" . Within "Location" I have 8 different solutions {ex "R1" for room 1, "R2" room 2, and so on} I only need to copy rows that have a "Location". I need to keep a link back to this master workbook, and would like to have this new workbook I'm creating to either auto update or update by a simple macro. Once I have the data cop...

I am using VLOOKUP to search through a db that has formatting on individual cells (text color, subscript, superscript, etc.). The result from a successful VLOOKUP is in place text. Is there any way to have the lookup cell automatically match the formatting of the reference cell? A formula can only return a value to a cell, so I say the answer is no. HTH Regards, Howard "kalandine" <kalandine@discussions.microsoft.com> wrote in message news:486780D2-C14B-47BE-A7D6-71D6622E4DD3@microsoft.com... >I am using VLOOKUP to search through a db that has formatting...

I have a little database which arrives periodically, and I would like to chart the number of members by nation. I can't recall ever having this opportunity before. The nations are standard ISO codes (UA, UK, US, UY) and not numeric. Is there a way to organize these entries somewhat automatically for charting? Alternately, I wondered if I filtered the nation column, is there a way to export all the unique entries, preparatory to counting this by hand (so to speak) . . . Any advice would be gratefully appreciated. -- Regards, P D Sterling New York, Texas & Texas, New York PD,...

Hi all... I'm sure tihs is a simple VLOOKUP problem, but I can't seem to figure out the issue. I have a lookup table of Nation abbreviations and the corresponding full name: IN India IO British Indian Ocean Terr IP Clipperton Island IR Iran IS Israel IT Italy IV Cote D'Ivoire IZ Iraq On a separate page, I have a listing of nations for people in a program (by CODE). I want to use VLOOKUP to take the coded country and return the nation name. So, A B C IT [formula] Italy I entered the following formula: =VLOOKUP(A1,NationLookup,2) The formula...

Hello - newbie poster here. I've been scouring this group during my current project and found much useful information - thanks to all. However I've come to a halt and need to ask for help on a specific question. I have a spreadsheet working out the Rugby Zurich Premiership scores (LOTS of formulae entering!). The ultimate is to display an automatically calculating league table from the datasheet. I've used the LARGE function to order the points for each team and VLOOKUP to pull in the corresponding team name, games won, lost, drawn, points for and against and the goal differen...

Hi, I have a worksheet called Data_dump which contains my students results for their last 6 exams. I then have another worksheet called action_plan which has a drop down box which shows all the students names. When they select their name, I use vlookup to generate a graph of their performance from the information in Data_dump. At the bottom of this page is cell (C9) for them to enter their comments about how they plan to improve their performance. I have 2 questions..... 1. The students can type their plan for improvement into cell C9 & then printout the page, but I can't find a way...

I'm making a dashboard type report and have my first chart linked to: DataRange=E!$C$14:$G$21 I copied the chart and want to do is change the E to a G to reference my other data sheet. Problem is that after I hit "ok" it changes =G!$C$14:$G$21 to ='G'!$C$14:$G$21 with the single apostrophes. Because of that, it thinks the entire range is a data label. This used to work but now it doesn't so I think I must have changed some kind of setting somewhere. Any help would be greatly appreciated. Thanks, Mike Zz Even with the single quotes, Excel should recognize ...

Hi I worked on print.Everythings working perfectly except one problem.ie. When i set the PageRange option to 'ALL' option and press print button..instead of displaying only the pages which has information ..its displaying that all pages from 1 to 65535 r going to be printed. How do i remove this problem? thanks, vani I thing you may failed to set the maximum number of pages to print. Check CPrintInfo::SetMaxPage(nMaxPage); method regds Jibesh -- ------------------------------------------------------------------------- FIGHT BACK AGAINST SPAM! Download Spam Inspecto...

Hi, I am struggling to find a solution to match my requirements & would appreciate it if somebody can help me out. I have a spreadsheet with just under 4500 lines of information. For each line, there are around 10 entries (i.e.... 'description', 'shipment number', 'order number', 'cost per item', 'supplier', 'shipment method' etc). I have a second spreadsheet which contains a completely different set of information with the only common reference between the two spreadsheets being the 'order number'. I am looking to do a ...

new to the site, so just wanna say hi to everyone first! how u all doin hope allot beter then me. you see ive got my alevel project to hand i , and i need some serious help with a vlookup. hope someone ca help.need someone to help! basically i want a vlook up that looks up from 2 sheets. this may soun simple but read on.! currently my vlookup formula looks like this: =VLOOKUP(A12,'Warehouse Products'!A14:B18,2,0) so it is looking up the information from the warehouse product sheet however i also want it to look not only from the warehouse sheet bu also the delivery sheet. so if met...

hi my problem here is that i get this #NA thing anytime i select a product code more than twice. data is formatted as tables in the lookup sheet with 757 rows. my other sheet is just 5 rows but i have used data validation liminting the entry to the codes. but i have realised that a few minutes a go that the #NA keep coming up but when i choose a different code it gives me the the product name. if choose lCQ 1 LCQ 2 LCQ 3 this gets me #NA but as soon as that is changed i get a name any reason for that please. I am not sure I understood your query correctly. If you want t...

I have a VLOOKUP formula in Excel 2000 that is doing what I want it to do, but I don't know that it's going to work correctly for the whole project. The short of what i am trying to do is i have a sheet that has 2 columns. the first is a vendor ID and the second is a percentage that we take off of retail price from that vendor. Basically what i would like to do is on another worksheet, be able to enter a vendor ID that will then lookup their discount percentage and display it in the cell next to it. i then do various calculations from there. this works perfectly if i only enter one ven...