| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function to retrieve array name
i have a spread sheet with array names in each cell
I have a working formula to retrieve the correct array name The question =VLOOKUP(U51,MFFC!$A$2:$M$11,2) U51 is the cell for an age group (17) the spread sheet MFFC has the age groups starting the row and the cells contain array names. curlup pushups run swim bike eliptical 17 curlpt17 curltl17 pupt17 putl17 runpt17 runtl17 swimpt17 swimtl17 bikept17 biketl17 elippt17 eliptl17 the problem is that once retrieved, i cannot get the function to use the array name because it is deemed as just text and not recognized as a valid array name. this is the last step to automate my form and it is proving formidable. my only other alternative is to cut and paste the formula from another matching cell. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
function to retrieve array name
The below would reference the array...
=INDIRECT(VLOOKUP(U51,MFFC!$A$2:$M$11,2,0)) If this post helps click Yes --------------- Jacob Skaria "Monty" wrote: i have a spread sheet with array names in each cell I have a working formula to retrieve the correct array name The question =VLOOKUP(U51,MFFC!$A$2:$M$11,2) U51 is the cell for an age group (17) the spread sheet MFFC has the age groups starting the row and the cells contain array names. curlup pushups run swim bike eliptical 17 curlpt17 curltl17 pupt17 putl17 runpt17 runtl17 swimpt17 swimtl17 bikept17 biketl17 elippt17 eliptl17 the problem is that once retrieved, i cannot get the function to use the array name because it is deemed as just text and not recognized as a valid array name. this is the last step to automate my form and it is proving formidable. my only other alternative is to cut and paste the formula from another matching cell. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
function to retrieve array name
How do you intend to use the array name? How is the array defined?
If the VLOOKUP returns a text string that represents a defined name this is how you need to express it: INDIRECT(VLOOKUP(U51,MFFC!$A$2:$M$11,2)) -- Biff Microsoft Excel MVP "Monty" wrote in message ... i have a spread sheet with array names in each cell I have a working formula to retrieve the correct array name The question =VLOOKUP(U51,MFFC!$A$2:$M$11,2) U51 is the cell for an age group (17) the spread sheet MFFC has the age groups starting the row and the cells contain array names. curlup pushups run swim bike eliptical 17 curlpt17 curltl17 pupt17 putl17 runpt17 runtl17 swimpt17 swimtl17 bikept17 biketl17 elippt17 eliptl17 the problem is that once retrieved, i cannot get the function to use the array name because it is deemed as just text and not recognized as a valid array name. this is the last step to automate my form and it is proving formidable. my only other alternative is to cut and paste the formula from another matching cell. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
function to retrieve array name
=SUMIF(curlpt45,N52,curltl45)
this is the final formula but the array names need swapped out with the value derived from =VLOOKUP(U51,MFFC!$A$2:$M$11,2) which should be the value "curlpt45" because U51 contains the number 45 for example =SUMIF(=VLOOKUP(U51,MFFC!$A$2:$M$11,2),N52,=VLOOKU P(U51,MFFC!$A$2:$M$11,2)) I have been manually typing in the curlpt45 into the formula. when the age group changes in U column then the curlpt## has to change to match for the formula to calculate the correct value from the SUMIF. =INDIRECT(VLOOKUP(U51,MFFC!$A$2:$M$11,2,0)) returned 100 and it should have been curlpt45 "T. Valko" wrote: How do you intend to use the array name? How is the array defined? If the VLOOKUP returns a text string that represents a defined name this is how you need to express it: INDIRECT(VLOOKUP(U51,MFFC!$A$2:$M$11,2)) -- Biff Microsoft Excel MVP "Monty" wrote in message ... i have a spread sheet with array names in each cell I have a working formula to retrieve the correct array name The question =VLOOKUP(U51,MFFC!$A$2:$M$11,2) U51 is the cell for an age group (17) the spread sheet MFFC has the age groups starting the row and the cells contain array names. curlup pushups run swim bike eliptical 17 curlpt17 curltl17 pupt17 putl17 runpt17 runtl17 swimpt17 swimtl17 bikept17 biketl17 elippt17 eliptl17 the problem is that once retrieved, i cannot get the function to use the array name because it is deemed as just text and not recognized as a valid array name. this is the last step to automate my form and it is proving formidable. my only other alternative is to cut and paste the formula from another matching cell. . |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
function to retrieve array name
=SUMIF(curlpt45,N52,curltl45)
this is the final formula but the array names need swapped out with the value derived from =VLOOKUP(U51,MFFC!$A$2:$M$11,2) which should be the value "curlpt45" because U51 contains the number 45 for example =SUMIF(=VLOOKUP(U51,MFFC!$A$2:$M$11,2),N52,=VLOOKU P(U51,MFFC!$A$2:$M$11,2)) I have been manually typing in the curlpt45 into the formula. when the age group changes in U column then the curlpt## has to change to match for the formula to calculate the correct value from the SUMIF. =INDIRECT(VLOOKUP(U51,MFFC!$A$2:$M$11,2,0)) returned 100 and it should have been curlpt45 "T. Valko" wrote: How do you intend to use the array name? How is the array defined? If the VLOOKUP returns a text string that represents a defined name this is how you need to express it: INDIRECT(VLOOKUP(U51,MFFC!$A$2:$M$11,2)) -- Biff Microsoft Excel MVP "Monty" wrote in message ... i have a spread sheet with array names in each cell I have a working formula to retrieve the correct array name The question =VLOOKUP(U51,MFFC!$A$2:$M$11,2) U51 is the cell for an age group (17) the spread sheet MFFC has the age groups starting the row and the cells contain array names. curlup pushups run swim bike eliptical 17 curlpt17 curltl17 pupt17 putl17 runpt17 runtl17 swimpt17 swimtl17 bikept17 biketl17 elippt17 eliptl17 the problem is that once retrieved, i cannot get the function to use the array name because it is deemed as just text and not recognized as a valid array name. this is the last step to automate my form and it is proving formidable. my only other alternative is to cut and paste the formula from another matching cell. . |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
function to retrieve array name
Try it like this:
=SUMIF(INDIRECT(VLOOKUP(U51,MFFC!$A$2:$M$11,2,0)), N52,curltl45) Note however, this will not work if the named range curlpt45 is defined using functions like OFFSET and INDEX. -- Biff Microsoft Excel MVP "Monty" wrote in message ... =SUMIF(curlpt45,N52,curltl45) this is the final formula but the array names need swapped out with the value derived from =VLOOKUP(U51,MFFC!$A$2:$M$11,2) which should be the value "curlpt45" because U51 contains the number 45 for example =SUMIF(=VLOOKUP(U51,MFFC!$A$2:$M$11,2),N52,=VLOOKU P(U51,MFFC!$A$2:$M$11,2)) I have been manually typing in the curlpt45 into the formula. when the age group changes in U column then the curlpt## has to change to match for the formula to calculate the correct value from the SUMIF. =INDIRECT(VLOOKUP(U51,MFFC!$A$2:$M$11,2,0)) returned 100 and it should have been curlpt45 "T. Valko" wrote: How do you intend to use the array name? How is the array defined? If the VLOOKUP returns a text string that represents a defined name this is how you need to express it: INDIRECT(VLOOKUP(U51,MFFC!$A$2:$M$11,2)) -- Biff Microsoft Excel MVP "Monty" wrote in message ... i have a spread sheet with array names in each cell I have a working formula to retrieve the correct array name The question =VLOOKUP(U51,MFFC!$A$2:$M$11,2) U51 is the cell for an age group (17) the spread sheet MFFC has the age groups starting the row and the cells contain array names. curlup pushups run swim bike eliptical 17 curlpt17 curltl17 pupt17 putl17 runpt17 runtl17 swimpt17 swimtl17 bikept17 biketl17 elippt17 eliptl17 the problem is that once retrieved, i cannot get the function to use the array name because it is deemed as just text and not recognized as a valid array name. this is the last step to automate my form and it is proving formidable. my only other alternative is to cut and paste the formula from another matching cell. . |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
function to retrieve array name
If VLOOKUP(U51,MFFC!$A$2:$M$11,2,0)) returns the text 'curlpt45'
=INDIRECT(VLOOKUP(U51,MFFC!$A$2:$M$11,2,0)) will refer this range.. If 'curlpt45' refers to a single cell and the cell value is 100 then it returns this cell value If this post helps click Yes --------------- Jacob Skaria "Monty" wrote: =SUMIF(curlpt45,N52,curltl45) this is the final formula but the array names need swapped out with the value derived from =VLOOKUP(U51,MFFC!$A$2:$M$11,2) which should be the value "curlpt45" because U51 contains the number 45 for example =SUMIF(=VLOOKUP(U51,MFFC!$A$2:$M$11,2),N52,=VLOOKU P(U51,MFFC!$A$2:$M$11,2)) I have been manually typing in the curlpt45 into the formula. when the age group changes in U column then the curlpt## has to change to match for the formula to calculate the correct value from the SUMIF. =INDIRECT(VLOOKUP(U51,MFFC!$A$2:$M$11,2,0)) returned 100 and it should have been curlpt45 "T. Valko" wrote: How do you intend to use the array name? How is the array defined? If the VLOOKUP returns a text string that represents a defined name this is how you need to express it: INDIRECT(VLOOKUP(U51,MFFC!$A$2:$M$11,2)) -- Biff Microsoft Excel MVP "Monty" wrote in message ... i have a spread sheet with array names in each cell I have a working formula to retrieve the correct array name The question =VLOOKUP(U51,MFFC!$A$2:$M$11,2) U51 is the cell for an age group (17) the spread sheet MFFC has the age groups starting the row and the cells contain array names. curlup pushups run swim bike eliptical 17 curlpt17 curltl17 pupt17 putl17 runpt17 runtl17 swimpt17 swimtl17 bikept17 biketl17 elippt17 eliptl17 the problem is that once retrieved, i cannot get the function to use the array name because it is deemed as just text and not recognized as a valid array name. this is the last step to automate my form and it is proving formidable. my only other alternative is to cut and paste the formula from another matching cell. . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Is it possible to use the lookup function to retrieve objects? | Excel Discussion (Misc queries) | |||
| Is there a function/formula to retrieve the weeknumber? | Excel Worksheet Functions | |||
| function to retrieve a list of unique characters from a column | Excel Worksheet Functions | |||
| Worksheet function to retrieve cell value based on row an col numb | Excel Discussion (Misc queries) | |||
| Can I retrieve a value and its format using the minimum function? | Excel Worksheet Functions | |||
Linear Mode
