simple excel question

Discussion in 'NZ Computing' started by Richard, Jan 18, 2009.

  1. Richard

    Richard Guest

    Just not sure what its called, have seen it done before where there was
    one sheet with a list of dates, amounts etc, and then there was another
    sheet that had the ability to step thru the list with fields that showed
    the total to date etc for each week from each line of the first sheet

    Its to do some pay slips that I have just kept the details in a single
    sheet and dont want to manually do each slip myself.
     
    Richard, Jan 18, 2009
    #1
    1. Advertising

  2. Richard

    jim shooz Guest

    Richard wrote:
    > Just not sure what its called, have seen it done before where there was
    > one sheet with a list of dates, amounts etc, and then there was another
    > sheet that had the ability to step thru the list with fields that showed
    > the total to date etc for each week from each line of the first sheet
    >
    > Its to do some pay slips that I have just kept the details in a single
    > sheet and dont want to manually do each slip myself.


    Try

    HLOOKUP(lookup_value,table_array,row_index_num)

    or
    VLOOKUP
     
    jim shooz, Jan 18, 2009
    #2
    1. Advertising

  3. Richard

    Richard Guest

    jim shooz wrote:
    > Richard wrote:
    >> Just not sure what its called, have seen it done before where there
    >> was one sheet with a list of dates, amounts etc, and then there was
    >> another sheet that had the ability to step thru the list with fields
    >> that showed the total to date etc for each week from each line of the
    >> first sheet
    >>
    >> Its to do some pay slips that I have just kept the details in a single
    >> sheet and dont want to manually do each slip myself.

    >
    > Try
    >
    > HLOOKUP(lookup_value,table_array,row_index_num)
    >
    > or
    > VLOOKUP


    That doesnt seem to do the trick sadly.

    I have a table that is basically

    item date amount
    1 01/05 $320
    2 15/05 $320
    3 29/05 $290


    etc

    So I was wanting to have


    Reciept (first col)
    recieved on (second column) the sum of (third col)


    in a pretty formatted sheet and then somehow programatically print one
    page for every row on the first sheet. I am of even incrimenting a field
    and hitting print myself each time but I dont know how to reference a
    cell to do that - having =transactions!B6 works sweet as, but my
    attempts at putting =transactions!B(6+1) fail with some cryptic error.

    Either I am seriously missing how to use HLOOKUP or its for something else.
     
    Richard, Jan 22, 2009
    #3
  4. Richard

    Richard Guest

    Richard wrote:
    > jim shooz wrote:
    >> Richard wrote:
    >>> Just not sure what its called, have seen it done before where there
    >>> was one sheet with a list of dates, amounts etc, and then there was
    >>> another sheet that had the ability to step thru the list with fields
    >>> that showed the total to date etc for each week from each line of the
    >>> first sheet
    >>>
    >>> Its to do some pay slips that I have just kept the details in a
    >>> single sheet and dont want to manually do each slip myself.

    >>
    >> Try
    >>
    >> HLOOKUP(lookup_value,table_array,row_index_num)
    >>
    >> or
    >> VLOOKUP

    >
    > That doesnt seem to do the trick sadly.
    >
    > I have a table that is basically
    >
    > item date amount
    > 1 01/05 $320
    > 2 15/05 $320
    > 3 29/05 $290
    >
    >
    > etc
    >
    > So I was wanting to have
    >
    >
    > Reciept (first col)
    > recieved on (second column) the sum of (third col)
    >
    >
    > in a pretty formatted sheet and then somehow programatically print one
    > page for every row on the first sheet. I am of even incrimenting a field
    > and hitting print myself each time but I dont know how to reference a
    > cell to do that - having =transactions!B6 works sweet as, but my
    > attempts at putting =transactions!B(6+1) fail with some cryptic error.
    >
    > Either I am seriously missing how to use HLOOKUP or its for something else.



    Nevermind,. it appears vlookup will do what I want, just not very
    intuitivly.

    Still need to sort a way to get it to do all of them, but for now
    incimenting a cell and hitting print will do.
     
    Richard, Jan 22, 2009
    #4
  5. Richard

    jim shooz Guest

    Richard wrote:
    > Richard wrote:
    >> jim shooz wrote:
    >>> Richard wrote:
    >>>> Just not sure what its called, have seen it done before where there
    >>>> was one sheet with a list of dates, amounts etc, and then there was
    >>>> another sheet that had the ability to step thru the list with fields
    >>>> that showed the total to date etc for each week from each line of
    >>>> the first sheet
    >>>>
    >>>> Its to do some pay slips that I have just kept the details in a
    >>>> single sheet and dont want to manually do each slip myself.
    >>>
    >>> Try
    >>>
    >>> HLOOKUP(lookup_value,table_array,row_index_num)
    >>>
    >>> or
    >>> VLOOKUP

    >>
    >> That doesnt seem to do the trick sadly.
    >>
    >> I have a table that is basically
    >>
    >> item date amount
    >> 1 01/05 $320
    >> 2 15/05 $320
    >> 3 29/05 $290
    >>
    >>
    >> etc
    >>
    >> So I was wanting to have
    >>
    >>
    >> Reciept (first col)
    >> recieved on (second column) the sum of (third col)
    >>
    >>
    >> in a pretty formatted sheet and then somehow programatically print one
    >> page for every row on the first sheet. I am of even incrimenting a
    >> field and hitting print myself each time but I dont know how to
    >> reference a cell to do that - having =transactions!B6 works sweet as,
    >> but my attempts at putting =transactions!B(6+1) fail with some cryptic
    >> error.
    >>
    >> Either I am seriously missing how to use HLOOKUP or its for something
    >> else.

    >
    >
    > Nevermind,. it appears vlookup will do what I want, just not very
    > intuitivly.
    >
    > Still need to sort a way to get it to do all of them, but for now
    > incimenting a cell and hitting print will do.


    Bit rusty on this now but do you have a block of data which you want to
    take entries from, insert that in a form, print the form, and move to
    the next entry?

    You could set up a counter and using a small macro use an IF statement
    to cycle through that data creating printouts for each entry.
     
    jim shooz, Jan 22, 2009
    #5
  6. In message <gku4u7$2qu$>, Richard wrote:

    > Its to do some pay slips that I have just kept the details in a single
    > sheet and dont want to manually do each slip myself.


    Sounds like you've fallen into the classic mistake of trying to use a
    spreadsheet as a database.
     
    Lawrence D'Oliveiro, Jan 23, 2009
    #6
  7. Richard

    Richard Guest

    Lawrence D'Oliveiro wrote:
    > In message <gku4u7$2qu$>, Richard wrote:
    >
    >> Its to do some pay slips that I have just kept the details in a single
    >> sheet and dont want to manually do each slip myself.

    >
    > Sounds like you've fallen into the classic mistake of trying to use a
    > spreadsheet as a database.


    Whats the difference really - everything you type into a spreadsheet is
    data, so yeah, its a database - not a fancy one like oricle or mssql or
    mysql - but still a database ? Its an easy to use tool that allows me to
    sum figures for tax purposes, to print them formatted how I want, and to
    keep a copy of the data how I want it.

    I have never had to do this sort of thing with it before, so its a
    learning curve, But it is clearly capable of doing what I want since I
    have seen it do it at a previous employer.
     
    Richard, Jan 24, 2009
    #7
  8. Richard

    Enkidu Guest

    Richard wrote:
    > Lawrence D'Oliveiro wrote:
    >> In message <gku4u7$2qu$>, Richard wrote:
    >>
    >>> Its to do some pay slips that I have just kept the details in a single
    >>> sheet and dont want to manually do each slip myself.

    >>
    >> Sounds like you've fallen into the classic mistake of trying to use a
    >> spreadsheet as a database.

    >
    > Whats the difference really - everything you type into a spreadsheet is
    > data, so yeah, its a database - not a fancy one like oricle or mssql or
    > mysql - but still a database ? Its an easy to use tool that allows me to
    > sum figures for tax purposes, to print them formatted how I want, and to
    > keep a copy of the data how I want it.
    >
    > I have never had to do this sort of thing with it before, so its a
    > learning curve, But it is clearly capable of doing what I want since I
    > have seen it do it at a previous employer.
    >

    So have I and I've also seen people run a mile when it either goes wrong
    or needs modifications. Trouble is they start out simple, but then they
    grow and morph and the maintainer leaves so that no one knows how it works.

    With a proper program and database the DBA looks after the database and
    the programmer looks after the programs.

    Of course, if you are a small business and you are the payroll person,
    the cashier, the CEO and the CIO you probably can't really justify a
    database, but when it gets complicated then you should consider NOT
    using a spreadsheet.

    Cheers,

    Cliff

    --

    Linux is theft.
     
    Enkidu, Jan 24, 2009
    #8
  9. In message <gldu2n$gje$>, Richard wrote:

    > Its an easy to use tool that allows me to sum figures for tax purposes, to
    > print them formatted how I want, and to keep a copy of the data how I want
    > it.


    And to get the calculations wrong without realizing you've got them wrong.

    <http://www.google.co.nz/search?q=spreadsheet+debugging>
     
    Lawrence D'Oliveiro, Jan 24, 2009
    #9
  10. Richard

    EMB Guest

    Enkidu wrote:
    > Richard wrote:
    >> Lawrence D'Oliveiro wrote:
    >>> In message <gku4u7$2qu$>, Richard wrote:
    >>>
    >>>> Its to do some pay slips that I have just kept the details in a single
    >>>> sheet and dont want to manually do each slip myself.
    >>>
    >>> Sounds like you've fallen into the classic mistake of trying to use a
    >>> spreadsheet as a database.

    >>
    >> Whats the difference really - everything you type into a spreadsheet
    >> is data, so yeah, its a database - not a fancy one like oricle or
    >> mssql or mysql - but still a database ? Its an easy to use tool that
    >> allows me to sum figures for tax purposes, to print them formatted how
    >> I want, and to keep a copy of the data how I want it.
    >>
    >> I have never had to do this sort of thing with it before, so its a
    >> learning curve, But it is clearly capable of doing what I want since I
    >> have seen it do it at a previous employer.
    > >

    > So have I and I've also seen people run a mile when it either goes wrong
    > or needs modifications. Trouble is they start out simple, but then they
    > grow and morph and the maintainer leaves so that no one knows how it works.
    >
    > With a proper program and database the DBA looks after the database and
    > the programmer looks after the programs.
    >
    > Of course, if you are a small business and you are the payroll person,
    > the cashier, the CEO and the CIO you probably can't really justify a
    > database, but when it gets complicated then you should consider NOT
    > using a spreadsheet.


    And in terms of something like a payroll, Ace Payroll is cheap, reliable
    and does exactly what it says on the box. The UI is somewhat sucky but
    payroll clerks deserve that sort of thing anyway ;-)
     
    EMB, Jan 25, 2009
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. bb

    Excel question

    bb, Sep 5, 2003, in forum: Computer Support
    Replies:
    1
    Views:
    452
  2. sunny

    Simple Excel formula required

    sunny, Jan 6, 2004, in forum: Computer Support
    Replies:
    4
    Views:
    690
  3. Replies:
    7
    Views:
    4,265
    Kimba W. Lion
    Jan 26, 2007
  4. Kim
    Replies:
    10
    Views:
    495
  5. MeekiMoo
    Replies:
    0
    Views:
    664
    MeekiMoo
    Jul 28, 2009
Loading...

Share This Page