openoffice-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Johnny Rosenberg <gurus.knu...@gmail.com>
Subject Re: VLOOKUP vs Macros
Date Mon, 30 Sep 2013 20:01:18 GMT
2013/9/30 John Meyer <johnmeyer@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
> <johnmeyer@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 <coreurus@aol.com> wrote:
> >>
> >>> <
> >>> ----- Original Message --(Start-looking-by-there)
> >>> From: John Meyer >
> >>> To: <users@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
> >>> >
> >>>
> >>>
> >>
> >
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message