Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet variable
I am looking for a way to use a list value from a data validation (cell D9)
to use as a value in =OFFSET(Merry!C6,COUNTA(Merry!C6:C364)-6,-1) So Merry would be removed and the variable put in it's place. Each time I reference the cell D9 I get a #VALUE! error. Any help would be appriciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet variable
maybe...
=OFFSET(indirect("'"&d9&"'!C6"),COUNTA(indirect("' "&d9&"!C6:C364"))-6,-1) wagz wrote: I am looking for a way to use a list value from a data validation (cell D9) to use as a value in =OFFSET(Merry!C6,COUNTA(Merry!C6:C364)-6,-1) So Merry would be removed and the variable put in it's place. Each time I reference the cell D9 I get a #VALUE! error. Any help would be appriciated. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet variable
This returns "True" but not the value.
"Dave Peterson" wrote: maybe... =OFFSET(indirect("'"&d9&"'!C6"),COUNTA(indirect("' "&d9&"!C6:C364"))-6,-1) wagz wrote: I am looking for a way to use a list value from a data validation (cell D9) to use as a value in =OFFSET(Merry!C6,COUNTA(Merry!C6:C364)-6,-1) So Merry would be removed and the variable put in it's place. Each time I reference the cell D9 I get a #VALUE! error. Any help would be appriciated. -- Dave Peterson . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet variable
I think you changed the formula.
Try it again. If it doesn't work, then post your formula and the values in D9, C6 and the value that =counta() returns. wagz wrote: This returns "True" but not the value. "Dave Peterson" wrote: maybe... =OFFSET(indirect("'"&d9&"'!C6"),COUNTA(indirect("' "&d9&"!C6:C364"))-6,-1) wagz wrote: I am looking for a way to use a list value from a data validation (cell D9) to use as a value in =OFFSET(Merry!C6,COUNTA(Merry!C6:C364)-6,-1) So Merry would be removed and the variable put in it's place. Each time I reference the cell D9 I get a #VALUE! error. Any help would be appriciated. -- Dave Peterson . -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet variable
I have gotten past the true return but now it doesn't look for the first
empty cell as the reference in the code. So 3,-1 pulls the value from begining C column instead of using the counta reference. "Dave Peterson" wrote: I think you changed the formula. Try it again. If it doesn't work, then post your formula and the values in D9, C6 and the value that =counta() returns. wagz wrote: This returns "True" but not the value. "Dave Peterson" wrote: maybe... =OFFSET(indirect("'"&d9&"'!C6"),COUNTA(indirect("' "&d9&"!C6:C364"))-6,-1) wagz wrote: I am looking for a way to use a list value from a data validation (cell D9) to use as a value in =OFFSET(Merry!C6,COUNTA(Merry!C6:C364)-6,-1) So Merry would be removed and the variable put in it's place. Each time I reference the cell D9 I get a #VALUE! error. Any help would be appriciated. -- Dave Peterson . -- Dave Peterson . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet variable
For me to have a guess, you'll still have to post the stuff I mentioned before.
wagz wrote: I have gotten past the true return but now it doesn't look for the first empty cell as the reference in the code. So 3,-1 pulls the value from begining C column instead of using the counta reference. "Dave Peterson" wrote: I think you changed the formula. Try it again. If it doesn't work, then post your formula and the values in D9, C6 and the value that =counta() returns. wagz wrote: This returns "True" but not the value. "Dave Peterson" wrote: maybe... =OFFSET(indirect("'"&d9&"'!C6"),COUNTA(indirect("' "&d9&"!C6:C364"))-6,-1) wagz wrote: I am looking for a way to use a list value from a data validation (cell D9) to use as a value in =OFFSET(Merry!C6,COUNTA(Merry!C6:C364)-6,-1) So Merry would be removed and the variable put in it's place. Each time I reference the cell D9 I get a #VALUE! error. Any help would be appriciated. -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How To make a sheet reference Variable (eq: sum(sheet!D2:H2)) | Excel Discussion (Misc queries) | |||
passing a variable from sheet to form to another sheet | Excel Programming | |||
selecting sheet name in another workbook by variable (same sheet name) | Excel Programming | |||
Variable sheet and Formula Sheet | Excel Discussion (Misc queries) | |||
variable in a link where the variable is the name of the sheet | Excel Worksheet Functions |