| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array reference using indirect address
I need to reference only the first cell of an array on a different worksheet
using a value in a cell on the current worksheet: Current Worksheet Cell B11 contains text from another function which is a cell reference i.e AB13 I am trying to create a VLookup array on a different worksheet: =VLOOKUP(8,Details!AB13:CT13,70) I know I need to somehow replace the AB13 with &B11, but I cannot get it to work correctly. Thanks for the help.... |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array reference using indirect address
Try it like this:
=VLOOKUP(8,INDIRECT("Details!"&B11&":CT13"),70) -- Biff Microsoft Excel MVP "Mshaw" wrote in message ... I need to reference only the first cell of an array on a different worksheet using a value in a cell on the current worksheet: Current Worksheet Cell B11 contains text from another function which is a cell reference i.e AB13 I am trying to create a VLookup array on a different worksheet: =VLOOKUP(8,Details!AB13:CT13,70) I know I need to somehow replace the AB13 with &B11, but I cannot get it to work correctly. Thanks for the help.... |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array reference using indirect address
Hmmm...
I just noticed that your table array is a single row: =VLOOKUP(8,Details!AB13:CT13,70) Details!AB13:CT13 Is that a typo? -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try it like this: =VLOOKUP(8,INDIRECT("Details!"&B11&":CT13"),70) -- Biff Microsoft Excel MVP "Mshaw" wrote in message ... I need to reference only the first cell of an array on a different worksheet using a value in a cell on the current worksheet: Current Worksheet Cell B11 contains text from another function which is a cell reference i.e AB13 I am trying to create a VLookup array on a different worksheet: =VLOOKUP(8,Details!AB13:CT13,70) I know I need to somehow replace the AB13 with &B11, but I cannot get it to work correctly. Thanks for the help.... |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array reference using indirect address
Thank You....
I think I tried every possible iteration of quotes, except the one you showed. The post was correct using a single row with Vlookup, maybe I could have done it differently, but what I'm looking for is whether or not there is an 8 in the first cell and if there is then I return the value from the last cell of the array. The Vlookup is in an IF statement that returns null if there is not an 8 in that first cell. Everything in the worsheet is tied to a single cell that has a date, I can change the date and all the references change, including the column # to return at the end of the vlookup statement, which I showed as a constant in the original post. Mike "T. Valko" wrote: Try it like this: =VLOOKUP(8,INDIRECT("Details!"&B11&":CT13"),70) -- Biff Microsoft Excel MVP "Mshaw" wrote in message ... I need to reference only the first cell of an array on a different worksheet using a value in a cell on the current worksheet: Current Worksheet Cell B11 contains text from another function which is a cell reference i.e AB13 I am trying to create a VLookup array on a different worksheet: =VLOOKUP(8,Details!AB13:CT13,70) I know I need to somehow replace the AB13 with &B11, but I cannot get it to work correctly. Thanks for the help.... |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array reference using indirect address
You're welcome. Thanks for feeding back with the clarification.
-- Biff Microsoft Excel MVP "Mshaw" wrote in message ... Thank You.... I think I tried every possible iteration of quotes, except the one you showed. The post was correct using a single row with Vlookup, maybe I could have done it differently, but what I'm looking for is whether or not there is an 8 in the first cell and if there is then I return the value from the last cell of the array. The Vlookup is in an IF statement that returns null if there is not an 8 in that first cell. Everything in the worsheet is tied to a single cell that has a date, I can change the date and all the references change, including the column # to return at the end of the vlookup statement, which I showed as a constant in the original post. Mike "T. Valko" wrote: Try it like this: =VLOOKUP(8,INDIRECT("Details!"&B11&":CT13"),70) -- Biff Microsoft Excel MVP "Mshaw" wrote in message ... I need to reference only the first cell of an array on a different worksheet using a value in a cell on the current worksheet: Current Worksheet Cell B11 contains text from another function which is a cell reference i.e AB13 I am trying to create a VLookup array on a different worksheet: =VLOOKUP(8,Details!AB13:CT13,70) I know I need to somehow replace the AB13 with &B11, but I cannot get it to work correctly. Thanks for the help.... |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array reference using indirect address
Except that it looks up the second but last cell, if you want the last cell
in AB13:CT13 you should change 70 to 71 -- Regards, Peo Sjoblom "Mshaw" wrote in message ... Thank You.... I think I tried every possible iteration of quotes, except the one you showed. The post was correct using a single row with Vlookup, maybe I could have done it differently, but what I'm looking for is whether or not there is an 8 in the first cell and if there is then I return the value from the last cell of the array. The Vlookup is in an IF statement that returns null if there is not an 8 in that first cell. Everything in the worsheet is tied to a single cell that has a date, I can change the date and all the references change, including the column # to return at the end of the vlookup statement, which I showed as a constant in the original post. Mike "T. Valko" wrote: Try it like this: =VLOOKUP(8,INDIRECT("Details!"&B11&":CT13"),70) -- Biff Microsoft Excel MVP "Mshaw" wrote in message ... I need to reference only the first cell of an array on a different worksheet using a value in a cell on the current worksheet: Current Worksheet Cell B11 contains text from another function which is a cell reference i.e AB13 I am trying to create a VLookup array on a different worksheet: =VLOOKUP(8,Details!AB13:CT13,70) I know I need to somehow replace the AB13 with &B11, but I cannot get it to work correctly. Thanks for the help.... |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array reference using indirect address
If B11 contains the starting cell of the array (AB13) you can use
=VLOOKUP(8,Details!INDIRECT(B11):CT13,70) and the formula becomes =VLOOKUP(8,Details!AB13:CT13,70) "Mshaw" wrote in message ... I need to reference only the first cell of an array on a different worksheet using a value in a cell on the current worksheet: Current Worksheet Cell B11 contains text from another function which is a cell reference i.e AB13 I am trying to create a VLookup array on a different worksheet: =VLOOKUP(8,Details!AB13:CT13,70) I know I need to somehow replace the AB13 with &B11, but I cannot get it to work correctly. Thanks for the help.... |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array reference using indirect address
=VLOOKUP(8,Details!INDIRECT(B11):CT13,70)
Excel won't accept that as a formula. You have to put the entire table array *inside* the INDIRECT function. =VLOOKUP(8,INDIRECT("Details!"&B11&":CT13"),70) But even this formula doesn't make sense since the table array is a single row. -- Biff Microsoft Excel MVP "Wondering" wrote in message ... If B11 contains the starting cell of the array (AB13) you can use =VLOOKUP(8,Details!INDIRECT(B11):CT13,70) and the formula becomes =VLOOKUP(8,Details!AB13:CT13,70) "Mshaw" wrote in message ... I need to reference only the first cell of an array on a different worksheet using a value in a cell on the current worksheet: Current Worksheet Cell B11 contains text from another function which is a cell reference i.e AB13 I am trying to create a VLookup array on a different worksheet: =VLOOKUP(8,Details!AB13:CT13,70) I know I need to somehow replace the AB13 with &B11, but I cannot get it to work correctly. Thanks for the help.... |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array reference using indirect address
Excel 2007 accepts the formula =VLOOKUP(C1,INDIRECT(D1):B7,2). D1 contains
A1, the formula becomes =VLOOKUP(C1,$A$1,$B$7,2) and returns the correct value from column B. If I use =SUM(INDIRECT(D1),A7) the formula becomes SUM($A1$A7) and sums A1 thru A7 "T. Valko" wrote in message ... =VLOOKUP(8,Details!INDIRECT(B11):CT13,70) Excel won't accept that as a formula. You have to put the entire table array *inside* the INDIRECT function. =VLOOKUP(8,INDIRECT("Details!"&B11&":CT13"),70) But even this formula doesn't make sense since the table array is a single row. -- Biff Microsoft Excel MVP "Wondering" wrote in message ... If B11 contains the starting cell of the array (AB13) you can use =VLOOKUP(8,Details!INDIRECT(B11):CT13,70) and the formula becomes =VLOOKUP(8,Details!AB13:CT13,70) "Mshaw" wrote in message ... I need to reference only the first cell of an array on a different worksheet using a value in a cell on the current worksheet: Current Worksheet Cell B11 contains text from another function which is a cell reference i.e AB13 I am trying to create a VLookup array on a different worksheet: =VLOOKUP(8,Details!AB13:CT13,70) I know I need to somehow replace the AB13 with &B11, but I cannot get it to work correctly. Thanks for the help.... |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array reference using indirect address
But I do agree his addresses are out of whack
"Wondering" wrote in message ... Excel 2007 accepts the formula =VLOOKUP(C1,INDIRECT(D1):B7,2). D1 contains A1, the formula becomes =VLOOKUP(C1,$A$1,$B$7,2) and returns the correct value from column B. If I use =SUM(INDIRECT(D1),A7) the formula becomes SUM($A1$A7) and sums A1 thru A7 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array reference using indirect address - Correction
Correction. I always see my mistakes after I post. Sorry
Excel 2007 accepts the formula =VLOOKUP(C1,INDIRECT(D1):B7,2). D1 contains A1, the formula becomes =VLOOKUP(C1,$A$1:$B$7,2) and returns the correct value from column B. If I use =SUM(INDIRECT(D1):A7) the formula becomes SUM($A1:$A7) and sums A1 thru A7 T Valko" wrote in message ... =VLOOKUP(8,Details!INDIRECT(B11):CT13,70) Excel won't accept that as a formula. You have to put the entire table array *inside* the INDIRECT function. =VLOOKUP(8,INDIRECT("Details!"&B11&":CT13"),70) But even this formula doesn't make sense since the table array is a single row. -- Biff Microsoft Excel MVP |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array reference using indirect address - Correction
As I said I always see my errors after I post. I shall examine this post for
at least 1/2 hour before posting. Correction. I always see my mistakes after I post. Sorry Excel 2007 accepts the formula =VLOOKUP(C1,INDIRECT(D1):B7,2). D1 contains A1. The formula becomes =VLOOKUP(C1,$A$1:$B$7,2) and returns the correct value from column B. If I use =SUM(INDIRECT(D1):A7) the formula becomes SUM($A$1:$A$7) and sums A1 thru A7 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array reference using indirect address
Excel 2007 accepts: =VLOOKUP(INDIRECT(A1),INDIRECT(B1):INDIRECT(C1),2)
Works fine. I don't have Excel 2003 to test this. I don't know why it wouldn't. After all this is the whole purpose of indirection. "T. Valko" wrote in message ... =VLOOKUP(8,Details!INDIRECT(B11):CT13,70) Excel won't accept that as a formula. You have to put the entire table array *inside* the INDIRECT function. =VLOOKUP(8,INDIRECT("Details!"&B11&":CT13"),70) But even this formula doesn't make sense since the table array is a single row. -- Biff Microsoft Excel MVP "Wondering" wrote in message ... If B11 contains the starting cell of the array (AB13) you can use =VLOOKUP(8,Details!INDIRECT(B11):CT13,70) and the formula becomes =VLOOKUP(8,Details!AB13:CT13,70) "Mshaw" wrote in message ... I need to reference only the first cell of an array on a different worksheet using a value in a cell on the current worksheet: Current Worksheet Cell B11 contains text from another function which is a cell reference i.e AB13 I am trying to create a VLookup array on a different worksheet: =VLOOKUP(8,Details!AB13:CT13,70) I know I need to somehow replace the AB13 with &B11, but I cannot get it to work correctly. Thanks for the help.... |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array reference using indirect address
You are not doing the same formula as Biff, use the OP's original references
and lookup the values in another sheet. You are looking up values in the same sheet =VLOOKUP(8,INDIRECT(B11):INDIRECT(C11),70) with AB13 in B11 and CT13 in C11 will work as long as you are looking up the values in the same sheet, that is not the case. The OP clearly stated that he is using VLOOKUP in another sheets or else it wouldn't make any sense incorporating the sheet name.. That works in all Excel versions. The problem is when you refer to another sheet, you cannot for instance put the sheet name in D11 and use 3 INDIRECT you need to do like Biff . So what you are saying is incorrect, it doesn't work like that in Excel 2007, it works exactly like in previous versions where you have to build text string referring to other worksheets -- Regards, Peo Sjoblom "Wondering" wrote in message ... Excel 2007 accepts: =VLOOKUP(INDIRECT(A1),INDIRECT(B1):INDIRECT(C1),2) Works fine. I don't have Excel 2003 to test this. I don't know why it wouldn't. After all this is the whole purpose of indirection. "T. Valko" wrote in message ... =VLOOKUP(8,Details!INDIRECT(B11):CT13,70) Excel won't accept that as a formula. You have to put the entire table array *inside* the INDIRECT function. =VLOOKUP(8,INDIRECT("Details!"&B11&":CT13"),70) But even this formula doesn't make sense since the table array is a single row. -- Biff Microsoft Excel MVP "Wondering" wrote in message ... If B11 contains the starting cell of the array (AB13) you can use =VLOOKUP(8,Details!INDIRECT(B11):CT13,70) and the formula becomes =VLOOKUP(8,Details!AB13:CT13,70) "Mshaw" wrote in message ... I need to reference only the first cell of an array on a different worksheet using a value in a cell on the current worksheet: Current Worksheet Cell B11 contains text from another function which is a cell reference i.e AB13 I am trying to create a VLookup array on a different worksheet: =VLOOKUP(8,Details!AB13:CT13,70) I know I need to somehow replace the AB13 with &B11, but I cannot get it to work correctly. Thanks for the help.... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions | Excel Worksheet Functions | |||
| =(INDIRECT(ADDRESS(ROW(),#))) with SUMPRODUCT returning #VALUE! | Excel Worksheet Functions | |||
| INDIRECT(ADDRESS... Across worksheets | Excel Worksheet Functions | |||
| Indirect and Address in Reference to other sheets | Excel Worksheet Functions | |||
| Address func in array reference | Excel Worksheet Functions | |||
Hybrid Mode
