You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openoffice.apache.org by John Meyer <jo...@pueblocomputing.com> on 2013/09/28 17:30:21 UTC
VLOOKUP vs Macros
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
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
Re: VLOOKUP vs Macros
Posted by Johnny Rosenberg <gu...@gmail.com>.
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>.
Still doesn't work. Back to the drawing board.
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 <gu...@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>.
Derp, just saw that.
Thanks.
On Mon, Sep 30, 2013 at 2:01 PM, Johnny Rosenberg <gu...@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/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>.
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
<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>.
=COUNTIF(INDIRECT(ADDRESS(1,3,1,,"Bonuses")).$E$1:$E2000,A2)
On Mon, Sep 30, 2013 at 1:18 PM, John Meyer
<jo...@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>.
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>.
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 <
andrew@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<us...@openoffice.apache.org>
>> For additional commands, e-mail: users-help@openoffice.apache.**org<us...@openoffice.apache.org>
>>
>>
> --
> Andrew Pitonyak
> My Macro Document: http://www.pitonyak.org/**AndrewMacro.odt<http://www.pitonyak.org/AndrewMacro.odt>
> Info: http://www.pitonyak.org/oo.php
>
>
> ------------------------------**------------------------------**---------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.**apache.org<us...@openoffice.apache.org>
> For additional commands, e-mail: users-help@openoffice.apache.**org<us...@openoffice.apache.org>
>
>
Re: VLOOKUP vs Macros
Posted by Andrew Douglas Pitonyak <an...@pitonyak.org>.
=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