| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can INDIRECT function reference a cell that contains a formula
I am trying to limit data entry options into a worksheet based on other
information already 'correctly' entered. Based on a number of criteria I return a Named Range as a result of the formula: I am using =INDIRECT(AC18) as the data validation formula Cell AC18: =IF(ISNA(IF(TubeRequired=VLOOKUP(SystemSelected,'I nput Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,5,FALSE),"Get Quote"))),"",(IF(TubeRequired=VLOOKUP(SystemSelect ed,'Input Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,5,FALSE),"Get Quote")))) The resulting named ranges in the 'InputLists'!$A$3:$E$40 are BracketList_A, BracketList_B and BracketList_C where these named ranges containg the brackets that are appropriate for selection by the user. These named ranges are Dynamic Ranges allowing us to update the data table as needed. Based on what I'd read in Debra Dalgleish's site and in the help files I thought that this would work... I don't get any errors but the drop down list in the data validation is empty. Too much info? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can INDIRECT function reference a cell that contains a formula
Hi!
Using Indirect and dynamic ranges won't work. The reason the drop down doesn't work is because =INDIRECT(AC18) evaluates to a #REF! error. Try this as the drop down source: List the named ranges somewhere,say, G1:I1. G1 = listA H1 = listB I1 = listC Or, you may already have the names listed somewhere as the column headers for the named ranges. If so just refer to those cells: =CHOOSE(MATCH(AC18,G1:I1,0),listA,listB,listC) You can have up to 29 named ranges and use CHOOSE. When your formula returns "Get Quote" that'll cause the source formula to error and the drop down won't work. Biff "Steve E" wrote in message ... I am trying to limit data entry options into a worksheet based on other information already 'correctly' entered. Based on a number of criteria I return a Named Range as a result of the formula: I am using =INDIRECT(AC18) as the data validation formula Cell AC18: =IF(ISNA(IF(TubeRequired=VLOOKUP(SystemSelected,'I nput Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,5,FALSE),"Get Quote"))),"",(IF(TubeRequired=VLOOKUP(SystemSelect ed,'Input Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,5,FALSE),"Get Quote")))) The resulting named ranges in the 'InputLists'!$A$3:$E$40 are BracketList_A, BracketList_B and BracketList_C where these named ranges containg the brackets that are appropriate for selection by the user. These named ranges are Dynamic Ranges allowing us to update the data table as needed. Based on what I'd read in Debra Dalgleish's site and in the help files I thought that this would work... I don't get any errors but the drop down list in the data validation is empty. Too much info? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can INDIRECT function reference a cell that contains a formula
Hi BIff,
Thanks for the help. Unfortunately, I seem to only get listB to populate the drop down... even though cell AC18 evaluates to listA... Any ideas? And... wouldn't it be nice if you could cut and paste into the formula bar in the data validation box? Appreciate your help. Steve "Biff" wrote: Hi! Using Indirect and dynamic ranges won't work. The reason the drop down doesn't work is because =INDIRECT(AC18) evaluates to a #REF! error. Try this as the drop down source: List the named ranges somewhere,say, G1:I1. G1 = listA H1 = listB I1 = listC Or, you may already have the names listed somewhere as the column headers for the named ranges. If so just refer to those cells: =CHOOSE(MATCH(AC18,G1:I1,0),listA,listB,listC) You can have up to 29 named ranges and use CHOOSE. When your formula returns "Get Quote" that'll cause the source formula to error and the drop down won't work. Biff "Steve E" wrote in message ... I am trying to limit data entry options into a worksheet based on other information already 'correctly' entered. Based on a number of criteria I return a Named Range as a result of the formula: I am using =INDIRECT(AC18) as the data validation formula Cell AC18: =IF(ISNA(IF(TubeRequired=VLOOKUP(SystemSelected,'I nput Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,5,FALSE),"Get Quote"))),"",(IF(TubeRequired=VLOOKUP(SystemSelect ed,'Input Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,5,FALSE),"Get Quote")))) The resulting named ranges in the 'InputLists'!$A$3:$E$40 are BracketList_A, BracketList_B and BracketList_C where these named ranges containg the brackets that are appropriate for selection by the user. These named ranges are Dynamic Ranges allowing us to update the data table as needed. Based on what I'd read in Debra Dalgleish's site and in the help files I thought that this would work... I don't get any errors but the drop down list in the data validation is empty. Too much info? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can INDIRECT function reference a cell that contains a formula
Biff,
When I tried the same formula in a blank cell on my worksheet it evaluated to a $VALUE error. When I put the listA, listB, listC named ranges in quotes ie "listA, "listB", "listC" it returned the correct match... but when I tried this same thing in the data validation formula bar I get an error (of course, non-traceable). ?? "Steve E" wrote: Hi BIff, Thanks for the help. Unfortunately, I seem to only get listB to populate the drop down... even though cell AC18 evaluates to listA... Any ideas? And... wouldn't it be nice if you could cut and paste into the formula bar in the data validation box? Appreciate your help. Steve "Biff" wrote: Hi! Using Indirect and dynamic ranges won't work. The reason the drop down doesn't work is because =INDIRECT(AC18) evaluates to a #REF! error. Try this as the drop down source: List the named ranges somewhere,say, G1:I1. G1 = listA H1 = listB I1 = listC Or, you may already have the names listed somewhere as the column headers for the named ranges. If so just refer to those cells: =CHOOSE(MATCH(AC18,G1:I1,0),listA,listB,listC) You can have up to 29 named ranges and use CHOOSE. When your formula returns "Get Quote" that'll cause the source formula to error and the drop down won't work. Biff "Steve E" wrote in message ... I am trying to limit data entry options into a worksheet based on other information already 'correctly' entered. Based on a number of criteria I return a Named Range as a result of the formula: I am using =INDIRECT(AC18) as the data validation formula Cell AC18: =IF(ISNA(IF(TubeRequired=VLOOKUP(SystemSelected,'I nput Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,5,FALSE),"Get Quote"))),"",(IF(TubeRequired=VLOOKUP(SystemSelect ed,'Input Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,5,FALSE),"Get Quote")))) The resulting named ranges in the 'InputLists'!$A$3:$E$40 are BracketList_A, BracketList_B and BracketList_C where these named ranges containg the brackets that are appropriate for selection by the user. These named ranges are Dynamic Ranges allowing us to update the data table as needed. Based on what I'd read in Debra Dalgleish's site and in the help files I thought that this would work... I don't get any errors but the drop down list in the data validation is empty. Too much info? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can INDIRECT function reference a cell that contains a formula
The formula won't work in a worksheet cell.
DON'T enclose the range names in quotes. They'll be evaluated as TEXT strings. See my other reply! Biff "Steve E" wrote in message ... Biff, When I tried the same formula in a blank cell on my worksheet it evaluated to a $VALUE error. When I put the listA, listB, listC named ranges in quotes ie "listA, "listB", "listC" it returned the correct match... but when I tried this same thing in the data validation formula bar I get an error (of course, non-traceable). ?? "Steve E" wrote: Hi BIff, Thanks for the help. Unfortunately, I seem to only get listB to populate the drop down... even though cell AC18 evaluates to listA... Any ideas? And... wouldn't it be nice if you could cut and paste into the formula bar in the data validation box? Appreciate your help. Steve "Biff" wrote: Hi! Using Indirect and dynamic ranges won't work. The reason the drop down doesn't work is because =INDIRECT(AC18) evaluates to a #REF! error. Try this as the drop down source: List the named ranges somewhere,say, G1:I1. G1 = listA H1 = listB I1 = listC Or, you may already have the names listed somewhere as the column headers for the named ranges. If so just refer to those cells: =CHOOSE(MATCH(AC18,G1:I1,0),listA,listB,listC) You can have up to 29 named ranges and use CHOOSE. When your formula returns "Get Quote" that'll cause the source formula to error and the drop down won't work. Biff "Steve E" wrote in message ... I am trying to limit data entry options into a worksheet based on other information already 'correctly' entered. Based on a number of criteria I return a Named Range as a result of the formula: I am using =INDIRECT(AC18) as the data validation formula Cell AC18: =IF(ISNA(IF(TubeRequired=VLOOKUP(SystemSelected,'I nput Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,5,FALSE),"Get Quote"))),"",(IF(TubeRequired=VLOOKUP(SystemSelect ed,'Input Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,5,FALSE),"Get Quote")))) The resulting named ranges in the 'InputLists'!$A$3:$E$40 are BracketList_A, BracketList_B and BracketList_C where these named ranges containg the brackets that are appropriate for selection by the user. These named ranges are Dynamic Ranges allowing us to update the data table as needed. Based on what I'd read in Debra Dalgleish's site and in the help files I thought that this would work... I don't get any errors but the drop down list in the data validation is empty. Too much info? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can INDIRECT function reference a cell that contains a formula
wouldn't it be nice if you could cut and paste into the formula bar
in the data validation box? You can! I often write the formula on the worksheet then copy/paste it into the box. Those little boxes are a real PITA! CTRL C to copy CTRL V to paste Thanks for the help. Unfortunately, I seem to only get listB to populate the drop down... even though cell AC18 evaluates to listA... Did you make a list of the range names? Or, did you already have them listed as column headers? What are the EXACT cells adresses of those names? Post the EXACT formula you tried. If all else fails you can send the file to me and I'll set it up for you. Or, I can put together a sample file that demonstrates this. Biff "Steve E" wrote in message ... Hi BIff, Thanks for the help. Unfortunately, I seem to only get listB to populate the drop down... even though cell AC18 evaluates to listA... Any ideas? And... wouldn't it be nice if you could cut and paste into the formula bar in the data validation box? Appreciate your help. Steve "Biff" wrote: Hi! Using Indirect and dynamic ranges won't work. The reason the drop down doesn't work is because =INDIRECT(AC18) evaluates to a #REF! error. Try this as the drop down source: List the named ranges somewhere,say, G1:I1. G1 = listA H1 = listB I1 = listC Or, you may already have the names listed somewhere as the column headers for the named ranges. If so just refer to those cells: =CHOOSE(MATCH(AC18,G1:I1,0),listA,listB,listC) You can have up to 29 named ranges and use CHOOSE. When your formula returns "Get Quote" that'll cause the source formula to error and the drop down won't work. Biff "Steve E" wrote in message ... I am trying to limit data entry options into a worksheet based on other information already 'correctly' entered. Based on a number of criteria I return a Named Range as a result of the formula: I am using =INDIRECT(AC18) as the data validation formula Cell AC18: =IF(ISNA(IF(TubeRequired=VLOOKUP(SystemSelected,'I nput Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,5,FALSE),"Get Quote"))),"",(IF(TubeRequired=VLOOKUP(SystemSelect ed,'Input Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,5,FALSE),"Get Quote")))) The resulting named ranges in the 'InputLists'!$A$3:$E$40 are BracketList_A, BracketList_B and BracketList_C where these named ranges containg the brackets that are appropriate for selection by the user. These named ranges are Dynamic Ranges allowing us to update the data table as needed. Based on what I'd read in Debra Dalgleish's site and in the help files I thought that this would work... I don't get any errors but the drop down list in the data validation is empty. Too much info? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can INDIRECT function reference a cell that contains a formula
Hi Biff,
Finally able to get back to solving this problem. The exact formula that I entered in the data validation formula bar is: =CHOOSE(MATCH(AC18,$AC$11:$AC$13,0),BracketList_A, BracketList_B,BracketList_C) My list of range names is AC11=BracketList_A, AC12=BracketListB, AC13=BracketLIst_C The named ranges are on a seperate worksheet in the same workbook Regardless of what AC18 (see orig opst for that formula) evaluates to I only get BracketList_B range in the drop down list... I can send you the file if it will be easier... THanks, Steve "Biff" wrote: wouldn't it be nice if you could cut and paste into the formula bar in the data validation box? You can! I often write the formula on the worksheet then copy/paste it into the box. Those little boxes are a real PITA! CTRL C to copy CTRL V to paste Thanks for the help. Unfortunately, I seem to only get listB to populate the drop down... even though cell AC18 evaluates to listA... Did you make a list of the range names? Or, did you already have them listed as column headers? What are the EXACT cells adresses of those names? Post the EXACT formula you tried. If all else fails you can send the file to me and I'll set it up for you. Or, I can put together a sample file that demonstrates this. Biff "Steve E" wrote in message ... Hi BIff, Thanks for the help. Unfortunately, I seem to only get listB to populate the drop down... even though cell AC18 evaluates to listA... Any ideas? And... wouldn't it be nice if you could cut and paste into the formula bar in the data validation box? Appreciate your help. Steve "Biff" wrote: Hi! Using Indirect and dynamic ranges won't work. The reason the drop down doesn't work is because =INDIRECT(AC18) evaluates to a #REF! error. Try this as the drop down source: List the named ranges somewhere,say, G1:I1. G1 = listA H1 = listB I1 = listC Or, you may already have the names listed somewhere as the column headers for the named ranges. If so just refer to those cells: =CHOOSE(MATCH(AC18,G1:I1,0),listA,listB,listC) You can have up to 29 named ranges and use CHOOSE. When your formula returns "Get Quote" that'll cause the source formula to error and the drop down won't work. Biff "Steve E" wrote in message ... I am trying to limit data entry options into a worksheet based on other information already 'correctly' entered. Based on a number of criteria I return a Named Range as a result of the formula: I am using =INDIRECT(AC18) as the data validation formula Cell AC18: =IF(ISNA(IF(TubeRequired=VLOOKUP(SystemSelected,'I nput Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,5,FALSE),"Get Quote"))),"",(IF(TubeRequired=VLOOKUP(SystemSelect ed,'Input Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,5,FALSE),"Get Quote")))) The resulting named ranges in the 'InputLists'!$A$3:$E$40 are BracketList_A, BracketList_B and BracketList_C where these named ranges containg the brackets that are appropriate for selection by the user. These named ranges are Dynamic Ranges allowing us to update the data table as needed. Based on what I'd read in Debra Dalgleish's site and in the help files I thought that this would work... I don't get any errors but the drop down list in the data validation is empty. Too much info? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can INDIRECT function reference a cell that contains a formula
I can send you the file if it will be easier...
Ok, I'm at: xl can help at comcast period net Remove "can" and and change the obvious. Biff "Steve E" wrote in message ... Hi Biff, Finally able to get back to solving this problem. The exact formula that I entered in the data validation formula bar is: =CHOOSE(MATCH(AC18,$AC$11:$AC$13,0),BracketList_A, BracketList_B,BracketList_C) My list of range names is AC11=BracketList_A, AC12=BracketListB, AC13=BracketLIst_C The named ranges are on a seperate worksheet in the same workbook Regardless of what AC18 (see orig opst for that formula) evaluates to I only get BracketList_B range in the drop down list... I can send you the file if it will be easier... THanks, Steve "Biff" wrote: wouldn't it be nice if you could cut and paste into the formula bar in the data validation box? You can! I often write the formula on the worksheet then copy/paste it into the box. Those little boxes are a real PITA! CTRL C to copy CTRL V to paste Thanks for the help. Unfortunately, I seem to only get listB to populate the drop down... even though cell AC18 evaluates to listA... Did you make a list of the range names? Or, did you already have them listed as column headers? What are the EXACT cells adresses of those names? Post the EXACT formula you tried. If all else fails you can send the file to me and I'll set it up for you. Or, I can put together a sample file that demonstrates this. Biff "Steve E" wrote in message ... Hi BIff, Thanks for the help. Unfortunately, I seem to only get listB to populate the drop down... even though cell AC18 evaluates to listA... Any ideas? And... wouldn't it be nice if you could cut and paste into the formula bar in the data validation box? Appreciate your help. Steve "Biff" wrote: Hi! Using Indirect and dynamic ranges won't work. The reason the drop down doesn't work is because =INDIRECT(AC18) evaluates to a #REF! error. Try this as the drop down source: List the named ranges somewhere,say, G1:I1. G1 = listA H1 = listB I1 = listC Or, you may already have the names listed somewhere as the column headers for the named ranges. If so just refer to those cells: =CHOOSE(MATCH(AC18,G1:I1,0),listA,listB,listC) You can have up to 29 named ranges and use CHOOSE. When your formula returns "Get Quote" that'll cause the source formula to error and the drop down won't work. Biff "Steve E" wrote in message ... I am trying to limit data entry options into a worksheet based on other information already 'correctly' entered. Based on a number of criteria I return a Named Range as a result of the formula: I am using =INDIRECT(AC18) as the data validation formula Cell AC18: =IF(ISNA(IF(TubeRequired=VLOOKUP(SystemSelected,'I nput Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,5,FALSE),"Get Quote"))),"",(IF(TubeRequired=VLOOKUP(SystemSelect ed,'Input Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input Lists'!$A$3:$E$40,5,FALSE),"Get Quote")))) The resulting named ranges in the 'InputLists'!$A$3:$E$40 are BracketList_A, BracketList_B and BracketList_C where these named ranges containg the brackets that are appropriate for selection by the user. These named ranges are Dynamic Ranges allowing us to update the data table as needed. Based on what I'd read in Debra Dalgleish's site and in the help files I thought that this would work... I don't get any errors but the drop down list in the data validation is empty. Too much info? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup Data in two seperate Spreadsheets | Excel Worksheet Functions | |||
| CELL Function: cell reference by formula | Excel Worksheet Functions | |||
| use a formula as a cell reference in a function | Excel Worksheet Functions | |||
| referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
| GET.CELL | Excel Worksheet Functions | |||
Hybrid Mode
