If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Invalid Formula with the use of Indirect
Hi. I'm not sure why it causes the error. The formula is: =VLOOKUP(A1, INDIRECT($C$15), 2, 1) =VLOOKUP(A2, INDIRECT($C$15), 2, 1) =VLOOKUP(A3, INDIRECT($C$15), 2, 1) Cells A1 to A3 contain the values to lookup. Cell C15 contains =ADDRESS(ROW(C5),COLUMN(C5), 4) &":"& ADDRESS(ROW(C14),COLUMN(C14), 4) =C5:C14 [answer display] It won't work! Error: #REF! I need to use the INDIRECT function because I need a dynamic reference (the table). The table is being constantly updated. How to solve this problem? Or is there any alternative to do the same thing? -- Additional information: - I'm using Office XP - I'm using Windows XP |
#2
|
|||
|
|||
Invalid Formula with the use of Indirect
Your range needs to be at least two columns to work within a VLOOKUP formula, i.e. rather than C5:C14 you need C514 or similar -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=535317 |
#3
|
|||
|
|||
Invalid Formula with the use of Indirect
daddylonglegs Wrote: Your range needs to be at least two columns to work within a VLOOKUP formula, i.e. rather than C5:C14 you need C514 or similar Sorry, I should rephrase that... The range needs to be at least as many columns wide as the third argument, as you have a third argument of 2 your range needs to be at least 2 columns wide. You either need to change the range, as indicated above, or change the column index number (third argument) to 1. Obviously the solution is dependant on exactly want you need to do, are you trying to return a value from column D or column C? -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=535317 |
#4
|
|||
|
|||
Invalid Formula with the use of Indirect
Perhaps you can create a dynamic named range, as described he
http://www.contextures.com/xlNames01.html then reference that range in the VLookup formulas. If you provide more detail about the table, and the sheet layout, and what you need to do, someone may be able to suggest alternatives. 0-0 Wai Wai ^-^ wrote: Hi. I'm not sure why it causes the error. The formula is: =VLOOKUP(A1, INDIRECT($C$15), 2, 1) =VLOOKUP(A2, INDIRECT($C$15), 2, 1) =VLOOKUP(A3, INDIRECT($C$15), 2, 1) Cells A1 to A3 contain the values to lookup. Cell C15 contains =ADDRESS(ROW(C5),COLUMN(C5), 4) &":"& ADDRESS(ROW(C14),COLUMN(C14), 4) =C5:C14 [answer display] It won't work! Error: #REF! I need to use the INDIRECT function because I need a dynamic reference (the table). The table is being constantly updated. How to solve this problem? Or is there any alternative to do the same thing? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#5
|
|||
|
|||
Invalid Formula with the use of Indirect
Oh! My silly mistake.
It's because I'm working with different forumlas, some of them use "data set" column only, some of them (like vlookup) need use the whole table but I forget it. So I should create a cell which gets C514. Then I use indirect to call this cell. Thanks for your help. "daddylonglegs" ¦b ¶l¥ó news:daddylonglegs.26pzha_1145800201.9147@excelfor um-nospam.com ¤¤¼¶¼g... daddylonglegs Wrote: Your range needs to be at least two columns to work within a VLOOKUP formula, i.e. rather than C5:C14 you need C514 or similar Sorry, I should rephrase that... The range needs to be at least as many columns wide as the third argument, as you have a third argument of 2 your range needs to be at least 2 columns wide. You either need to change the range, as indicated above, or change the column index number (third argument) to 1. Obviously the solution is dependant on exactly want you need to do, are you trying to return a value from column D or column C? -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=535317 |
#6
|
|||
|
|||
Invalid Formula with the use of Indirect
As to dynamic named range, the problme is I can't change the reference style of
that named range into absolute (eg $A$1) or relative (eg A1) or partially relative. So it's not flexible enough. Thanks for your help! "Debra Dalgleish" ??? ???... Perhaps you can create a dynamic named range, as described he http://www.contextures.com/xlNames01.html then reference that range in the VLookup formulas. If you provide more detail about the table, and the sheet layout, and what you need to do, someone may be able to suggest alternatives. 0-0 Wai Wai ^-^ wrote: Hi. I'm not sure why it causes the error. The formula is: =VLOOKUP(A1, INDIRECT($C$15), 2, 1) =VLOOKUP(A2, INDIRECT($C$15), 2, 1) =VLOOKUP(A3, INDIRECT($C$15), 2, 1) Cells A1 to A3 contain the values to lookup. Cell C15 contains =ADDRESS(ROW(C5),COLUMN(C5), 4) &":"& ADDRESS(ROW(C14),COLUMN(C14), 4) =C5:C14 [answer display] It won't work! Error: #REF! I need to use the INDIRECT function because I need a dynamic reference (the table). The table is being constantly updated. How to solve this problem? Or is there any alternative to do the same thing? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formula to replace invalid filename characters | tschultz | Worksheet Functions | 2 | January 27th, 2006 07:07 PM |
how to use 2 worksheets in one formula with INDIRECT & VLOOKUP | NEWB | Worksheet Functions | 1 | December 3rd, 2005 05:48 AM |
Indirect Formula | fullers | Worksheet Functions | 3 | November 25th, 2005 01:08 PM |
Change formula to Indirect | Ronbo | Worksheet Functions | 3 | October 10th, 2005 07:33 PM |
referencing named formula using INDIRECT function | [email protected] | Worksheet Functions | 19 | May 11th, 2005 09:48 AM |