Some VBA help please

[Sir Babydum GBE]

Title is no help at all, is it! Sorry about that.

For those of you that remember me I'll fill you in on what's been happening: I stopped working in any IT related business a few years ago. I also stopped trying to save the world too as none of the world leaders would return my phone calls, so I thought "Stuff 'em - they'll have to manage without me, the ungrateful tykes!". Now I am a humble window cleaner earning a modest income and thoroughly enjoying the outdoor life.

As such, I decided to marshal what things I could remember about Excel and use it to keep a record of my customers and organise my work. So, to my question...

One of my sheets is called "Work Printout" and it tells me Who I'm due to visit that day (and a few things about the job). (This info is held in rows 2 - 30) Rows 33 onwards shows me who still hasn't paid me from previous days work.

What I would like to do at the end of each day is transfer some information by double-clicking things.

For example: Task 1: if I double click any cell in the range E2:E30 (lets say E5) the macro will understand that the job was done and has been paid for. So it will copy the range A5 to H5, Paste that to the first unoccupied row on a sheet called "Done" but in cells B to I, not A to H. And in column A of that new row it will insert today's date. Then it will look for that customer who's address was in Column B of "Work Printout", Locate that customer on a sheet called "Customer list" (where the address is in column E) and add today's date into column F)
Once all that is done it will go back to my "Work Printout" sheet and clear the info from that line.

On the other hand (Task 2) If on the sheet "Work Printout" I double click any cell in the range F2:F30 (say, F5), then it will look at the cell contents. If it is blank it means I've done the job but it remains unpaid. So then the macro will do everything the first macro did, but will also copy A5 to H5, pasting it on the nearest free row from row 33 down (same columns this time because it's on the same sheet) and inserting today's date in column D.

Task 3. If I double-clicked F5 and the cell contained "ND" Then it means that, for whatever reason, I wan not able to do that house today, I will have typed that reason in column H. So in this case the macro only need copy the reason I typed into column H, locate the customer on the "Customer list" sheet and copy that reason to column Z of that sheet, appending it to whatever comments are already there.


So if you can do/advise/suggest any or all of the above, I will be forever indebted to you for about a week.

Good to be back

Sir BD

[Bob Phillips]

Good to see you back, British Gas must be missing you.

A workbook would be helpful, obfuscated of course to protect the innocent.

[mdmackillop]

Hi Sir BD
Welcome back!
If you can remember how to attach a sample workbook (Go Advanced / Manage Attachments) I'm sue we can produce a suitable project.
Regards
MD

[Sir Babydum GBE]

Sorry for the late reply and thanks for your help. File attached (names and addresses changed). Also, in my original request, the sheet that I called "Done" is actually called "Worked" (oops)

Mr Excel Example Workbook.xlsm

[Bob Phillips]

Well I can see you are just as unreliable as ever. 5 days and you didn't return our call. You are lucky we didn't just think "Stuff him - he'll have to manage without me, the ungrateful tyke!"

As for this, it must be the weirdest code I have ever seen.

    Sheets("Change CURRENT date here").Select
    Range("F6").FormulaR1C1 = "=TODAY()"
    Range("F6").Copy
    Range("G6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("F6").ClearContents

Notwithstanding the awful selects, you load a formula into F6, copy that as values to G6, then clear it out of F6. What the hell did you bother with F6 for at all?

One line does it

    Worksheets("Change CURRENT date here").Range("G6").Value = Date

I guess I better look to see if I can help now I have done with abusing you.

[Bob Phillips]

Seems overly complex to me.

Why not just have a single list with customer name, address (could be a lookup), Job Date, Job Cost, Job Code (Done or whatever), Date Paid, Notes and then chuck a pivot at that table, you can add some slicers for month, postal area etc. Seems so much simpler not to replicate, and not to copy stuff around.

If you put it in a table, that has automatic filters so you can filter that to help with data input.

[Sir Babydum GBE]

I guess I better look to see if I can help now I have done with abusing you.

I can't be brilliant at absolutely everything Mr X. So let me take a look at your suggestions now...

[Sir Babydum GBE]

Thanks for helping XLD

Seems overly complex to me.

That is true and it's partly because it was built in stages. It started as a simple list of customers that I could refer to if I needed to contact them. But eventually I just kept adding info here and there to make it more of a planning tool. So yes, even on the list there are columns that are redundant and I will eventually revamp the whole thing.

Why not just have a single list...?

Because I need to maintain a record of who I've done, when, how much and so on. So, for tax purposes (as an example) it is good to be able to see a breakdown of every job done from, say, April to March. Its much easier to go through your activity when its set out as it currently is.
Also, its difficult to explain why, but having the day's job list with the customers who owe beneath just works. and, once someone pays, its easy just to delete the row.

[Bob Phillips]

That is true and it's partly because it was built in stages. It started as a simple list of customers that I could refer to if I needed to contact them. But eventually I just kept adding info here and there to make it more of a planning tool. So yes, even on the list there are columns that are redundant and I will eventually revamp the whole thing.

It is not redundanrt columns I am referring to, it is redundant design (or lack of it). You have too many sheets. Moving the data around to show the different status is redundant, just add a column to flag that status.

Let me put it this way. You should always look at a spreadsheet just as you would an application, put some design effort into it. It has a data layer, a business logic layer, and a presentation layer. Different parts of the application deal with each of those layers. One of the problems with Excel is that it tends to make it difficult to differentiate those layers, but you should make the effort.

The data layer can be one table, or many (in your case, at least two, your customer list and the job list, I also suggest a date table), but make them Excel tables.

The business logic layer is probably just you interacting with Excel. Just add new customers as you get them, flag them as you lose them. Add new jobs as you get them, flag them when done, flag them when paid.

The presentation layer is where I suggest pivot tables. If you have a date table as well (with date, month, year, etc. columns), you can link that to the job date, and then put slicers on the attributes of date, so ou can ask for Apr jobs, May jobs, Apr and May jobs.

Because I need to maintain a record of who I've done, when, how much and so on. So, for tax purposes (as an example) it is good to be able to see a breakdown of every job done from, say, April to March. Its much easier to go through your activity when its set out as it currently is.
Also, its difficult to explain why, but having the day's job list with the customers who owe beneath just works. and, once someone pays, its easy just to delete the row.

So do I BD, I also run a business. But I keep a single list and I run pivots to analyse it, as I suggest above. Nothing in what I am suggesting works against that. As for deleting, you should never delete data, just flag it as complete or whatever.

BTW, what version of Excel do you have?

[SamT]

The business logic layer is probably just you interacting with Excel.

USERFORMS

[Bob Phillips]

USERFORMS

No! Userforms are hard work (to develop well), and are nowhere as easy to use as the Excel grid.

[Sir Babydum GBE]

BTW, what version of Excel do you have?

Office 365

[Sir Babydum GBE]

The data layer can be one table, or many (in your case, at least two, your customer list and the job list, I also suggest a date table), but make them Excel tables.

The business logic layer is probably just you interacting with Excel. Just add new customers as you get them, flag them as you lose them. Add new jobs as you get them, flag them when done, flag them when paid.

You've gone to some length to break it down for me, which I really appreciate, but humour me here, as I want to make sure I've understood you.

and, just to give you some more detail about how my business works... the vast majority of my customers are repeat customers (it's a window cleaning round, after all; very occasionally I'll do one-off cleans) Anyhow, most of them have exactly the same thing each time but each visit carries the possibility of a change (customer not in so I can't get round the back etc, or customer wants an extra such as insides done). And then of course a customer may pay there and then, or just partially or even in advance. Or some may refuse the job when I arrive ("miss me this time"). Back to your answer...

"Layers" and "tables" are not mutually exclusive, right? So to apply our approach to my specific workbook needs. It would look something like this.

A sheet with customer details including contact details and price list (price for fronts, price for all round, price for extras etc)
on that sheet I can flag cancelled customers so that the don't show in my validation drop downs and pivot tables etc.

A job sheet. Which will contain my validation dropdowns to select a customer. On selection of that customer, I get the rest of the details through lookups, which is effectively copying the customer data (minus some unnecessary info). On that sheet I will have placed 'marker' columns ("date" "job done" ""amount paid") and a notes column.

thats pretty much what I have at the moment in my two main sheets, but what you're suggesting is create a pivot table to interrogate the job sheet so that i can display only the jobs that still need doing (for my daily printout) based on the markers I've used. And another table to display customers that still owe money?

if that's right, then when you talk about a "business logic layer", you don't mean a separate sheet or sheets? You are simply referring to the fact that on each sheet I have there are parts that I interact with? (Adding new customers, filling in marker columns)

[Bob Phillips]

... and, just to give you some more detail about how my business works... the vast majority of my customers are repeat customers (it's a window cleaning round, after all; very occasionally I'll do one-off cleans) Anyhow, most of them have exactly the same thing each time but each visit carries the possibility of a change (customer not in so I can't get round the back etc, or customer wants an extra such as insides done). And then of course a customer may pay there and then, or just partially or even in advance. Or some may refuse the job when I arrive ("miss me this time"). Back to your answer...

That is exactly what I assumed, and although that is a different business model to mine, I don't think it impacts the overall approach.

... "Layers" and "tables" are not mutually exclusive, right? So to apply our approach to my specific workbook needs. It would look something like this.

Well they are not really inclusive, you might think of the tables as vertical components, layers as horizontal components; the layers work across the table; the tables feed into the layers. Does that make sense?

... A sheet with customer details including contact details and price list (price for fronts, price for all round, price for extras etc)
on that sheet I can flag cancelled customers so that the don't show in my validation drop downs and pivot tables etc.

Yes, that is what I am would do. Not so sure about prices as they vary, it might be the place to put them if you have some automatic way to pick them up when you add a new job, but it might just be as simple to simply put the price on the jobs list. Remember, in BI terminology, Customer is a dimension, it is holds data about your business, slowly changing data, whereas Jobs is a fact, it includes the metics, date of job, price of job, maybe time spent on job, etc.

... A job sheet. Which will contain my validation dropdowns to select a customer. On selection of that customer, I get the rest of the details through lookups, which is effectively copying the customer data (minus some unnecessary info). On that sheet I will have placed 'marker' columns ("date" "job done" ""amount paid") and a notes column.

Yes, although I would only hold the customer name, you don't need the address, that is already on the customer table. You will need some way to build your lookup lists from the customer table t exclude cancelled customers etc., but that is relatively simple formulae, or maybe some VBA.

... thats pretty much what I have at the moment in my two main sheets, but what you're suggesting is create a pivot table to interrogate the job sheet so that i can display only the jobs that still need doing (for my daily printout) based on the markers I've used. And another table to display customers that still owe money?

Yes, because that is where you get you info, say the jobs for the coming week, jobs unpaid, jobs in April and March. You may already have that pretty much, but my approach does away with any data manipulation other than entering it.

... if that's right, then when you talk about a "business logic layer", you don't mean a separate sheet or sheets? You are simply referring to the fact that on each sheet I have there are parts that I interact with? (Adding new customers, filling in marker columns)

Exactly right. SamT suggested userforms (maybe a bit tongue-in-cheek), but that is total overkill in my view. If you were creating this for some Excel tyro, maybe, but it is for your own use and you can manage Excel tables.

I started knocking up a demo for you. It won't have all of the attributes that you need, at least at the start, but hopefully it will show what I mean better than mere words (mere words, I never believe that words are mere!). I need to pop out for a lunch date, but I will add a bit more later today and post it here for you.

AS for your Office 365, is it a version that includes Power Pivot, and Get and Transform (Power query)?

[Sir Babydum GBE]

Thanks a lot XLD

Not so sure about prices as they vary, it might be the place to put them if you have some automatic way to pick them up when you add a new job, but it might just be as simple to simply put the price on the jobs list.

The problem is that the basic prices are set for each customer. It's not like I sell a service that has a fixed price that I can superimpose on any customer (well, there is a basic pricing model, but differences in houses and negotiations at the point of customer acquisition mean that the model is really just a target price). And, because I have a poor memory, when I print a job sheet I need to be reminded of the price. When there are variations ("do the insides", paint removal etc) then I can set a price off-the-cuff if I'm with the customer (which I'd need to record so that I have that price to hand if they request the same service in the future), but I do plan to have two principal set prices for each customer: 'Front only', or 'Front & Back'.

AS for your Office 365, is it a version that includes Power Pivot, and Get and Transform (Power query)?

Never heard of them but I just checked... Power Pivot - no, Get & Transform - yes

[SamT]

On a Contractor Forum, I used to teach Estimating with Excel. One of the first things I emphasized was Tabulating all metrics. In your particular case, this might mean lists all standard window sizes. I don't know if this suits your locales, but in "the Colonies, heh, heh," window sizes are called out like "2-0 x 4-6," which translates to a window 2'0"W x 4'6"H.

This would result in a table with the Call Outs in Column 1, Widths in Column B, and Heights in Column C. A small Table. Most are. Most such reference tables would fit on one sheet.

Add another Table with Customers in Column A and a horizontal list of Window Call Outs in Row 1. A Window Count for each Size would be at the intersection of the Customer Row and Call Out Column and this table would be a Lookup table for each Customer and for averaging prices so you can set standard prices per window.

For Pricing, a Table with Window Call Outs in one Column and the Price of each in the next Column, enables lookups for pricing.

Of course, you need a table for each type of service. Cleaning, Paint removal, Caulking, etc. If you don't use a standard markup for insides vs outsides, you would also need a table(s) for inside work.

All this does mean a little more prep time, but it quickly pays for itself when the Workbook goes into production.

The Estimating sheet I developed for a Roofing Contractor only had a dozen or so tables, which is what I would expect for a Window Cleaning Contractor.

[mdmackillop]

Have you thought about including a pricing "factor" which would differ for customers/locations, allowing you to use a standard schedule of window size prices etc.

[Sir Babydum GBE]

...In your particular case, this might mean lists all standard window sizes. I don't know if this suits your locales, but in "the Colonies, heh, heh," window sizes are called out like "2-0 x 4-6," which translates to a window 2'0"W x 4'6"H

I appreciate your input
Certainly worth thinking about, and I do have a basic pricing model but there are more factors that I take into account This includes things like whether windows are leaded, if they are hard to reach, whether ladder placement will be difficult or easy, whether access to the back is easy or not, what type of frames they have etc etc), and when you canvass for work, you really have to give a price there and then, so I think it might be too time consuming entering window measurements etc. I have a sort of sixth sense for assessing these things and pricing quickly. But depending on what 'mood' I'm in (and area) there may be slight variances. But there's no getting away from the fact that pricing ultimately is individual to the customer, so when I create a new customer record, that seems to me to be the most logical place to put the agreed price.

[Sir Babydum GBE]

Have you thought about including a pricing "factor" which would differ for customers/locations, allowing you to use a standard schedule of window size prices etc.

This would be a good solution to the problems I mentioned to SamT below. I guess I could have a "difficulty" sliding scale. On the other hand, as I mentioned, coming up with and negotiating a price with the customer isn't a problem for me. Typically I'll decide on a price for the fronts as I'm walking up the path. If the customer wants the service I say the top-end price for that house and I have a minimum price I'll accept in case the customer barters. I can't guess the price of doing backs as well because you need to see it before you can price it. But I digress... At this stage in my business when I select the customers that I will visit for that day I need to see the prices I agreed with them, it needs to be that way around.

[Sir Babydum GBE]

I started knocking up a demo for you. It won't have all of the attributes that you need, at least at the start, but hopefully it will show what I mean better than mere words (mere words, I never believe that words are mere!). I need to pop out for a lunch date, but I will add a bit more later today and post it here for you.

Thanks very much indeed.