can you set employers id numbers to use on spreadsheets to help invoicing

[steveUK]

hey all

i'm recently new to excel as I set up my own business and I've starting using excel to keep my books, I know a lot of the basics and enough to create my own spreadsheets but obliviously there's still loads to learn, I was wondering if I can create my employees an id tag so when inputting in a cell who worked on what job I can calculate the different rates of pay to quickly give me a total invoice price for that one job.

[BadlySpelledBuoy]

Hi and welcome.

You can certainly add a unique employee number and use that for a whole heap of things with Excel.

Perhaps if you could show us the sort of thing you have in mind it would help.
Nothing fancy, juts a manually generated example of the sort of data you capture and an idea of what you'd like to do with it.

BSB

To attach a sample workbook click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[MarvinP]

Hi Steve,

Instead of giving each employee an ID number, why not use their names? That way the data is a lot more obvious and you won't need a lookup table to see who you were thinking about in a few years.

If a full name is too long, how about last and first initial?

[steveUK]

thanks for your response guys, yes I've added a quick example of what I'm trying to do, so what I want to be able to do is enter various ids relating to which engineers may have worked on that job so would need some sort of
multiple entry to calculate my total cost and my invoicing price to the customer, e.g I enter E1 & E4 and it would calculate the start and end date "5 days" at rate D "day" for one engineer at £15 P/H and the second engineer
at £7.50 P/H hours the hours the engineers worked to give me a quick total cost then a invoicing price I can send to my customer.

[steveUK]

yes Initials would work well..

[BadlySpelledBuoy]

Could you show us your sample workbook with expected results?

BSB

[steveUK]

ok here I've quickly edited it so I want it to sort of work like this but not sure of the best way to achieve it.

[steveUK]

see attached

[AliGW]

Your workbook does not appear to have changed at all. You need to remove the attachment and then upload the new version.

[steveUK]

yes I just realised I forgot click upload....lol

[AliGW]

Sorry, Steve, but it's still no different.

[steveUK]

it should have, check the "see attached" post above, I've removed everything else...

[AliGW]

I'm really sorry - I must be missing something! What exactly are you asking us to help you with? You have formulae in place - what more is it that you wish to achieve?

[steveUK]

just a quick update, I've been playing with my example and I am heading in the right direction currently but have hit a little wall, so by using the IF AND OR functions I've managed to get it to change the DAY or Night Rate For 1 Engineer using the Initials as an ID reference but need to get it to work using multiple ID's for other Employees giving my calculation in the same cell, so far I feel that I may have to have extra cells for the different engineers working on the same project which ultimately will calculate it all together the same as my first engineer, not sure if I am losing you here as I might be a little lost myself now so will be taking a small break and will come back to it any input pushing me in the right direction may help, I've attached my work so far..

[steveUK]

ok I am trying to create a spread sheet that by inputting some data for a project I can easily see how much I will have to pay my guys for the total duration of the project and then how much I can invoice my customer, the tricky thing is that my rates are different for my engineers as some have far more experience than others so are on a better day rate also to throw into the mix some project are done at night so again the rates are increased, if you take a look at my latest mock spreadsheet it might make sense, its early days and will be adapting this more as I go to help me run my business as efficiently as I can without the extra costs of my having to pay someone to do it really.

[AliGW]

I really wouldn't do it this way. I would have one row per engineer assigned to the job, so multiple rows for the job reference, and then use SUMIFS to work out the invoice price. It would be much more straightforward than having multiple entries in one cell, which is never advisable.

[BadlySpelledBuoy]

I agree with AliGW on this. It's far easier to sum/count based on single rows of data.

Perhaps a simple userform for inputting data and stamping it to a page would work in this situation. Then a custom table to pull everything together.

Show us what you want and we can go from there.


BSB

[steveUK]

ok sorry please ignore my example files looks like I was having trouble with my save file as I lost the lot and had to redo the lot. I will add my re-add my newest version as I have now managed to get it to work and thought I would show you guys for your comments, I've still some ways to go to where I want it. So far if you change the first cell with the Initials of the employee (see table) the rates will change accordingly and prices will now vary also changing the project date and the Day Or Night Rate also works...

[steveUK]

I never did like simple....lol not sure i know what a userform or a custom table is on excel haven't played with them yet so only going on the small bit I do know..

[steveUK]

yes thanks for your advise I think for me it will just look better all on one row when its finished so as I grow will be easier for me the look at and follow and when I might be running 20 engineers or more I don't want have 20 rows attached to for just one job, but I agree that it would probably be far more simpler but I never liked the easy route so long as it work..well it just works...

[avk]

Do you mean as per engineer name create id. If this is correct then put below formula in "D4"

Please Login or Register  to view this content.

copy down