You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openoffice.apache.org by Johnny Rosenberg <gu...@gmail.com> on 2013/10/01 16:11:14 UTC
Re: VLOOKUP vs Macros
2013/9/30 John Meyer <jo...@pueblocomputing.com>
> 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
> <jo...@pueblocomputing.com>wrote:
>
> > Derp, just saw that.
> >
> > Thanks.
> >
> >
> > On Mon, Sep 30, 2013 at 2:01 PM, Johnny Rosenberg <
> gurus.knugum@gmail.com>wrote:
> >
> >> 2013/9/30 John Meyer <jo...@pueblocomputing.com>
> >>
> >> > 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
> >> > <jo...@pueblocomputing.com>wrote:
> >> >
> >> > > =COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)
> >> > >
> >> > >
> >> > > On Mon, Sep 30, 2013 at 1:18 PM, John Meyer <
> >> > johnmeyer@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 <co...@aol.com>
> wrote:
> >> > >>
> >> > >>> <
> >> > >>> ----- Original Message --(Start-looking-by-there)
> >> > >>> From: John Meyer >
> >> > >>> To: <us...@openoffice.apache.org>
> >> > >>> 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-unsubscribe@openoffice.apache.org
> >> > >>> > For additional commands, e-mail:
> users-help@openoffice.apache.org
> >> > >>> >
> >> > >>>
> >> > >>>
> >> > >>
> >> > >
> >> >
> >>
> >
> >
>
Re: VLOOKUP vs Macros
Posted by Johnny Rosenberg <gu...@gmail.com>.
2013/10/1 John Meyer <jo...@pueblocomputing.com>
> 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 <jo...@pueblocomputing.com>
>>>
>>> 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
>>>> <jo...@pueblocomputing.com>wrote:
>>>>
>>>> Derp, just saw that.
>>>>>
>>>>> Thanks.
>>>>>
>>>>>
>>>>> On Mon, Sep 30, 2013 at 2:01 PM, Johnny Rosenberg <
>>>>>
>>>> gurus.knugum@gmail.com>wrote:
>>>>
>>>>> 2013/9/30 John Meyer <jo...@pueblocomputing.com>
>>>>>>
>>>>>> 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
>>>>>>> <jo...@pueblocomputing.com>wrote:
>>>>>>>
>>>>>>> =COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)
>>>>>>>>
>>>>>>>>
>>>>>>>> On Mon, Sep 30, 2013 at 1:18 PM, John Meyer <
>>>>>>>>
>>>>>>> johnmeyer@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 <co...@aol.com>
>>>>>>>>>
>>>>>>>> wrote:
>>>>
>>>>> <
>>>>>>>>>> ----- Original Message --(Start-looking-by-there)
>>>>>>>>>> From: John Meyer >
>>>>>>>>>> To: <us...@openoffice.apache.org>
>>>>>>>>>> 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-unsubscribe@openoffice.apache.org
>>>>>>>>>>> For additional commands, e-mail:
>>>>>>>>>>>
>>>>>>>>>> users-help@openoffice.apache.org
>>>>
>>>>>
>>>>>>>>>>
>>>>>
>>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
>
Re: VLOOKUP vs Macros
Posted by John Meyer <jo...@pueblocomputing.com>.
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 <jo...@pueblocomputing.com>
>>
>>> 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
>>> <jo...@pueblocomputing.com>wrote:
>>>
>>>> Derp, just saw that.
>>>>
>>>> Thanks.
>>>>
>>>>
>>>> On Mon, Sep 30, 2013 at 2:01 PM, Johnny Rosenberg <
>>> gurus.knugum@gmail.com>wrote:
>>>>> 2013/9/30 John Meyer <jo...@pueblocomputing.com>
>>>>>
>>>>>> 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
>>>>>> <jo...@pueblocomputing.com>wrote:
>>>>>>
>>>>>>> =COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)
>>>>>>>
>>>>>>>
>>>>>>> On Mon, Sep 30, 2013 at 1:18 PM, John Meyer <
>>>>>> johnmeyer@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 <co...@aol.com>
>>> wrote:
>>>>>>>>> <
>>>>>>>>> ----- Original Message --(Start-looking-by-there)
>>>>>>>>> From: John Meyer >
>>>>>>>>> To: <us...@openoffice.apache.org>
>>>>>>>>> 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-unsubscribe@openoffice.apache.org
>>>>>>>>>> For additional commands, e-mail:
>>> users-help@openoffice.apache.org
>>>>>>>>>
>>>>
>
Re: VLOOKUP vs Macros
Posted by John Meyer <jo...@pueblocomputing.com>.
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 <jo...@pueblocomputing.com>
>
>> 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
>> <jo...@pueblocomputing.com>wrote:
>>
>>> Derp, just saw that.
>>>
>>> Thanks.
>>>
>>>
>>> On Mon, Sep 30, 2013 at 2:01 PM, Johnny Rosenberg <
>> gurus.knugum@gmail.com>wrote:
>>>> 2013/9/30 John Meyer <jo...@pueblocomputing.com>
>>>>
>>>>> 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
>>>>> <jo...@pueblocomputing.com>wrote:
>>>>>
>>>>>> =COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)
>>>>>>
>>>>>>
>>>>>> On Mon, Sep 30, 2013 at 1:18 PM, John Meyer <
>>>>> johnmeyer@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 <co...@aol.com>
>> wrote:
>>>>>>>> <
>>>>>>>> ----- Original Message --(Start-looking-by-there)
>>>>>>>> From: John Meyer >
>>>>>>>> To: <us...@openoffice.apache.org>
>>>>>>>> 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-unsubscribe@openoffice.apache.org
>>>>>>>>> For additional commands, e-mail:
>> users-help@openoffice.apache.org
>>>>>>>>
>>>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org