sheet variable

[wagz]

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]

maybe...

=OFFSET(indirect("'"&d9&"'!C6"),COUNTA(indirect("'"&d9&"!C6:C364"))-6,-1)

 

[wagz]

This returns "True" but not the value.

maybe...

=OFFSET(indirect("'"&d9&"'!C6"),COUNTA(indirect("'"&d9&"!C6:C364"))-6,-1)

 

[Dave Peterson]

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]

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]

For me to have a guess, you'll still have to post the stuff I mentioned before.