D&C GLug - Home Page

[ Date Index ] [ Thread Index ] [ <= Previous by date / thread ] [ Next by date / thread => ]

Re: [LUG] Excel Spreadsheet formula

 
On Sat, Apr 18, 2015 at 07:34:49PM +0100, Daniel Robinson wrote:

  Hi folks, perhaps these images and details can help you get an idea for
  what I am trying to achieve with the spreadsheet.

  Scrap calling sheet.

  This spreadsheet is currently used while calling accounts to find out how
  many units of scrap they have at their address. Each account is called on
  the telephone once weekly and the phone call details are logged into this
  spreadsheet.

  [1]http://www.mordros.myzen.co.uk/cs.jpg

  Bottom tabs currently list each grouping of accounts per franchise. The
  image we are currently looking at shows us one of those tabs, other tabs
  have varying numbers of accounts listed inside of them.

  Column A is currently used as a divider to show the name of the month
  which serves no real purpose other than making the sheet easier on the
  eye.


I would use Month()


  Column B begins the grouping of weekly calls. Usually a franchise is
  called on the same day but the date of the call is entered manually.
  Between weekly calls a clear cell is left blank for visual effect.


I hate empty columns / rows: without them it is easy to sort
Column G Company shows which collections company to use, DME being a third 
  party or MTS meaning in house.

  Casing Collection sheet

  If a collection requires the third party to attend a new log is created,
  currently this has been duplicated work as the data was manually
  re-entered into this spreadsheet.

  [2]http://www.mordros.myzen.co.uk/cc.jpg

  Initially what I would like to be able to do is make a rule for the Scrap
  Calling Sheet which will look for the word DME to be written in to a cell
  in column G, copy that row and then list the row into the Casing
  Collection Sheet.

  If column G has MTS I would like to have that listed also in another
  similar spreadsheet.


Pass, I would use the filter command and manually copy the results
If you added the another column for the account name then it would be very simple to filter all accounts and copy the relevant records 

YMMV


  On 18 April 2015 at 16:25, jay bennie <[3]jay@xxxxxxxxxxx> wrote:

    Sent from my iPhone
    > On 18 Apr 2015, at 16:07, Mark Croft <[4]mark.croft.lug@xxxxxxxxx>
    wrote:
    >
    > i do believe it takes a lot of effort to learn database cos there is a
    > lot of theory that backs it all up.

    like everything, it starts with a simple principle.

    a list -> relationship <- another list

    use, learn more, refactor ... people get over academic with databases.

    KISS

    >
    > --
    > The Mailing List for the Devon & Cornwall LUG
    > [5]http://mailman.dclug.org.uk/listinfo/list
    > FAQ: [6]http://www.dcglug.org.uk/listfaq

    --
    The Mailing List for the Devon & Cornwall LUG
    [7]http://mailman.dclug.org.uk/listinfo/list
    FAQ: [8]http://www.dcglug.org.uk/listfaq

References

  Visible links
  1. http://www.mordros.myzen.co.uk/cs.jpg
  2. http://www.mordros.myzen.co.uk/cc.jpg
  3. mailto:jay@xxxxxxxxxxx
  4. mailto:mark.croft.lug@xxxxxxxxx
  5. http://mailman.dclug.org.uk/listinfo/list
  6. http://www.dcglug.org.uk/listfaq
  7. http://mailman.dclug.org.uk/listinfo/list
  8. http://www.dcglug.org.uk/listfaq



--
The Mailing List for the Devon & Cornwall LUG
http://mailman.dclug.org.uk/listinfo/list
FAQ: http://www.dcglug.org.uk/listfaq



--
The Mailing List for the Devon & Cornwall LUG
http://mailman.dclug.org.uk/listinfo/list
FAQ: http://www.dcglug.org.uk/listfaq