Vlookup and offset
Hi, I would like to lookup a users ID in a table and check if they have a paticular qualification, a normal VLOOKUP will do that but then I would like to refer to a date in the header to see when they gained the qualification so the cell can determine whether they are qualified based on the current date. I could do this without checking the date but that would disguise the lack of skills for the rest of the past records as soon as the skill was recorded. I think I need to combine an IF, VLOOKUP and an OFFSET but am at a loss as to how to put it all together. Hope someone can help. Thanks in anticipation. Don
d.hart440
excel
39879 articles. 
2 followers.
3 Replies
654 Views
Would an "AND" function do it? S "DonH" <d.hart440@ntlworld.com> wrote in message news:lbl_g.4852$t6.841@newsfe4-win.ntli.net... | Hi, | | I would like to lookup a users ID in a table and check if they have a | paticular qualification, a normal VLOOKUP will do that but then I would like | to refer to a date in the header to see when they gained the qualification | so the cell can determine whether they are qualified based on the current | date. I could do this without checking the date but that would disguise the | lack of skills for the rest of the past records as soon as the skill was | recorded. | | I think I need to combine an IF, VLOOKUP and an OFFSET but am at a loss as | to how to put it all together. | | Hope someone can help. | | Thanks in anticipation. | | Don | |
scott_orchard_REMOVETHISHi Don Assuming your reference date was in cell A1 perhaps something like the following =IF($A$1-VLOOKUP(ID,Table,offset,0)<x_days,"Valid","Invalid") I am assuming the value in the Table is the date of their qualification. If whether they hold a particular qualification is held in one column of the table, and date of acquiring the qualification is held in the next column, you need only concern yourself with the date column, since if they don't hold the qualification then presumable the date cell would be blank. If date is blank, Excel will interpret that as 01/01/1900 and when taken away from your reference date would give a huge value which would cause an "Invalid" outcome. -- Regards Roger Govier "DonH" <d.hart440@ntlworld.com> wrote in message news:lbl_g.4852$t6.841@newsfe4-win.ntli.net... > Hi, > > I would like to lookup a users ID in a table and check if they have a > paticular qualification, a normal VLOOKUP will do that but then I > would like to refer to a date in the header to see when they gained > the qualification so the cell can determine whether they are qualified > based on the current date. I could do this without checking the date > but that would disguise the lack of skills for the rest of the past > records as soon as the skill was recorded. > > I think I need to combine an IF, VLOOKUP and an OFFSET but am at a > loss as to how to put it all together. > > Hope someone can help. > > Thanks in anticipation. > > Don >
roger5293Many thanks for your reply I'll give it a go. Regards DonH "Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message news:ueQaDxQ9GHA.3740@TK2MSFTNGP05.phx.gbl... > Hi Don > > Assuming your reference date was in cell A1 > perhaps something like the following > =IF($A$1-VLOOKUP(ID,Table,offset,0)<x_days,"Valid","Invalid") > > I am assuming the value in the Table is the date of their qualification. > If whether they hold a particular qualification is held in one column of > the table, and date of acquiring the qualification is held in the next > column, you need only concern yourself with the date column, since if they > don't hold the qualification then presumable the date cell would be blank. > > If date is blank, Excel will interpret that as 01/01/1900 and when taken > away from your reference date would give a huge value which would cause an > "Invalid" outcome. > > -- > Regards > > Roger Govier > > > "DonH" <d.hart440@ntlworld.com> wrote in message > news:lbl_g.4852$t6.841@newsfe4-win.ntli.net... >> Hi, >> >> I would like to lookup a users ID in a table and check if they have a >> paticular qualification, a normal VLOOKUP will do that but then I would >> like to refer to a date in the header to see when they gained the >> qualification so the cell can determine whether they are qualified based >> on the current date. I could do this without checking the date but that >> would disguise the lack of skills for the rest of the past records as >> soon as the skill was recorded. >> >> I think I need to combine an IF, VLOOKUP and an OFFSET but am at a loss >> as to how to put it all together. >> >> Hope someone can help. >> >> Thanks in anticipation. >> >> Don >> > >
Similar Artilces:
vlookup
I have 2 excel sheets. The company name field in both differ in most cases. currently I do a vlookup like this =VLOOKUP(AH5033,companydetails_company.xls!M$2:M$1491,1,FALSE) For example on the first excel sheet I will have: "Greenberg Ltd" and then on the other sheet I might have "Greenberg Ltd." or "UK Greenberg" Is there something called a best match? Its a once off thing I need to do so efficency is not an issue. I'd really appreciate the help with this one. there is no artificial intelligence built into the function, but you can possibly use som...
combobox and vlookups?
I am trying to add a combobox that when you select from the dropdown menu, the columns nextdoor automatically pull up corresponding data that is related to the selection from the dropdown list. Does this make sense? What do I do to set this whole thing up? (I don't know code). Thanks. -- Message posted via http://www.officekb.com You shouldn't need code for anything you described here. Set up the combobox with the list fill range, if any. Set the linked cell to the desired cell. Then, use the VLOOKUP command in another cell to look for the linked cell from the combo box to lo...
offset/macro problem
I want to hide certain rows given a certain value. I.E. if a user enters a value of 3 securities, I want to hide rows 14-40. If a user enters a value of 6 securities, I want to hide rows 17-40. I have tried to write a macro using the offset button, but cannot get it to work. Any help? See attached file. Thanks. Sub HideRows() ' ' ' Rows.Offset("G4+11:40").Select Range("B14").Activate Selection.EntireRow.Hidden = True Range("B3").Select End Sub Attachment filename: loop.macro.problem.xls Download attachment: http://www.exce...
VLOOKUP and sum
I have a list of project numbers, their monthly sales, and, 5 categories the projects fall into. For each month, I need to associate the project numbers with the five categories, and, sum the monthly sales for each category. (i.e. be able to paste the row of project numbers and associated sales into a spreadsheet and be able to produce the monthly sales of each of the 5 categories e.g. project # category <4000 S 4101 O 4102 C 4103 G 4104 I 4105 G 4106 C 4107 O 4108 O 41...
ComboBox linkedcell population not recognized by Vlookup
I have created a worsheet in which I have Vlookup formulas performing calculations. I then created Combobox drop lists and linked given cells. Everything is working properly except the combobox populating the linkedcell is not being recognized by my Vlookup functions. Has anyone ran into this problem? If so can you tell me how to fix. Thanks Ed Hi you're probably searching for a number. If yes it could be that the value returned from your combo box is stored as 'Text' (though it represents a number). If you current VLOOKUP formula looks like =VLOOKUP(A1,'lookup'!A1:B...
Vlookup #20
Hi- I am using vlookup where the lookup table is in another file, I have the file name in a cell of the sheet, please let me know how to refer to the file name and range name in the vlookup function by using the cell address that contains the file name. One way: =VLOOKUP(A1,INDIRECT("'[" & A2 & "]Sheet1'!A:B"),2,FALSE) However, for INDIRECT, the file must be open or you'll get a #Ref! error. To work with closed workbooks, you could use the INDIRECT.EXT function in Laurent Longre's MOREFUNC.xll add-in: http://xcell05.free.fr/ In ...
Vlookup hyperlink
Dear All I am using Office 97 :( And want to try to do a vlookup but the value I want to retrieve is a hyperlink, when doing this it returns the text but the hyperlink doesn't work Is there anyway this can be done Thank Adam Hi Adam try something like =HYPERLINK(VLOOKUP(....)) -- Regards Frank Kabel Frankfurt, Germany Adam wrote: > Dear All, > > I am using Office 97 :( And want to try to do a vlookup but the value > I want to retrieve is a hyperlink, when doing this it returns the > text but the hyperlink doesn't work. > > Is there anyway this can be done? ...
Vlookup in vba
Hi I have the following snippet of code: Sheets("mdata").Select Range("E1").Select ActiveCell.FormulaR1C1 = "RVU" Range("E2").Select ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-1],rvu!R[-1]C[-4]:R[7238]C[-3],2)),0,VLOOKUP(RC[-1],rvu!R[-1]C[-4]:R[7238]C[-3],2))" Range("E2").Select Selection.AutoFill Destination:=Range("E2:E" & LastRow), Type:=xlFillDefault This fills about 5,000 rows with look up data from the rvu ws, and puts in 0 if #N/A is obtained. HOWEVER, if I was ...
Vlookup #33
In my spreadsheet i have a worksheet(1) with three columns. A B C data1 data1 data1 data2 data2 data2 I would like to use the VLOOKUP function to bring data from column c into another worksheet(2), when data entered into worksheet(2),columns A and B, matches data with worksheet(1) Is this possible? Please advise Paul =INDEX(Sheet1!C1:C1000,MATCH(1,(Sheet2!A2=Sheet1!A1:A1000)*(Sheet2!B2=Sheet1 !B1:B1000),0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- ...
Vlookups, IF's..and Oh No!
There are three sheets. Customer Service Roster, Customer Servic Timesheet and Master Data. Sheet *"Customer Service Roster" * It is split into five sections, one for each day (Mon-Fri). You clic on a cell under the date and it brings up a list to select. Once yo select the option, the start time appears on the left of it. Prett simple. A5=IF(ISNA((VLOOKUP(B5,'Maste Data'!J79:L106,3,0))),0,(VLOOKUP(B5,'Master Data'!J79:L106,3,0))) B5=Validation List Sheet *"Customer Service Timesheet"* This has the employees listed on A6:A20. Then it has to the ri...
VLOOKUP problem #4
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 ...
VLOOKUP for ZIP codes??
Hi all. I work for a college and we've divided up the country, by zip code, into many many many different regions. What I have is a worksheet in excel that looks like this 'MinZip' 'MaxZip' Region 75000 75899 DALLAS 76000 76899 DALLAS Where MINZIP s the beginning number of a ZIPCode range for a particular region, and MAX is the terminating zip code. On a separate form, I have the listing of our applicants and their zip codes. I was to create a lookup formula that says (in english): If REALZIP is between MINZIP and MAXZIP on row one, re...
vlookups sheet is too big
Group, I created a sheet a while back with Vlookups all over the sheet which is currently 14 Megs im trying to get the sheet down to a reasonable size. Does anybody know of a way to do this. Thanks in advance, Fish Fish Perhaps Excel estimates your actual range of data as far larger than it really is. To reset the "used range" http://www.contextures.on.ca/xlfaqApp.html#Unused Gord Dibben Excel MVP On Mon, 8 Nov 2004 09:34:17 -0800, "Fish" <anonymous@discussions.microsoft.com> wrote: >Group, > >I created a sheet a while back with Vlookups all over...
Vlookup icw =product()
Hi, Can someone tell me which formula to use for the following command: I would like to lookup a certain name in another sheet and subsequently take the PRODUCT of the range of the numbers stated after this name. For example: find RNNF in sheet1 (column A)....say this is cell A1....... and take =PRODUCT() of values in B1:Z1 I tried =VLOOKUP(RNNF,'Performance Funds'! A1:A31,PRODUCT(1+'Performance Funds'!B:Z)-1,0) but no luck;-( Thanks a lot!!!! Rgds, Robert Hi Robert, =PRODUCT(INDIRECT("B"&MATCH("RNNF",A1:A3,0)&":Z"&MATCH("R...
Vlookup with Reference cell from Sharpoint
Hi, I have a cell (Ex : Emp ID) in a XL sheet, whenever i enter an ID, it should go to Web Sharepoint folder and refer the Emp ID with the Global XL Sheet and returns the value (ex : Employee's Name) How is it possible ? ...
Integration Manager empty Inventory Offset Posting Account Setup
Dear Support: We are using the Integration Manager importing into a Inventory Transaction from a Source file. However, are forced to enter a Inventory Offset Account into the Tools >> Setup >> Posting >> Posting Account. Without it the error is given: 'The Inventory Offset posting account is missing for Item number 000000'. The source file has an offset account, but it is actually only "missing" from the Posting Account setup. Therefore, we would like to suggest that this restriction is removed. The reason for this is: We do not want anything account ...
VLOOKUP ?
I use the VLOOKUP function a lot but was wondering if it were possible to use this when there are more than one cell I want to separately use data from. Currently I have: =VLOOKUP(B1,Sheet3!$A$1:$B$65,2,FALSE) but what I can't seem to do is then take the data from the $C$ column, thus avoiding the $B$ column =VLOOKUP(B1,Sheet3!$A$1:$C$65,2,FALSE) ~ doesn't work as this includes column B. What I have to do at the moment is copy/paste the columns 'C' and 'D' from the 1st sheet to two new sheets so the data is in column B Many thanks Keith (Southend) =VLOOKUP(B1,Sheet3...
VLOOKUP with VB
Hi, I have the current code and would like help with two queestions: 1) is it possible to return the value and not the formula 2) Is it possible to enter the formula in all the cells without using copy down 'Copy EIF Numbers to NB sheet Sheets("NB").Range("P3") = "EIF" Sheets("NB").Range("O3").Copy Sheets("NB").Range("P3").PasteSpecial Paste:=xlPasteFormats Sheets("NB").Range("P4").FormulaR1C1 = "=VLOOKUP(NB!RC[-15]&RC[-14],EIF!C[-15]:C[-9],7,FALSE)" LastRow = Sheets...
help on a vlookup please
Hi there i need some help on a vlookup, I just can't seem to get it to work. in sheet 1, B2 I have a name joe blow in sheet 1, A2 I would like their street number (on address sheet) this is what I have tried and keep getting #N/A or blank =IF($B2>0,VLOOKUP($B2,address sheet!$A$1:$O$139,1,FALSE )," ") address sheet colmn A has the street number address sheet column B has their name street numbers are formated as numbers any ideas? regards Ditchy Ballarat, Australia Hi, Try this =IF($B2>0,VLOOKUP($B2,'address sheet'!$A$1:$O$139,2,FALSE ),"...
VLOOKUP returns #N/A but want a blank instead
I want to have a blank field return instead of #N/A when my VLOOKUP is false. How would I change my current formula to do that? Here is the formula currently used: =VLOOKUP(A10,DC$7:DH$801,4,1)*D10 Any help would be appreciated. Hi try =IF(ISNA(VLOOKUP(A10,DC$7:DH$801,4,1)),"",VLOOKUP(A10,DC$7:DH$801,4,1)* D10) -- Regards Frank Kabel Frankfurt, Germany "Golf Club" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:584f01c42d4d$5ec3f9d0$a601280a@phx.gbl... > I want to have a blank field return instead of #N/A when > my VLOOKUP is false. ...
VLOOKUP numbers as #N/A
Hi Having a few issues with VLOOKUP, basically if Part Numbers (ColumnD) contains text and numbers it is okay, hoever, rows that just contain numbers only or numbers and slashes or dots are showing as #N/A in ColumnE, have searched on here (http://www.contextures.com/ xlFunctions02.html) and tried looking at following but does not seem to work. any help appreciated!!! Here is formula currently: =VLOOKUP(D2,CommodityCode,4) 'CommodityCode' is named Range on another sheet in same workbook Here are results for column D and E respectively: Part Number Commodity GW Sma...
defined name
Hi, My spreadsheet has data from B3:K3. I've named that range as Labour_Sales with =OFFSET(Metrics!$B$3,0,0,COUNTA(Metrics!$3:$3),1) in the Refers to box so that every time i add a column the formula would update automatically. I am using Excel 2007 In cell L3 i have a formula =AVERAGE (Labour_Sales). I got #DIV/0!. Why isnt this working?? Pls help :) -- HT I think you will find that you have defined a range in a column not in a row The syntax is =OFFSET(reference, rows, columns, height, width) You want a height of 1 and a width of COUNT(......) So change your definition to =OFFSE...
VLOOKUP and cell color problem.
I have a spreadsheet to create a quotation/estimate and I use the VLOOKUP function to retrieve the price of an item on another worksheet. In that worksheet, I have several pricelists from various suppliers but one column gives me the lowest price and changes the cell color associated with the supplier so I know from which supplier the price comes from. Now my main quotation page displays the cheapest price but does not pass along the cell color which I need so i can tell, by looking at the quotation, which supplier i need to order each item from. Is there a way to pass along not only the va...
Vlookup
Dear all, For vlookup, it will return the value of the destination cell. Is it possible to return the row no. instead of value of the destination cell?? Your help is highly appreciated. Thanks & regards, Automne Look at the MATCH function in Help. On Sat, 5 Mar 2005 01:41:39 +0800, "automne" <hersbt_no_spam@yahoo.com.hk> wrote: >Dear all, > >For vlookup, it will return the value of the destination cell. Is it >possible to return the row no. instead of value of the destination cell?? > >Your help is highly appreciated. > >Thanks & regards,...
Vlookup not working where lookup value contains an apostrophe
Column A contains a list of names including O'Brien. Column B contains the result of a vlookup which uses the names in column A as the look up value. ie Column B2 contains the formula =vlookup(A2 ... The Array table is on a separate sheet within the workbook. All look up values except for O'Brien generate the desired result. O'Brien generates #N/A. Any suggestions as to what is going wrong? It should work fine. Maybe there's a white space somewhere throwing apparent good matches off. Try using TRIM: =VLOOKUP(TRIM(A2),.. Any good? hit the YES -- Max Singapore http://...
I have 2 excel sheets. The company name field in both differ in most cases. currently I do a vlookup like this =VLOOKUP(AH5033,companydetails_company.xls!M$2:M$1491,1,FALSE) For example on the first excel sheet I will have: "Greenberg Ltd" and then on the other sheet I might have "Greenberg Ltd." or "UK Greenberg" Is there something called a best match? Its a once off thing I need to do so efficency is not an issue. I'd really appreciate the help with this one. there is no artificial intelligence built into the function, but you can possibly use som...
combobox and vlookups?
I am trying to add a combobox that when you select from the dropdown menu, the columns nextdoor automatically pull up corresponding data that is related to the selection from the dropdown list. Does this make sense? What do I do to set this whole thing up? (I don't know code). Thanks. -- Message posted via http://www.officekb.com You shouldn't need code for anything you described here. Set up the combobox with the list fill range, if any. Set the linked cell to the desired cell. Then, use the VLOOKUP command in another cell to look for the linked cell from the combo box to lo...
offset/macro problem
I want to hide certain rows given a certain value. I.E. if a user enters a value of 3 securities, I want to hide rows 14-40. If a user enters a value of 6 securities, I want to hide rows 17-40. I have tried to write a macro using the offset button, but cannot get it to work. Any help? See attached file. Thanks. Sub HideRows() ' ' ' Rows.Offset("G4+11:40").Select Range("B14").Activate Selection.EntireRow.Hidden = True Range("B3").Select End Sub Attachment filename: loop.macro.problem.xls Download attachment: http://www.exce...
VLOOKUP and sum
I have a list of project numbers, their monthly sales, and, 5 categories the projects fall into. For each month, I need to associate the project numbers with the five categories, and, sum the monthly sales for each category. (i.e. be able to paste the row of project numbers and associated sales into a spreadsheet and be able to produce the monthly sales of each of the 5 categories e.g. project # category <4000 S 4101 O 4102 C 4103 G 4104 I 4105 G 4106 C 4107 O 4108 O 41...
ComboBox linkedcell population not recognized by Vlookup
I have created a worsheet in which I have Vlookup formulas performing calculations. I then created Combobox drop lists and linked given cells. Everything is working properly except the combobox populating the linkedcell is not being recognized by my Vlookup functions. Has anyone ran into this problem? If so can you tell me how to fix. Thanks Ed Hi you're probably searching for a number. If yes it could be that the value returned from your combo box is stored as 'Text' (though it represents a number). If you current VLOOKUP formula looks like =VLOOKUP(A1,'lookup'!A1:B...
Vlookup #20
Hi- I am using vlookup where the lookup table is in another file, I have the file name in a cell of the sheet, please let me know how to refer to the file name and range name in the vlookup function by using the cell address that contains the file name. One way: =VLOOKUP(A1,INDIRECT("'[" & A2 & "]Sheet1'!A:B"),2,FALSE) However, for INDIRECT, the file must be open or you'll get a #Ref! error. To work with closed workbooks, you could use the INDIRECT.EXT function in Laurent Longre's MOREFUNC.xll add-in: http://xcell05.free.fr/ In ...
Vlookup hyperlink
Dear All I am using Office 97 :( And want to try to do a vlookup but the value I want to retrieve is a hyperlink, when doing this it returns the text but the hyperlink doesn't work Is there anyway this can be done Thank Adam Hi Adam try something like =HYPERLINK(VLOOKUP(....)) -- Regards Frank Kabel Frankfurt, Germany Adam wrote: > Dear All, > > I am using Office 97 :( And want to try to do a vlookup but the value > I want to retrieve is a hyperlink, when doing this it returns the > text but the hyperlink doesn't work. > > Is there anyway this can be done? ...
Vlookup in vba
Hi I have the following snippet of code: Sheets("mdata").Select Range("E1").Select ActiveCell.FormulaR1C1 = "RVU" Range("E2").Select ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-1],rvu!R[-1]C[-4]:R[7238]C[-3],2)),0,VLOOKUP(RC[-1],rvu!R[-1]C[-4]:R[7238]C[-3],2))" Range("E2").Select Selection.AutoFill Destination:=Range("E2:E" & LastRow), Type:=xlFillDefault This fills about 5,000 rows with look up data from the rvu ws, and puts in 0 if #N/A is obtained. HOWEVER, if I was ...
Vlookup #33
In my spreadsheet i have a worksheet(1) with three columns. A B C data1 data1 data1 data2 data2 data2 I would like to use the VLOOKUP function to bring data from column c into another worksheet(2), when data entered into worksheet(2),columns A and B, matches data with worksheet(1) Is this possible? Please advise Paul =INDEX(Sheet1!C1:C1000,MATCH(1,(Sheet2!A2=Sheet1!A1:A1000)*(Sheet2!B2=Sheet1 !B1:B1000),0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- ...
Vlookups, IF's..and Oh No!
There are three sheets. Customer Service Roster, Customer Servic Timesheet and Master Data. Sheet *"Customer Service Roster" * It is split into five sections, one for each day (Mon-Fri). You clic on a cell under the date and it brings up a list to select. Once yo select the option, the start time appears on the left of it. Prett simple. A5=IF(ISNA((VLOOKUP(B5,'Maste Data'!J79:L106,3,0))),0,(VLOOKUP(B5,'Master Data'!J79:L106,3,0))) B5=Validation List Sheet *"Customer Service Timesheet"* This has the employees listed on A6:A20. Then it has to the ri...
VLOOKUP problem #4
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 ...
VLOOKUP for ZIP codes??
Hi all. I work for a college and we've divided up the country, by zip code, into many many many different regions. What I have is a worksheet in excel that looks like this 'MinZip' 'MaxZip' Region 75000 75899 DALLAS 76000 76899 DALLAS Where MINZIP s the beginning number of a ZIPCode range for a particular region, and MAX is the terminating zip code. On a separate form, I have the listing of our applicants and their zip codes. I was to create a lookup formula that says (in english): If REALZIP is between MINZIP and MAXZIP on row one, re...
vlookups sheet is too big
Group, I created a sheet a while back with Vlookups all over the sheet which is currently 14 Megs im trying to get the sheet down to a reasonable size. Does anybody know of a way to do this. Thanks in advance, Fish Fish Perhaps Excel estimates your actual range of data as far larger than it really is. To reset the "used range" http://www.contextures.on.ca/xlfaqApp.html#Unused Gord Dibben Excel MVP On Mon, 8 Nov 2004 09:34:17 -0800, "Fish" <anonymous@discussions.microsoft.com> wrote: >Group, > >I created a sheet a while back with Vlookups all over...
Vlookup icw =product()
Hi, Can someone tell me which formula to use for the following command: I would like to lookup a certain name in another sheet and subsequently take the PRODUCT of the range of the numbers stated after this name. For example: find RNNF in sheet1 (column A)....say this is cell A1....... and take =PRODUCT() of values in B1:Z1 I tried =VLOOKUP(RNNF,'Performance Funds'! A1:A31,PRODUCT(1+'Performance Funds'!B:Z)-1,0) but no luck;-( Thanks a lot!!!! Rgds, Robert Hi Robert, =PRODUCT(INDIRECT("B"&MATCH("RNNF",A1:A3,0)&":Z"&MATCH("R...
Vlookup with Reference cell from Sharpoint
Hi, I have a cell (Ex : Emp ID) in a XL sheet, whenever i enter an ID, it should go to Web Sharepoint folder and refer the Emp ID with the Global XL Sheet and returns the value (ex : Employee's Name) How is it possible ? ...
Integration Manager empty Inventory Offset Posting Account Setup
Dear Support: We are using the Integration Manager importing into a Inventory Transaction from a Source file. However, are forced to enter a Inventory Offset Account into the Tools >> Setup >> Posting >> Posting Account. Without it the error is given: 'The Inventory Offset posting account is missing for Item number 000000'. The source file has an offset account, but it is actually only "missing" from the Posting Account setup. Therefore, we would like to suggest that this restriction is removed. The reason for this is: We do not want anything account ...
VLOOKUP ?
I use the VLOOKUP function a lot but was wondering if it were possible to use this when there are more than one cell I want to separately use data from. Currently I have: =VLOOKUP(B1,Sheet3!$A$1:$B$65,2,FALSE) but what I can't seem to do is then take the data from the $C$ column, thus avoiding the $B$ column =VLOOKUP(B1,Sheet3!$A$1:$C$65,2,FALSE) ~ doesn't work as this includes column B. What I have to do at the moment is copy/paste the columns 'C' and 'D' from the 1st sheet to two new sheets so the data is in column B Many thanks Keith (Southend) =VLOOKUP(B1,Sheet3...
VLOOKUP with VB
Hi, I have the current code and would like help with two queestions: 1) is it possible to return the value and not the formula 2) Is it possible to enter the formula in all the cells without using copy down 'Copy EIF Numbers to NB sheet Sheets("NB").Range("P3") = "EIF" Sheets("NB").Range("O3").Copy Sheets("NB").Range("P3").PasteSpecial Paste:=xlPasteFormats Sheets("NB").Range("P4").FormulaR1C1 = "=VLOOKUP(NB!RC[-15]&RC[-14],EIF!C[-15]:C[-9],7,FALSE)" LastRow = Sheets...
help on a vlookup please
Hi there i need some help on a vlookup, I just can't seem to get it to work. in sheet 1, B2 I have a name joe blow in sheet 1, A2 I would like their street number (on address sheet) this is what I have tried and keep getting #N/A or blank =IF($B2>0,VLOOKUP($B2,address sheet!$A$1:$O$139,1,FALSE )," ") address sheet colmn A has the street number address sheet column B has their name street numbers are formated as numbers any ideas? regards Ditchy Ballarat, Australia Hi, Try this =IF($B2>0,VLOOKUP($B2,'address sheet'!$A$1:$O$139,2,FALSE ),"...
VLOOKUP returns #N/A but want a blank instead
I want to have a blank field return instead of #N/A when my VLOOKUP is false. How would I change my current formula to do that? Here is the formula currently used: =VLOOKUP(A10,DC$7:DH$801,4,1)*D10 Any help would be appreciated. Hi try =IF(ISNA(VLOOKUP(A10,DC$7:DH$801,4,1)),"",VLOOKUP(A10,DC$7:DH$801,4,1)* D10) -- Regards Frank Kabel Frankfurt, Germany "Golf Club" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:584f01c42d4d$5ec3f9d0$a601280a@phx.gbl... > I want to have a blank field return instead of #N/A when > my VLOOKUP is false. ...
VLOOKUP numbers as #N/A
Hi Having a few issues with VLOOKUP, basically if Part Numbers (ColumnD) contains text and numbers it is okay, hoever, rows that just contain numbers only or numbers and slashes or dots are showing as #N/A in ColumnE, have searched on here (http://www.contextures.com/ xlFunctions02.html) and tried looking at following but does not seem to work. any help appreciated!!! Here is formula currently: =VLOOKUP(D2,CommodityCode,4) 'CommodityCode' is named Range on another sheet in same workbook Here are results for column D and E respectively: Part Number Commodity GW Sma...
defined name
Hi, My spreadsheet has data from B3:K3. I've named that range as Labour_Sales with =OFFSET(Metrics!$B$3,0,0,COUNTA(Metrics!$3:$3),1) in the Refers to box so that every time i add a column the formula would update automatically. I am using Excel 2007 In cell L3 i have a formula =AVERAGE (Labour_Sales). I got #DIV/0!. Why isnt this working?? Pls help :) -- HT I think you will find that you have defined a range in a column not in a row The syntax is =OFFSET(reference, rows, columns, height, width) You want a height of 1 and a width of COUNT(......) So change your definition to =OFFSE...
VLOOKUP and cell color problem.
I have a spreadsheet to create a quotation/estimate and I use the VLOOKUP function to retrieve the price of an item on another worksheet. In that worksheet, I have several pricelists from various suppliers but one column gives me the lowest price and changes the cell color associated with the supplier so I know from which supplier the price comes from. Now my main quotation page displays the cheapest price but does not pass along the cell color which I need so i can tell, by looking at the quotation, which supplier i need to order each item from. Is there a way to pass along not only the va...
Vlookup
Dear all, For vlookup, it will return the value of the destination cell. Is it possible to return the row no. instead of value of the destination cell?? Your help is highly appreciated. Thanks & regards, Automne Look at the MATCH function in Help. On Sat, 5 Mar 2005 01:41:39 +0800, "automne" <hersbt_no_spam@yahoo.com.hk> wrote: >Dear all, > >For vlookup, it will return the value of the destination cell. Is it >possible to return the row no. instead of value of the destination cell?? > >Your help is highly appreciated. > >Thanks & regards,...
Vlookup not working where lookup value contains an apostrophe
Column A contains a list of names including O'Brien. Column B contains the result of a vlookup which uses the names in column A as the look up value. ie Column B2 contains the formula =vlookup(A2 ... The Array table is on a separate sheet within the workbook. All look up values except for O'Brien generate the desired result. O'Brien generates #N/A. Any suggestions as to what is going wrong? It should work fine. Maybe there's a white space somewhere throwing apparent good matches off. Try using TRIM: =VLOOKUP(TRIM(A2),.. Any good? hit the YES -- Max Singapore http://...