Re: VLOOKUP vs Macros

[Johnny Rosenberg]

2013/10/1 John Meyer 

> Okay, I've put up two screenshots.
>

Where? You forgot the link to them. Or did you attach them? You can't do
that, they will be stripped off. Upload them somewhere and give us the link.


Johnny Rosenberg


>
> the first contains the formula and what is listed in  C1.  The next is the
> names of the individual sheets.
>
>
>
>
> On 10/1/2013 8:21 AM, John Meyer wrote:
>
>> Same Err:501.  I'll post up more in the office.
>>
>> On 10/1/2013 8:11 AM, Johnny Rosenberg wrote:
>>
>>> 2013/9/30 John Meyer 
>>>
>>>  Still doesn't work.  Back to the drawing board.
>>>>
>>>>  Exactly what does your current formula look like and what error
>>> message do
>>> you get? Still 501?
>>>
>>>
>>>
>>> Johnny Rosenberg
>>>
>>>
>>>
>>>> On Mon, Sep 30, 2013 at 2:32 PM, John Meyer
>>>> wrote:
>>>>
>>>>  Derp, just saw that.
>>>>>
>>>>> Thanks.
>>>>>
>>>>>
>>>>> On Mon, Sep 30, 2013 at 2:01 PM, Johnny Rosenberg <
>>>>>
>>>> gurus.knu...@gmail.com>wrote:
>>>>
>>>>> 2013/9/30 John Meyer 
>>>>>>
>>>>>>  Sorry, that e-mail got sent off too quickly.  I'm trying this formula
>>>>>>>
>>>>>>> =COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)
>>>>>>>
>>>>>>> C1 contains the name of the worksheet ("09-27-13") and I'm trying to
>>>>>>>
>>>>>> use
>>>>
>>>>> it.  However, it keeps giving me an err:501.
>>>>>>>
>>>>>>>  Hint 1:
>>>>>> Use the builtin help and search for 501. It will take you to the error
>>>>>> codes. 501 means something like ”invalid character”. In this case it
>>>>>>
>>>>> seems
>>>>
>>>>> like you use ”,” instead of ”;” as parameter separators. I thought that
>>>>>> there was a setting for that somewhere, but now I can't find it, so I
>>>>>> guess
>>>>>> that is a LibreOffice feature, but I'm not sure. I used LibreOffice
>>>>>> for
>>>>>>
>>>>> a
>>>>
>>>>> couple of years but I am back with Apache OpenOffice again, since
>>>>>> LibreOffice was way too unstable for me. It actually destroyed one of
>>>>>> my
>>>>>> spreadsheets but Apache OpenOffice fixed it for me, that's why I'm
>>>>>> back…
>>>>>> :D
>>>>>>
>>>>>> Anyway, replace those commas with semi-colons and I think it will
>>>>>> work.
>>>>>>
>>>>>> Hint 2:
>>>>>> If you are working with a big formula and it doesn't work, hit Ctrl+F2
>>>>>> (select the cell that you are working with first) and you are able to
>>>>>> study
>>>>>> your formula a little better. For instance you can see sub values by
>>>>>> placing the cursor on different places in the formula. That way it's a
>>>>>> little easier to find WHERE the error is.
>>>>>>
>>>>>>
>>>>>>
>>>>>> Regards
>>>>>>
>>>>>>
>>>>>> Johnny Rosenberg
>>>>>>
>>>>>>
>>>>>>
>>>>>>> On Mon, Sep 30, 2013 at 1:22 PM, John Meyer
>>>>>>> wrote:
>>>>>>>
>>>>>>>  =COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)
>>>>>>>>
>>>>>>>>
>>>>>>>> On Mon, Sep 30, 2013 at 1:18 PM, John Meyer <
>>>>>>>>
>>>>>>> johnme...@pueblocomputing.com
>>>>>>>
>>>>>>>> wrote:
>>>>>>>>> So I guess I'm doing something wrong here.
>>>>>>>>>
>>>>>>>>> =COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)
>>>>>>>>>
>>>>>>>>> Returns an Err:501.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Sun, Sep 29, 2013 at 10:59 PM, Coreurus 
>>>>>>>>>
>>>>>>>> wrote:
>>>>
>>>>>  <
>>>>>>>>>> - Original Message --(Start-looking-by-there)
>>>>>>>>>> From: John Meyer >
>>>>>>>>>> To: 
>>>>>>>>>> Sent: Saturday, 28 September, 2013 04:40 PM
>>>>>>>>>> Subject: Re: VLOOKUP vs Macros
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>  I sent that a little too soon.
>>>>>>>>>>>
>>>>>>>>>>> The title refers to the fact that I was looking into another
>>>>>>>>>>>
>>>>>>>>>> solution.
>>>>>>>
>>>>>>>>  the sales sheet for each day is broken down with the following
>>>>>>>>>>>
>>>>>>>>>> information:
>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> Date, EmpID
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> Currently, what I am doing is separating them by date.
>>>>>>>>>>>
>>>>>>>>>>   However, I
>>>>
>>>>> was
>>>>>>>
>>>>>>>>  thinking a much less cluttered solution would involve pulling
>>>>>>>>>>>
>>>>>>>>>> the
>>>>
>>>>> date
>>>>>>>
>>>>>>>>  from the bonus calculation field and then doing either a VLOOKUP
>>>>>>>>>>>
>>>>>>>>>> with a
>>>>>>>
>>>>>>>>  count or Macro.  Where would I start looking if I wanted to do
>>>>>>>>>>>
>>>>>>>>>> either
>>>>>>
>>>>>>>  one of those solutions?
>>>>>>>>>>> Back to searching for the answer.
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> -
>>>>>>
>>>>>>>  To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
>>>>>>>>>>> For additional commands, e-mail:
>>>>>>>>>>>
>>>>>>>>>> users-h...@openoffice.apache.org
>>>>
>>>>>
>>>>>>>>>>
>>>>>
>>
>
>
>
> -
> To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
> For additional commands, e-mail: users-h...@openoffice.apache.org
>

Re: VLOOKUP vs Macros

[John Meyer]

Okay, I've put up two screenshots.

the first contains the formula and what is listed in  C1.  The next is 
the names of the individual sheets.




On 10/1/2013 8:21 AM, John Meyer wrote:

Same Err:501.  I'll post up more in the office.

On 10/1/2013 8:11 AM, Johnny Rosenberg wrote:

2013/9/30 John Meyer 


Still doesn't work.  Back to the drawing board.

Exactly what does your current formula look like and what error 
message do

you get? Still 501?



Johnny Rosenberg




On Mon, Sep 30, 2013 at 2:32 PM, John Meyer
wrote:


Derp, just saw that.

Thanks.


On Mon, Sep 30, 2013 at 2:01 PM, Johnny Rosenberg <

gurus.knu...@gmail.com>wrote:

2013/9/30 John Meyer 

Sorry, that e-mail got sent off too quickly.  I'm trying this 
formula


=COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)

C1 contains the name of the worksheet ("09-27-13") and I'm trying to

use

it.  However, it keeps giving me an err:501.


Hint 1:
Use the builtin help and search for 501. It will take you to the 
error

codes. 501 means something like ”invalid character”. In this case it

seems
like you use ”,” instead of ”;” as parameter separators. I thought 
that

there was a setting for that somewhere, but now I can't find it, so I
guess
that is a LibreOffice feature, but I'm not sure. I used 
LibreOffice for

a

couple of years but I am back with Apache OpenOffice again, since
LibreOffice was way too unstable for me. It actually destroyed one 
of my
spreadsheets but Apache OpenOffice fixed it for me, that's why I'm 
back…

:D

Anyway, replace those commas with semi-colons and I think it will 
work.


Hint 2:
If you are working with a big formula and it doesn't work, hit 
Ctrl+F2

(select the cell that you are working with first) and you are able to
study
your formula a little better. For instance you can see sub values by
placing the cursor on different places in the formula. That way 
it's a

little easier to find WHERE the error is.



Regards


Johnny Rosenberg




On Mon, Sep 30, 2013 at 1:22 PM, John Meyer
wrote:


=COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)


On Mon, Sep 30, 2013 at 1:18 PM, John Meyer <

johnme...@pueblocomputing.com

wrote:
So I guess I'm doing something wrong here.

=COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)

Returns an Err:501.



On Sun, Sep 29, 2013 at 10:59 PM, Coreurus 

wrote:

<
- Original Message --(Start-looking-by-there)
From: John Meyer >
To: 
Sent: Saturday, 28 September, 2013 04:40 PM
Subject: Re: VLOOKUP vs Macros



I sent that a little too soon.

The title refers to the fact that I was looking into another

solution.

the sales sheet for each day is broken down with the following

information:


Date, EmpID



Currently, what I am doing is separating them by date.

  However, I

was

thinking a much less cluttered solution would involve pulling

the

date

from the bonus calculation field and then doing either a VLOOKUP

with a

count or Macro.  Where would I start looking if I wanted to do

either

one of those solutions?
Back to searching for the answer.





-

To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
For additional commands, e-mail:

users-h...@openoffice.apache.org










-
To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
For additional commands, e-mail: users-h...@openoffice.apache.org

Re: VLOOKUP vs Macros

[John Meyer]

Same Err:501.  I'll post up more in the office.

On 10/1/2013 8:11 AM, Johnny Rosenberg wrote:

2013/9/30 John Meyer 


Still doesn't work.  Back to the drawing board.


Exactly what does your current formula look like and what error message do
you get? Still 501?



Johnny Rosenberg




On Mon, Sep 30, 2013 at 2:32 PM, John Meyer
wrote:


Derp, just saw that.

Thanks.


On Mon, Sep 30, 2013 at 2:01 PM, Johnny Rosenberg <

gurus.knu...@gmail.com>wrote:

2013/9/30 John Meyer 


Sorry, that e-mail got sent off too quickly.  I'm trying this formula

=COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)

C1 contains the name of the worksheet ("09-27-13") and I'm trying to

use

it.  However, it keeps giving me an err:501.


Hint 1:
Use the builtin help and search for 501. It will take you to the error
codes. 501 means something like ”invalid character”. In this case it

seems

like you use ”,” instead of ”;” as parameter separators. I thought that
there was a setting for that somewhere, but now I can't find it, so I
guess
that is a LibreOffice feature, but I'm not sure. I used LibreOffice for

a

couple of years but I am back with Apache OpenOffice again, since
LibreOffice was way too unstable for me. It actually destroyed one of my
spreadsheets but Apache OpenOffice fixed it for me, that's why I'm back…
:D

Anyway, replace those commas with semi-colons and I think it will work.

Hint 2:
If you are working with a big formula and it doesn't work, hit Ctrl+F2
(select the cell that you are working with first) and you are able to
study
your formula a little better. For instance you can see sub values by
placing the cursor on different places in the formula. That way it's a
little easier to find WHERE the error is.



Regards


Johnny Rosenberg




On Mon, Sep 30, 2013 at 1:22 PM, John Meyer
wrote:


=COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)


On Mon, Sep 30, 2013 at 1:18 PM, John Meyer <

johnme...@pueblocomputing.com

wrote:
So I guess I'm doing something wrong here.

=COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)

Returns an Err:501.



On Sun, Sep 29, 2013 at 10:59 PM, Coreurus 

wrote:

<
- Original Message --(Start-looking-by-there)
From: John Meyer >
To: 
Sent: Saturday, 28 September, 2013 04:40 PM
Subject: Re: VLOOKUP vs Macros



I sent that a little too soon.

The title refers to the fact that I was looking into another

solution.

the sales sheet for each day is broken down with the following

information:


Date, EmpID



Currently, what I am doing is separating them by date.

  However, I

was

thinking a much less cluttered solution would involve pulling

the

date

from the bonus calculation field and then doing either a VLOOKUP

with a

count or Macro.  Where would I start looking if I wanted to do

either

one of those solutions?
Back to searching for the answer.





-

To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
For additional commands, e-mail:

users-h...@openoffice.apache.org







-
To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
For additional commands, e-mail: users-h...@openoffice.apache.org

Re: VLOOKUP vs Macros

[Johnny Rosenberg]

2013/9/30 John Meyer 

> Still doesn't work.  Back to the drawing board.
>

Exactly what does your current formula look like and what error message do
you get? Still 501?



Johnny Rosenberg


>
>
> On Mon, Sep 30, 2013 at 2:32 PM, John Meyer
> wrote:
>
> > Derp, just saw that.
> >
> > Thanks.
> >
> >
> > On Mon, Sep 30, 2013 at 2:01 PM, Johnny Rosenberg <
> gurus.knu...@gmail.com>wrote:
> >
> >> 2013/9/30 John Meyer 
> >>
> >> > Sorry, that e-mail got sent off too quickly.  I'm trying this formula
> >> >
> >> > =COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)
> >> >
> >> > C1 contains the name of the worksheet ("09-27-13") and I'm trying to
> use
> >> > it.  However, it keeps giving me an err:501.
> >> >
> >>
> >> Hint 1:
> >> Use the builtin help and search for 501. It will take you to the error
> >> codes. 501 means something like ”invalid character”. In this case it
> seems
> >> like you use ”,” instead of ”;” as parameter separators. I thought that
> >> there was a setting for that somewhere, but now I can't find it, so I
> >> guess
> >> that is a LibreOffice feature, but I'm not sure. I used LibreOffice for
> a
> >> couple of years but I am back with Apache OpenOffice again, since
> >> LibreOffice was way too unstable for me. It actually destroyed one of my
> >> spreadsheets but Apache OpenOffice fixed it for me, that's why I'm back…
> >> :D
> >>
> >> Anyway, replace those commas with semi-colons and I think it will work.
> >>
> >> Hint 2:
> >> If you are working with a big formula and it doesn't work, hit Ctrl+F2
> >> (select the cell that you are working with first) and you are able to
> >> study
> >> your formula a little better. For instance you can see sub values by
> >> placing the cursor on different places in the formula. That way it's a
> >> little easier to find WHERE the error is.
> >>
> >>
> >>
> >> Regards
> >>
> >>
> >> Johnny Rosenberg
> >>
> >>
> >> >
> >> >
> >> > On Mon, Sep 30, 2013 at 1:22 PM, John Meyer
> >> > wrote:
> >> >
> >> > > =COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)
> >> > >
> >> > >
> >> > > On Mon, Sep 30, 2013 at 1:18 PM, John Meyer <
> >> > johnme...@pueblocomputing.com
> >> > > > wrote:
> >> > >
> >> > >> So I guess I'm doing something wrong here.
> >> > >>
> >> > >> =COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)
> >> > >>
> >> > >> Returns an Err:501.
> >> > >>
> >> > >>
> >> > >>
> >> > >> On Sun, Sep 29, 2013 at 10:59 PM, Coreurus 
> wrote:
> >> > >>
> >> > >>> <
> >> > >>> - Original Message --(Start-looking-by-there)
> >> > >>> From: John Meyer >
> >> > >>> To: 
> >> > >>> Sent: Saturday, 28 September, 2013 04:40 PM
> >> > >>> Subject: Re: VLOOKUP vs Macros
> >> > >>>
> >> > >>>
> >> > >>> > I sent that a little too soon.
> >> > >>> >
> >> > >>> > The title refers to the fact that I was looking into another
> >> > solution.
> >> > >>> >
> >> > >>> > the sales sheet for each day is broken down with the following
> >> > >>> information:
> >> > >>> >
> >> > >>> >
> >> > >>> > Date, EmpID
> >> > >>> >
> >> > >>> >
> >> > >>> >
> >> > >>> > Currently, what I am doing is separating them by date.
>  However, I
> >> > was
> >> > >>> > thinking a much less cluttered solution would involve pulling
> the
> >> > date
> >> > >>> > from the bonus calculation field and then doing either a VLOOKUP
> >> > with a
> >> > >>> > count or Macro.  Where would I start looking if I wanted to do
> >> either
> >> > >>> > one of those solutions?
> >> > >>> > Back to searching for the answer.
> >> > >>> >
> >> > >>> >
> >> > >>> >
> >> > >>> >
> >> -
> >> > >>> > To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
> >> > >>> > For additional commands, e-mail:
> users-h...@openoffice.apache.org
> >> > >>> >
> >> > >>>
> >> > >>>
> >> > >>
> >> > >
> >> >
> >>
> >
> >
>

Re: VLOOKUP vs Macros

[John Meyer]

Still doesn't work.  Back to the drawing board.


On Mon, Sep 30, 2013 at 2:32 PM, John Meyer
wrote:

> Derp, just saw that.
>
> Thanks.
>
>
> On Mon, Sep 30, 2013 at 2:01 PM, Johnny Rosenberg 
> wrote:
>
>> 2013/9/30 John Meyer 
>>
>> > Sorry, that e-mail got sent off too quickly.  I'm trying this formula
>> >
>> > =COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)
>> >
>> > C1 contains the name of the worksheet ("09-27-13") and I'm trying to use
>> > it.  However, it keeps giving me an err:501.
>> >
>>
>> Hint 1:
>> Use the builtin help and search for 501. It will take you to the error
>> codes. 501 means something like ”invalid character”. In this case it seems
>> like you use ”,” instead of ”;” as parameter separators. I thought that
>> there was a setting for that somewhere, but now I can't find it, so I
>> guess
>> that is a LibreOffice feature, but I'm not sure. I used LibreOffice for a
>> couple of years but I am back with Apache OpenOffice again, since
>> LibreOffice was way too unstable for me. It actually destroyed one of my
>> spreadsheets but Apache OpenOffice fixed it for me, that's why I'm back…
>> :D
>>
>> Anyway, replace those commas with semi-colons and I think it will work.
>>
>> Hint 2:
>> If you are working with a big formula and it doesn't work, hit Ctrl+F2
>> (select the cell that you are working with first) and you are able to
>> study
>> your formula a little better. For instance you can see sub values by
>> placing the cursor on different places in the formula. That way it's a
>> little easier to find WHERE the error is.
>>
>>
>>
>> Regards
>>
>>
>> Johnny Rosenberg
>>
>>
>> >
>> >
>> > On Mon, Sep 30, 2013 at 1:22 PM, John Meyer
>> > wrote:
>> >
>> > > =COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)
>> > >
>> > >
>> > > On Mon, Sep 30, 2013 at 1:18 PM, John Meyer <
>> > johnme...@pueblocomputing.com
>> > > > wrote:
>> > >
>> > >> So I guess I'm doing something wrong here.
>> > >>
>> > >> =COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)
>> > >>
>> > >> Returns an Err:501.
>> > >>
>> > >>
>> > >>
>> > >> On Sun, Sep 29, 2013 at 10:59 PM, Coreurus  wrote:
>> > >>
>> > >>> <
>> > >>> - Original Message --(Start-looking-by-there)
>> > >>> From: John Meyer >
>> > >>> To: 
>> > >>> Sent: Saturday, 28 September, 2013 04:40 PM
>> > >>> Subject: Re: VLOOKUP vs Macros
>> > >>>
>> > >>>
>> > >>> > I sent that a little too soon.
>> > >>> >
>> > >>> > The title refers to the fact that I was looking into another
>> > solution.
>> > >>> >
>> > >>> > the sales sheet for each day is broken down with the following
>> > >>> information:
>> > >>> >
>> > >>> >
>> > >>> > Date, EmpID
>> > >>> >
>> > >>> >
>> > >>> >
>> > >>> > Currently, what I am doing is separating them by date.  However, I
>> > was
>> > >>> > thinking a much less cluttered solution would involve pulling the
>> > date
>> > >>> > from the bonus calculation field and then doing either a VLOOKUP
>> > with a
>> > >>> > count or Macro.  Where would I start looking if I wanted to do
>> either
>> > >>> > one of those solutions?
>> > >>> > Back to searching for the answer.
>> > >>> >
>> > >>> >
>> > >>> >
>> > >>> >
>> -
>> > >>> > To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
>> > >>> > For additional commands, e-mail: users-h...@openoffice.apache.org
>> > >>> >
>> > >>>
>> > >>>
>> > >>
>> > >
>> >
>>
>
>

Re: VLOOKUP vs Macros

[John Meyer]

Derp, just saw that.

Thanks.


On Mon, Sep 30, 2013 at 2:01 PM, Johnny Rosenberg wrote:

> 2013/9/30 John Meyer 
>
> > Sorry, that e-mail got sent off too quickly.  I'm trying this formula
> >
> > =COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)
> >
> > C1 contains the name of the worksheet ("09-27-13") and I'm trying to use
> > it.  However, it keeps giving me an err:501.
> >
>
> Hint 1:
> Use the builtin help and search for 501. It will take you to the error
> codes. 501 means something like ”invalid character”. In this case it seems
> like you use ”,” instead of ”;” as parameter separators. I thought that
> there was a setting for that somewhere, but now I can't find it, so I guess
> that is a LibreOffice feature, but I'm not sure. I used LibreOffice for a
> couple of years but I am back with Apache OpenOffice again, since
> LibreOffice was way too unstable for me. It actually destroyed one of my
> spreadsheets but Apache OpenOffice fixed it for me, that's why I'm back… :D
>
> Anyway, replace those commas with semi-colons and I think it will work.
>
> Hint 2:
> If you are working with a big formula and it doesn't work, hit Ctrl+F2
> (select the cell that you are working with first) and you are able to study
> your formula a little better. For instance you can see sub values by
> placing the cursor on different places in the formula. That way it's a
> little easier to find WHERE the error is.
>
>
>
> Regards
>
>
> Johnny Rosenberg
>
>
> >
> >
> > On Mon, Sep 30, 2013 at 1:22 PM, John Meyer
> > wrote:
> >
> > > =COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)
> > >
> > >
> > > On Mon, Sep 30, 2013 at 1:18 PM, John Meyer <
> > johnme...@pueblocomputing.com
> > > > wrote:
> > >
> > >> So I guess I'm doing something wrong here.
> > >>
> > >> =COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)
> > >>
> > >> Returns an Err:501.
> > >>
> > >>
> > >>
> > >> On Sun, Sep 29, 2013 at 10:59 PM, Coreurus  wrote:
> > >>
> > >>> <
> > >>> - Original Message --(Start-looking-by-there)
> > >>> From: John Meyer >
> > >>> To: 
> > >>> Sent: Saturday, 28 September, 2013 04:40 PM
> > >>> Subject: Re: VLOOKUP vs Macros
> > >>>
> > >>>
> > >>> > I sent that a little too soon.
> > >>> >
> > >>> > The title refers to the fact that I was looking into another
> > solution.
> > >>> >
> > >>> > the sales sheet for each day is broken down with the following
> > >>> information:
> > >>> >
> > >>> >
> > >>> > Date, EmpID
> > >>> >
> > >>> >
> > >>> >
> > >>> > Currently, what I am doing is separating them by date.  However, I
> > was
> > >>> > thinking a much less cluttered solution would involve pulling the
> > date
> > >>> > from the bonus calculation field and then doing either a VLOOKUP
> > with a
> > >>> > count or Macro.  Where would I start looking if I wanted to do
> either
> > >>> > one of those solutions?
> > >>> > Back to searching for the answer.
> > >>> >
> > >>> >
> > >>> >
> > >>> >
> -
> > >>> > To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
> > >>> > For additional commands, e-mail: users-h...@openoffice.apache.org
> > >>> >
> > >>>
> > >>>
> > >>
> > >
> >
>

Re: VLOOKUP vs Macros

[Johnny Rosenberg]

2013/9/30 John Meyer 

> Sorry, that e-mail got sent off too quickly.  I'm trying this formula
>
> =COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)
>
> C1 contains the name of the worksheet ("09-27-13") and I'm trying to use
> it.  However, it keeps giving me an err:501.
>

Hint 1:
Use the builtin help and search for 501. It will take you to the error
codes. 501 means something like ”invalid character”. In this case it seems
like you use ”,” instead of ”;” as parameter separators. I thought that
there was a setting for that somewhere, but now I can't find it, so I guess
that is a LibreOffice feature, but I'm not sure. I used LibreOffice for a
couple of years but I am back with Apache OpenOffice again, since
LibreOffice was way too unstable for me. It actually destroyed one of my
spreadsheets but Apache OpenOffice fixed it for me, that's why I'm back… :D

Anyway, replace those commas with semi-colons and I think it will work.

Hint 2:
If you are working with a big formula and it doesn't work, hit Ctrl+F2
(select the cell that you are working with first) and you are able to study
your formula a little better. For instance you can see sub values by
placing the cursor on different places in the formula. That way it's a
little easier to find WHERE the error is.



Regards


Johnny Rosenberg


>
>
> On Mon, Sep 30, 2013 at 1:22 PM, John Meyer
> wrote:
>
> > =COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)
> >
> >
> > On Mon, Sep 30, 2013 at 1:18 PM, John Meyer <
> johnme...@pueblocomputing.com
> > > wrote:
> >
> >> So I guess I'm doing something wrong here.
> >>
> >> =COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)
> >>
> >> Returns an Err:501.
> >>
> >>
> >>
> >> On Sun, Sep 29, 2013 at 10:59 PM, Coreurus  wrote:
> >>
> >>> <
> >>> - Original Message --(Start-looking-by-there)
> >>> From: John Meyer >
> >>> To: 
> >>> Sent: Saturday, 28 September, 2013 04:40 PM
> >>> Subject: Re: VLOOKUP vs Macros
> >>>
> >>>
> >>> > I sent that a little too soon.
> >>> >
> >>> > The title refers to the fact that I was looking into another
> solution.
> >>> >
> >>> > the sales sheet for each day is broken down with the following
> >>> information:
> >>> >
> >>> >
> >>> > Date, EmpID
> >>> >
> >>> >
> >>> >
> >>> > Currently, what I am doing is separating them by date.  However, I
> was
> >>> > thinking a much less cluttered solution would involve pulling the
> date
> >>> > from the bonus calculation field and then doing either a VLOOKUP
> with a
> >>> > count or Macro.  Where would I start looking if I wanted to do either
> >>> > one of those solutions?
> >>> > Back to searching for the answer.
> >>> >
> >>> >
> >>> >
> >>> > -
> >>> > To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
> >>> > For additional commands, e-mail: users-h...@openoffice.apache.org
> >>> >
> >>>
> >>>
> >>
> >
>

Re: VLOOKUP vs Macros

[John Meyer]

Sorry, that e-mail got sent off too quickly.  I'm trying this formula

=COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)

C1 contains the name of the worksheet ("09-27-13") and I'm trying to use
it.  However, it keeps giving me an err:501.


On Mon, Sep 30, 2013 at 1:22 PM, John Meyer
wrote:

> =COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)
>
>
> On Mon, Sep 30, 2013 at 1:18 PM, John Meyer  > wrote:
>
>> So I guess I'm doing something wrong here.
>>
>> =COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)
>>
>> Returns an Err:501.
>>
>>
>>
>> On Sun, Sep 29, 2013 at 10:59 PM, Coreurus  wrote:
>>
>>> <
>>> ----- Original Message --(Start-looking-by-there)
>>> From: John Meyer >
>>> To: 
>>> Sent: Saturday, 28 September, 2013 04:40 PM
>>> Subject: Re: VLOOKUP vs Macros
>>>
>>>
>>> > I sent that a little too soon.
>>> >
>>> > The title refers to the fact that I was looking into another solution.
>>> >
>>> > the sales sheet for each day is broken down with the following
>>> information:
>>> >
>>> >
>>> > Date, EmpID
>>> >
>>> >
>>> >
>>> > Currently, what I am doing is separating them by date.  However, I was
>>> > thinking a much less cluttered solution would involve pulling the date
>>> > from the bonus calculation field and then doing either a VLOOKUP with a
>>> > count or Macro.  Where would I start looking if I wanted to do either
>>> > one of those solutions?
>>> > Back to searching for the answer.
>>> >
>>> >
>>> >
>>> > -
>>> > To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
>>> > For additional commands, e-mail: users-h...@openoffice.apache.org
>>> >
>>>
>>>
>>
>

Re: VLOOKUP vs Macros

[John Meyer]

=COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)


On Mon, Sep 30, 2013 at 1:18 PM, John Meyer
wrote:

> So I guess I'm doing something wrong here.
>
> =COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)
>
> Returns an Err:501.
>
>
>
> On Sun, Sep 29, 2013 at 10:59 PM, Coreurus  wrote:
>
>> <
>> - Original Message --(Start-looking-by-there)
>> From: John Meyer >
>> To: 
>> Sent: Saturday, 28 September, 2013 04:40 PM
>> Subject: Re: VLOOKUP vs Macros
>>
>>
>> > I sent that a little too soon.
>> >
>> > The title refers to the fact that I was looking into another solution.
>> >
>> > the sales sheet for each day is broken down with the following
>> information:
>> >
>> >
>> > Date, EmpID
>> >
>> >
>> >
>> > Currently, what I am doing is separating them by date.  However, I was
>> > thinking a much less cluttered solution would involve pulling the date
>> > from the bonus calculation field and then doing either a VLOOKUP with a
>> > count or Macro.  Where would I start looking if I wanted to do either
>> > one of those solutions?
>> > Back to searching for the answer.
>> >
>> >
>> >
>> > -
>> > To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
>> > For additional commands, e-mail: users-h...@openoffice.apache.org
>> >
>>
>>
>

Re: VLOOKUP vs Macros

[John Meyer]

Thanks for that, I will be sure to try that out when I get back to the
office.  I've heard the Indirect and address method mentioned before, but
didn't have a practical example of how to use it.


On Sun, Sep 29, 2013 at 12:04 PM, Andrew Douglas Pitonyak <
and...@pitonyak.org> wrote:

>
> =ADDRESS(1, 3, 1,, "sheet1")
>
> returns
>
> SHEET1.$C$1
>
> =INDIRECT(ADDRESS(1, 3, 1,, "sheet1"))
>
> returns whatever is stored in the cell referenced by SHEET1.$C$1
>
>
> On 09/28/2013 11:30 AM, John Meyer wrote:
>
>> I have a spreadsheet with a number of worksheets.  The first worksheet
>> has the bonuses for employees in this format
>>
>>
>> ID, Sales Agent, Sales For Date, Bonus for Date, Sales for Date(N), Bonus
>> For Date (N). . .
>>
>>
>> The rest of the spreadsheets are each named for the date of sales that
>> they contain.  They are in the same format as the column header for Sales
>> For Date.
>>
>>
>> I set up each row like this
>>
>> ID static, Agent name static, =COUNTIF(*staticdateworksheet***
>> !ColumnWithIDFirstRow:**ColumnWithIDNthRow,**Firstcolumnrowiamin),**
>> BonusCalculation
>>
>>
>> What I'd like to do is change that formula around to resolve the sheet
>> name by the first row of the salescount ID (i.e. if It's 9-27-2013 it would
>> pull up 9-27-2013, 9-28-2013, etc.).  Is this possible?  It's not a
>> pressing issue, I'd just like to see if it could be done.
>>
>> Thanks.
>>
>>
>>
>> --**--**-
>> To unsubscribe, e-mail: 
>> users-unsubscribe@openoffice.**apache.org
>> For additional commands, e-mail: 
>> users-help@openoffice.apache.**org
>>
>>
> --
> Andrew Pitonyak
> My Macro Document: 
> http://www.pitonyak.org/**AndrewMacro.odt
> Info:  http://www.pitonyak.org/oo.php
>
>
> --**--**-
> To unsubscribe, e-mail: 
> users-unsubscribe@openoffice.**apache.org
> For additional commands, e-mail: 
> users-help@openoffice.apache.**org
>
>

Re: VLOOKUP vs Macros

[Andrew Douglas Pitonyak]

=ADDRESS(1, 3, 1,, "sheet1")

returns

SHEET1.$C$1

=INDIRECT(ADDRESS(1, 3, 1,, "sheet1"))

returns whatever is stored in the cell referenced by SHEET1.$C$1

On 09/28/2013 11:30 AM, John Meyer wrote:
I have a spreadsheet with a number of worksheets.  The first worksheet 
has the bonuses for employees in this format



ID, Sales Agent, Sales For Date, Bonus for Date, Sales for Date(N), 
Bonus For Date (N). . .



The rest of the spreadsheets are each named for the date of sales that 
they contain.  They are in the same format as the column header for 
Sales For Date.



I set up each row like this

ID static, Agent name static, 
=COUNTIF(*staticdateworksheet*!ColumnWithIDFirstRow:ColumnWithIDNthRow,Firstcolumnrowiamin),BonusCalculation



What I'd like to do is change that formula around to resolve the sheet 
name by the first row of the salescount ID (i.e. if It's 9-27-2013 it 
would pull up 9-27-2013, 9-28-2013, etc.).  Is this possible?  It's 
not a pressing issue, I'd just like to see if it could be done.


Thanks.



-
To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
For additional commands, e-mail: users-h...@openoffice.apache.org



--
Andrew Pitonyak
My Macro Document: http://www.pitonyak.org/AndrewMacro.odt
Info:  http://www.pitonyak.org/oo.php


-
To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
For additional commands, e-mail: users-h...@openoffice.apache.org

Re: VLOOKUP vs Macros

[John Meyer]

I sent that a little too soon.

The title refers to the fact that I was looking into another solution.

the sales sheet for each day is broken down with the following information:


Date, EmpID



Currently, what I am doing is separating them by date.  However, I was 
thinking a much less cluttered solution would involve pulling the date 
from the bonus calculation field and then doing either a VLOOKUP with a 
count or Macro.  Where would I start looking if I wanted to do either 
one of those solutions?

Back to searching for the answer.



-
To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
For additional commands, e-mail: users-h...@openoffice.apache.org

VLOOKUP vs Macros

[John Meyer]

I have a spreadsheet with a number of worksheets.  The first worksheet 
has the bonuses for employees in this format



ID, Sales Agent, Sales For Date, Bonus for Date, Sales for Date(N), 
Bonus For Date (N). . .



The rest of the spreadsheets are each named for the date of sales that 
they contain.  They are in the same format as the column header for 
Sales For Date.



I set up each row like this

ID static, Agent name static, 
=COUNTIF(*staticdateworksheet*!ColumnWithIDFirstRow:ColumnWithIDNthRow,Firstcolumnrowiamin),BonusCalculation



What I'd like to do is change that formula around to resolve the sheet 
name by the first row of the salescount ID (i.e. if It's 9-27-2013 it 
would pull up 9-27-2013, 9-28-2013, etc.).  Is this possible?  It's not 
a pressing issue, I'd just like to see if it could be done.


Thanks.



-
To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
For additional commands, e-mail: users-h...@openoffice.apache.org