Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HLOOKUP second column
I am using the following macro to gather the information that I need:
=HLOOKUP($A380,INDIRECT("'"&$A380&"'!D2:H104"),29, FALSE) When I change the formula to =HLOOKUP($A380,INDIRECT("'"&$A380&"'!F2:H104"),29, FALSE) to gather data from the 3rd column of the data area, I get a #NA I made sure to copy the lease number (the A380 reference) to each column (D2, F2, H2) on the worksheet I'm looking up. Can you please tell me what's wrong with the second macro? And why I'm getting the #NA response? thanks Kelly |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HLOOKUP second column
On May 30, 7:11 pm, Kelly wrote:
I am using the following macro to gather the information that I need: =HLOOKUP($A380,INDIRECT("'"&$A380&"'!D2:H104"),29, FALSE) When I change the formula to =HLOOKUP($A380,INDIRECT("'"&$A380&"'!F2:H104"),29, FALSE) to gather data from the 3rd column of the data area, I get a #NA I made sure to copy the lease number (the A380 reference) to each column (D2, F2, H2) on the worksheet I'm looking up. Can you please tell me what's wrong with the second macro? And why I'm getting the #NA response? thanks Kelly Kelly, your formula seeks the sheet named in A380 and then goes to the range D2:H104 and does the HLOOKUP there to find what is in row 30 of the column that has A380's value in row 2. HLOOKUP (and VLOOKUP) require the lookup value to appear in the first row (or column respectively) of the lookup table. If you change your reference to F2:H104 HLOOKUP will not be able to find the lookup value. HTH Kostis Vezerides |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HLOOKUP second column
I made sure to put the Lease number (A380 reference) at the top of the column
in F2 (as it is in D2). Can't I start my lookup from column F instead of column D? And, if not, how do I look up in the range D2:H104 and return the data in columns F or H? Thanks for your quick response! Kelly "vezerid" wrote: On May 30, 7:11 pm, Kelly wrote: I am using the following macro to gather the information that I need: =HLOOKUP($A380,INDIRECT("'"&$A380&"'!D2:H104"),29, FALSE) When I change the formula to =HLOOKUP($A380,INDIRECT("'"&$A380&"'!F2:H104"),29, FALSE) to gather data from the 3rd column of the data area, I get a #NA I made sure to copy the lease number (the A380 reference) to each column (D2, F2, H2) on the worksheet I'm looking up. Can you please tell me what's wrong with the second macro? And why I'm getting the #NA response? thanks Kelly Kelly, your formula seeks the sheet named in A380 and then goes to the range D2:H104 and does the HLOOKUP there to find what is in row 30 of the column that has A380's value in row 2. HLOOKUP (and VLOOKUP) require the lookup value to appear in the first row (or column respectively) of the lookup table. If you change your reference to F2:H104 HLOOKUP will not be able to find the lookup value. HTH Kostis Vezerides |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HLOOKUP second column
I am not sure why the HLOOKUP is not functioning.
But what I would reiterate is that it is not being used for its intended purpose here and that is part of the problem. If you want a formula that goes to a sheet (name variable) and collects data from a particular cell D30,f30,H30 , then offset( INDIRECT("'"&$A380&"'!D1") ,29,0 ) has several advantages but the main one is it will not give errors unless you give it a leasenumber for a sheet that doesn't exist Trust me this will help! "Kelly" wrote: I made sure to put the Lease number (A380 reference) at the top of the column in F2 (as it is in D2). Can't I start my lookup from column F instead of column D? And, if not, how do I look up in the range D2:H104 and return the data in columns F or H? Thanks for your quick response! Kelly "vezerid" wrote: On May 30, 7:11 pm, Kelly wrote: I am using the following macro to gather the information that I need: =HLOOKUP($A380,INDIRECT("'"&$A380&"'!D2:H104"),29, FALSE) When I change the formula to =HLOOKUP($A380,INDIRECT("'"&$A380&"'!F2:H104"),29, FALSE) to gather data from the 3rd column of the data area, I get a #NA I made sure to copy the lease number (the A380 reference) to each column (D2, F2, H2) on the worksheet I'm looking up. Can you please tell me what's wrong with the second macro? And why I'm getting the #NA response? thanks Kelly Kelly, your formula seeks the sheet named in A380 and then goes to the range D2:H104 and does the HLOOKUP there to find what is in row 30 of the column that has A380's value in row 2. HLOOKUP (and VLOOKUP) require the lookup value to appear in the first row (or column respectively) of the lookup table. If you change your reference to F2:H104 HLOOKUP will not be able to find the lookup value. HTH Kostis Vezerides |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HLOOKUP second column
When I correct the formula you indicated (adding = before offset and
eliminating unnecessary spaces), I get the info in column D on the worksheet, not column F as I wanted. If I change your formula to be =offset(INDIRECT("'"&$A380&"'!F1"),29,0) I get a #REF! response. I still must be doing something wrong. "DazzaData" wrote: I am not sure why the HLOOKUP is not functioning. But what I would reiterate is that it is not being used for its intended purpose here and that is part of the problem. If you want a formula that goes to a sheet (name variable) and collects data from a particular cell D30,f30,H30 , then offset( INDIRECT("'"&$A380&"'!D1") ,29,0 ) has several advantages but the main one is it will not give errors unless you give it a leasenumber for a sheet that doesn't exist Trust me this will help! "Kelly" wrote: I made sure to put the Lease number (A380 reference) at the top of the column in F2 (as it is in D2). Can't I start my lookup from column F instead of column D? And, if not, how do I look up in the range D2:H104 and return the data in columns F or H? Thanks for your quick response! Kelly "vezerid" wrote: On May 30, 7:11 pm, Kelly wrote: I am using the following macro to gather the information that I need: =HLOOKUP($A380,INDIRECT("'"&$A380&"'!D2:H104"),29, FALSE) When I change the formula to =HLOOKUP($A380,INDIRECT("'"&$A380&"'!F2:H104"),29, FALSE) to gather data from the 3rd column of the data area, I get a #NA I made sure to copy the lease number (the A380 reference) to each column (D2, F2, H2) on the worksheet I'm looking up. Can you please tell me what's wrong with the second macro? And why I'm getting the #NA response? thanks Kelly Kelly, your formula seeks the sheet named in A380 and then goes to the range D2:H104 and does the HLOOKUP there to find what is in row 30 of the column that has A380's value in row 2. HLOOKUP (and VLOOKUP) require the lookup value to appear in the first row (or column respectively) of the lookup table. If you change your reference to F2:H104 HLOOKUP will not be able to find the lookup value. HTH Kostis Vezerides |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HLOOKUP second column
If it works for d1-d30 but not when you change d1 to f1, my first thought
would be #ref! must be whats actually in f30 which might have been the problem all along "Kelly" wrote: When I correct the formula you indicated (adding = before offset and eliminating unnecessary spaces), I get the info in column D on the worksheet, not column F as I wanted. If I change your formula to be =offset(INDIRECT("'"&$A380&"'!F1"),29,0) I get a #REF! response. I still must be doing something wrong. "DazzaData" wrote: I am not sure why the HLOOKUP is not functioning. But what I would reiterate is that it is not being used for its intended purpose here and that is part of the problem. If you want a formula that goes to a sheet (name variable) and collects data from a particular cell D30,f30,H30 , then offset( INDIRECT("'"&$A380&"'!D1") ,29,0 ) has several advantages but the main one is it will not give errors unless you give it a leasenumber for a sheet that doesn't exist Trust me this will help! "Kelly" wrote: I made sure to put the Lease number (A380 reference) at the top of the column in F2 (as it is in D2). Can't I start my lookup from column F instead of column D? And, if not, how do I look up in the range D2:H104 and return the data in columns F or H? Thanks for your quick response! Kelly "vezerid" wrote: On May 30, 7:11 pm, Kelly wrote: I am using the following macro to gather the information that I need: =HLOOKUP($A380,INDIRECT("'"&$A380&"'!D2:H104"),29, FALSE) When I change the formula to =HLOOKUP($A380,INDIRECT("'"&$A380&"'!F2:H104"),29, FALSE) to gather data from the 3rd column of the data area, I get a #NA I made sure to copy the lease number (the A380 reference) to each column (D2, F2, H2) on the worksheet I'm looking up. Can you please tell me what's wrong with the second macro? And why I'm getting the #NA response? thanks Kelly Kelly, your formula seeks the sheet named in A380 and then goes to the range D2:H104 and does the HLOOKUP there to find what is in row 30 of the column that has A380's value in row 2. HLOOKUP (and VLOOKUP) require the lookup value to appear in the first row (or column respectively) of the lookup table. If you change your reference to F2:H104 HLOOKUP will not be able to find the lookup value. HTH Kostis Vezerides |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HLOOKUP second column
On May 30, 7:50 pm, Kelly wrote:
I made sure to put the Lease number (A380 reference) at the top of the column in F2 (as it is in D2). Can't I start my lookup from column F instead of column D? And, if not, how do I look up in the range D2:H104 and return the data in columns F or H? Thanks for your quick response! Kelly "vezerid" wrote: On May 30, 7:11 pm, Kelly wrote: I am using the following macro to gather the information that I need: =HLOOKUP($A380,INDIRECT("'"&$A380&"'!D2:H104"),29, FALSE) When I change the formula to =HLOOKUP($A380,INDIRECT("'"&$A380&"'!F2:H104"),29, FALSE) to gather data from the 3rd column of the data area, I get a #NA I made sure to copy the lease number (the A380 reference) to each column (D2, F2, H2) on the worksheet I'm looking up. Can you please tell me what's wrong with the second macro? And why I'm getting the #NA response? thanks Kelly Kelly, your formula seeks the sheet named in A380 and then goes to the range D2:H104 and does the HLOOKUP there to find what is in row 30 of the column that has A380's value in row 2. HLOOKUP (and VLOOKUP) require the lookup value to appear in the first row (or column respectively) of the lookup table. If you change your reference to F2:H104 HLOOKUP will not be able to find the lookup value. HTH Kostis Vezerides It seems to me you need VLOOKUP not HLOOKUP for the data you are describing. If you want you can email me the file (vezerid at act dot edu). Most likely I will reply tomorrow as I am soon leaving, but someone else might jump in. In the meanwhile try to decipher the online help pages on VLOOKUP and HLOOKUP and see which one fits your task. Regards, Kostis Vezerides |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HLOOKUP second column
Have you got your rows and columns mixed up.
This formula looks along a row and then once it finds the column with that value down 28 rows to return a value. incidentally I think offset indirect would do the job more easily offset( INDIRECT("'"&$A380&"'!D2") ,28,0 ) offset( INDIRECT("'"&$A380&"'!f2") ,28,0 ) offset( INDIRECT("'"&$A380&"'!H2") ,28,0 ) since the formula use a380 to select sheet it isn't really using it to lookup anything inside the sheet, why would there be a sheet named after a lease number with data about other lease numbers in it?? "Kelly" wrote: I am using the following macro to gather the information that I need: =HLOOKUP($A380,INDIRECT("'"&$A380&"'!D2:H104"),29, FALSE) When I change the formula to =HLOOKUP($A380,INDIRECT("'"&$A380&"'!F2:H104"),29, FALSE) to gather data from the 3rd column of the data area, I get a #NA I made sure to copy the lease number (the A380 reference) to each column (D2, F2, H2) on the worksheet I'm looking up. Can you please tell me what's wrong with the second macro? And why I'm getting the #NA response? thanks Kelly |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup/hlookup without using row or column numbers | Excel Worksheet Functions | |||
HLOOKUP and SUM | Excel Discussion (Misc queries) | |||
Getting column number from HLOOKUP? | Excel Worksheet Functions | |||
Hlookup? | Excel Worksheet Functions | |||
HLOOKUP | Excel Worksheet Functions |