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.

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

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
> .
> 

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

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
> .
> 

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