|
Need multiple users advice
Hi guys,
I'm not sure where to post this but I'm looking for some assistance/guidance on what to do with a spreadsheet we are current'y using. We use a 'booking' sheet at work which has a series of colums which has fields such as booking ref,booking time,arrival time,departure time etc... of the vehicles that have been in and out of our warehouse. What we require is for this spreadsheet to become a little more 'advanced' and be a little easier to use and also generate reports for example : Vehicle 1 for customer A had a booking time of 09:00, it didn't arrive till 09:45 and left at 10:15. We'd like for that 'Customer' how many booking slots were missed and by how long and they how long it took for the vehicle to leave in this case it would have been : 45 minutes late 30 minutes 'turn around time' Also one of the main things we require is multiple users can see/edit live data which is not possible at the moment. You can find the spreadsheet here on my webspace http://www.nickyboyc.force9.co.uk/BOOKING.xls Any thoughts and suggestions will be very much appreciated Many thanks in advance Nick |
Need multiple users advice
Hi Nick many thanks for the reply,
Would it be possible to add this spread into for example an access data base or another program ? Or have you a suggestion on how you would solve this ? Regards Nick "Nick Hodge" wrote: Nick Based on the theory that Excel struggles with negative times (early), but I guess with deliveries you would count early as 'on time', you can achieve most of what you want with formulae. The bit that you *will* struggle with in Excel is live, read/write capabilities. Excel doe share workbooks, but it would not be live and it really doesn't work well. To the lateness, use a formula like (Expected and arrival times in B2 and C2 respectively) =IF((C2-B2)<0,0,C2-B2) result formatted as time. Remember if deliveries are likely to be days late sometimes, you will need to have a date column also. (If this happens, add the date and time columns for expected and arrival first and then subtract one from the other), eg =IF(((C2+D2)-(B2+A2))<0,0,(C2+D2)-(B2+A2)) For the standing time, just take the departure from the arrival (Say H2 and F2) =IF((H2-F2)<0,0,H2-F2) formatted as time... Hopefully this is a start -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk HIS "Nick Cartwright" wrote in message ... Hi guys, I'm not sure where to post this but I'm looking for some assistance/guidance on what to do with a spreadsheet we are current'y using. We use a 'booking' sheet at work which has a series of colums which has fields such as booking ref,booking time,arrival time,departure time etc... of the vehicles that have been in and out of our warehouse. What we require is for this spreadsheet to become a little more 'advanced' and be a little easier to use and also generate reports for example : Vehicle 1 for customer A had a booking time of 09:00, it didn't arrive till 09:45 and left at 10:15. We'd like for that 'Customer' how many booking slots were missed and by how long and they how long it took for the vehicle to leave in this case it would have been : 45 minutes late 30 minutes 'turn around time' Also one of the main things we require is multiple users can see/edit live data which is not possible at the moment. You can find the spreadsheet here on my webspace http://www.nickyboyc.force9.co.uk/BOOKING.xls Any thoughts and suggestions will be very much appreciated Many thanks in advance Nick |
Need multiple users advice
For a multi-user environment Access would be far more suitable, and you
could either have queries displaying data there or use Excel as a front end reporting tool with pivot tables perhaps You would want at least one table with all your date, arrival, etc data and then do the calculations in a query -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk HIS "Nick Cartwright" wrote in message ... Hi Nick many thanks for the reply, Would it be possible to add this spread into for example an access data base or another program ? Or have you a suggestion on how you would solve this ? Regards Nick "Nick Hodge" wrote: Nick Based on the theory that Excel struggles with negative times (early), but I guess with deliveries you would count early as 'on time', you can achieve most of what you want with formulae. The bit that you *will* struggle with in Excel is live, read/write capabilities. Excel doe share workbooks, but it would not be live and it really doesn't work well. To the lateness, use a formula like (Expected and arrival times in B2 and C2 respectively) =IF((C2-B2)<0,0,C2-B2) result formatted as time. Remember if deliveries are likely to be days late sometimes, you will need to have a date column also. (If this happens, add the date and time columns for expected and arrival first and then subtract one from the other), eg =IF(((C2+D2)-(B2+A2))<0,0,(C2+D2)-(B2+A2)) For the standing time, just take the departure from the arrival (Say H2 and F2) =IF((H2-F2)<0,0,H2-F2) formatted as time... Hopefully this is a start -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk HIS "Nick Cartwright" wrote in message ... Hi guys, I'm not sure where to post this but I'm looking for some assistance/guidance on what to do with a spreadsheet we are current'y using. We use a 'booking' sheet at work which has a series of colums which has fields such as booking ref,booking time,arrival time,departure time etc... of the vehicles that have been in and out of our warehouse. What we require is for this spreadsheet to become a little more 'advanced' and be a little easier to use and also generate reports for example : Vehicle 1 for customer A had a booking time of 09:00, it didn't arrive till 09:45 and left at 10:15. We'd like for that 'Customer' how many booking slots were missed and by how long and they how long it took for the vehicle to leave in this case it would have been : 45 minutes late 30 minutes 'turn around time' Also one of the main things we require is multiple users can see/edit live data which is not possible at the moment. You can find the spreadsheet here on my webspace http://www.nickyboyc.force9.co.uk/BOOKING.xls Any thoughts and suggestions will be very much appreciated Many thanks in advance Nick |
Need multiple users advice
Good morning, Nick C.,
When I read your first post above, Access came to mind almost immediately. It is better suited overall because of your needs to analyze the data and for several people to be able to use the program at the same time. The problem with Excel in doing analysis on data as you have it is that it's difficult to collate data from numerous worksheets and I suspect there are a lot more going to be involved than the week's worth in the sample you provided. The other problem is the one of simultaneous use by several people. I see a need for that here - you can't have two people scheduling the same resource for use at the same time by two or three different people for one thing. To prevent that, you need keep up to date on when resources are scheduled to prevent possible conflicts. With an Access database you can check almost instantly to see if someone has scheduled the use of something that would potentially conflict with a booking you are in the process of making. Nick Hodges noted you could use Excel as a front end and as an output for reports to be generated. True. But I'd consider building an Access application to be used as the front end and data storage with either output via Access reports or exported to Excel for even further data analysis. Actually I often prefer to export to Excel because it's easier to distribute an Excel workbook and do further analysis on it than it is an Access report. Another nice thing about Access is that in the final product you should have your back end, with the data tables, separated physically into a separate ..mdb file that is linked to with a front end with the business logic, user interface, reports, etc built in to it. Then everyone uses the front end to communicate with the centralized back end and so you have a 'real time' view of resource allocation. You can also be making changes to the front end to revise business rules, revise the user interface, etc. without taking the whole thing off line. My only reservation in recommending Access to look at would be one of size - Access tends to get bogged down when there are huge numbers of records (as in 6-digit numbers of them) to query or of you have a very large number of simultaneous users. It is also still limited to 2 GB for any single table or total .mdb file size - although for most cases that's plenty and if your data tables approach that size you've either come up with work arounds (spreading tables across several .mdb files) or switched to a more robust solution such as SQL Server or another database. For your use, my initial design of the database would be centered around making a booking one record in a table. Other tables I would probably have would be one containing a list of all available resources (trailers, vans, etc) and probably another one with my customer list in it. Well, just about any of the information for a booking that could be drawn from a list of that information would make things go smoother - insuring consistent spelling, use of abbreviations, names, etc. which will make your data analysis more accurate later. "Nick Cartwright" wrote: Hi Nick many thanks for the reply, Would it be possible to add this spread into for example an access data base or another program ? Or have you a suggestion on how you would solve this ? Regards Nick "Nick Hodge" wrote: Nick Based on the theory that Excel struggles with negative times (early), but I guess with deliveries you would count early as 'on time', you can achieve most of what you want with formulae. The bit that you *will* struggle with in Excel is live, read/write capabilities. Excel doe share workbooks, but it would not be live and it really doesn't work well. To the lateness, use a formula like (Expected and arrival times in B2 and C2 respectively) =IF((C2-B2)<0,0,C2-B2) result formatted as time. Remember if deliveries are likely to be days late sometimes, you will need to have a date column also. (If this happens, add the date and time columns for expected and arrival first and then subtract one from the other), eg =IF(((C2+D2)-(B2+A2))<0,0,(C2+D2)-(B2+A2)) For the standing time, just take the departure from the arrival (Say H2 and F2) =IF((H2-F2)<0,0,H2-F2) formatted as time... Hopefully this is a start -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk HIS "Nick Cartwright" wrote in message ... Hi guys, I'm not sure where to post this but I'm looking for some assistance/guidance on what to do with a spreadsheet we are current'y using. We use a 'booking' sheet at work which has a series of colums which has fields such as booking ref,booking time,arrival time,departure time etc... of the vehicles that have been in and out of our warehouse. What we require is for this spreadsheet to become a little more 'advanced' and be a little easier to use and also generate reports for example : Vehicle 1 for customer A had a booking time of 09:00, it didn't arrive till 09:45 and left at 10:15. We'd like for that 'Customer' how many booking slots were missed and by how long and they how long it took for the vehicle to leave in this case it would have been : 45 minutes late 30 minutes 'turn around time' Also one of the main things we require is multiple users can see/edit live data which is not possible at the moment. You can find the spreadsheet here on my webspace http://www.nickyboyc.force9.co.uk/BOOKING.xls Any thoughts and suggestions will be very much appreciated Many thanks in advance Nick |
Need multiple users advice
Hi JLatham,
Many thanks for the great reply, I'm hopeless with access etc... Is it possible you could develope this for me and obviously charge me accordingly ? Best Regards Nick "JLatham" wrote: Good morning, Nick C., When I read your first post above, Access came to mind almost immediately. It is better suited overall because of your needs to analyze the data and for several people to be able to use the program at the same time. The problem with Excel in doing analysis on data as you have it is that it's difficult to collate data from numerous worksheets and I suspect there are a lot more going to be involved than the week's worth in the sample you provided. The other problem is the one of simultaneous use by several people. I see a need for that here - you can't have two people scheduling the same resource for use at the same time by two or three different people for one thing. To prevent that, you need keep up to date on when resources are scheduled to prevent possible conflicts. With an Access database you can check almost instantly to see if someone has scheduled the use of something that would potentially conflict with a booking you are in the process of making. Nick Hodges noted you could use Excel as a front end and as an output for reports to be generated. True. But I'd consider building an Access application to be used as the front end and data storage with either output via Access reports or exported to Excel for even further data analysis. Actually I often prefer to export to Excel because it's easier to distribute an Excel workbook and do further analysis on it than it is an Access report. Another nice thing about Access is that in the final product you should have your back end, with the data tables, separated physically into a separate .mdb file that is linked to with a front end with the business logic, user interface, reports, etc built in to it. Then everyone uses the front end to communicate with the centralized back end and so you have a 'real time' view of resource allocation. You can also be making changes to the front end to revise business rules, revise the user interface, etc. without taking the whole thing off line. My only reservation in recommending Access to look at would be one of size - Access tends to get bogged down when there are huge numbers of records (as in 6-digit numbers of them) to query or of you have a very large number of simultaneous users. It is also still limited to 2 GB for any single table or total .mdb file size - although for most cases that's plenty and if your data tables approach that size you've either come up with work arounds (spreading tables across several .mdb files) or switched to a more robust solution such as SQL Server or another database. For your use, my initial design of the database would be centered around making a booking one record in a table. Other tables I would probably have would be one containing a list of all available resources (trailers, vans, etc) and probably another one with my customer list in it. Well, just about any of the information for a booking that could be drawn from a list of that information would make things go smoother - insuring consistent spelling, use of abbreviations, names, etc. which will make your data analysis more accurate later. "Nick Cartwright" wrote: Hi Nick many thanks for the reply, Would it be possible to add this spread into for example an access data base or another program ? Or have you a suggestion on how you would solve this ? Regards Nick "Nick Hodge" wrote: Nick Based on the theory that Excel struggles with negative times (early), but I guess with deliveries you would count early as 'on time', you can achieve most of what you want with formulae. The bit that you *will* struggle with in Excel is live, read/write capabilities. Excel doe share workbooks, but it would not be live and it really doesn't work well. To the lateness, use a formula like (Expected and arrival times in B2 and C2 respectively) =IF((C2-B2)<0,0,C2-B2) result formatted as time. Remember if deliveries are likely to be days late sometimes, you will need to have a date column also. (If this happens, add the date and time columns for expected and arrival first and then subtract one from the other), eg =IF(((C2+D2)-(B2+A2))<0,0,(C2+D2)-(B2+A2)) For the standing time, just take the departure from the arrival (Say H2 and F2) =IF((H2-F2)<0,0,H2-F2) formatted as time... Hopefully this is a start -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk HIS "Nick Cartwright" wrote in message ... Hi guys, I'm not sure where to post this but I'm looking for some assistance/guidance on what to do with a spreadsheet we are current'y using. We use a 'booking' sheet at work which has a series of colums which has fields such as booking ref,booking time,arrival time,departure time etc... of the vehicles that have been in and out of our warehouse. What we require is for this spreadsheet to become a little more 'advanced' and be a little easier to use and also generate reports for example : Vehicle 1 for customer A had a booking time of 09:00, it didn't arrive till 09:45 and left at 10:15. We'd like for that 'Customer' how many booking slots were missed and by how long and they how long it took for the vehicle to leave in this case it would have been : 45 minutes late 30 minutes 'turn around time' Also one of the main things we require is multiple users can see/edit live data which is not possible at the moment. You can find the spreadsheet here on my webspace http://www.nickyboyc.force9.co.uk/BOOKING.xls Any thoughts and suggestions will be very much appreciated Many thanks in advance Nick |
Need multiple users advice
Nick, I don't know that I'd charge for providing assistance to you. Lord
knows I've given away a lot of help with Access and Excel things to many others here and in a couple of other places I hang out. This doesn't look like a massive project so why don't you drop me an email to 2kmaro @ dslr.net (remove spaces to get legit email addy from that) and we can discuss details of things - number of users, connections available for networking the project, etc. JLatham "Nick Cartwright" wrote: Hi JLatham, Many thanks for the great reply, I'm hopeless with access etc... Is it possible you could develope this for me and obviously charge me accordingly ? Best Regards Nick "JLatham" wrote: Good morning, Nick C., When I read your first post above, Access came to mind almost immediately. It is better suited overall because of your needs to analyze the data and for several people to be able to use the program at the same time. The problem with Excel in doing analysis on data as you have it is that it's difficult to collate data from numerous worksheets and I suspect there are a lot more going to be involved than the week's worth in the sample you provided. The other problem is the one of simultaneous use by several people. I see a need for that here - you can't have two people scheduling the same resource for use at the same time by two or three different people for one thing. To prevent that, you need keep up to date on when resources are scheduled to prevent possible conflicts. With an Access database you can check almost instantly to see if someone has scheduled the use of something that would potentially conflict with a booking you are in the process of making. Nick Hodges noted you could use Excel as a front end and as an output for reports to be generated. True. But I'd consider building an Access application to be used as the front end and data storage with either output via Access reports or exported to Excel for even further data analysis. Actually I often prefer to export to Excel because it's easier to distribute an Excel workbook and do further analysis on it than it is an Access report. Another nice thing about Access is that in the final product you should have your back end, with the data tables, separated physically into a separate .mdb file that is linked to with a front end with the business logic, user interface, reports, etc built in to it. Then everyone uses the front end to communicate with the centralized back end and so you have a 'real time' view of resource allocation. You can also be making changes to the front end to revise business rules, revise the user interface, etc. without taking the whole thing off line. My only reservation in recommending Access to look at would be one of size - Access tends to get bogged down when there are huge numbers of records (as in 6-digit numbers of them) to query or of you have a very large number of simultaneous users. It is also still limited to 2 GB for any single table or total .mdb file size - although for most cases that's plenty and if your data tables approach that size you've either come up with work arounds (spreading tables across several .mdb files) or switched to a more robust solution such as SQL Server or another database. For your use, my initial design of the database would be centered around making a booking one record in a table. Other tables I would probably have would be one containing a list of all available resources (trailers, vans, etc) and probably another one with my customer list in it. Well, just about any of the information for a booking that could be drawn from a list of that information would make things go smoother - insuring consistent spelling, use of abbreviations, names, etc. which will make your data analysis more accurate later. "Nick Cartwright" wrote: Hi Nick many thanks for the reply, Would it be possible to add this spread into for example an access data base or another program ? Or have you a suggestion on how you would solve this ? Regards Nick "Nick Hodge" wrote: Nick Based on the theory that Excel struggles with negative times (early), but I guess with deliveries you would count early as 'on time', you can achieve most of what you want with formulae. The bit that you *will* struggle with in Excel is live, read/write capabilities. Excel doe share workbooks, but it would not be live and it really doesn't work well. To the lateness, use a formula like (Expected and arrival times in B2 and C2 respectively) =IF((C2-B2)<0,0,C2-B2) result formatted as time. Remember if deliveries are likely to be days late sometimes, you will need to have a date column also. (If this happens, add the date and time columns for expected and arrival first and then subtract one from the other), eg =IF(((C2+D2)-(B2+A2))<0,0,(C2+D2)-(B2+A2)) For the standing time, just take the departure from the arrival (Say H2 and F2) =IF((H2-F2)<0,0,H2-F2) formatted as time... Hopefully this is a start -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk HIS "Nick Cartwright" wrote in message ... Hi guys, I'm not sure where to post this but I'm looking for some assistance/guidance on what to do with a spreadsheet we are current'y using. We use a 'booking' sheet at work which has a series of colums which has fields such as booking ref,booking time,arrival time,departure time etc... of the vehicles that have been in and out of our warehouse. What we require is for this spreadsheet to become a little more 'advanced' and be a little easier to use and also generate reports for example : Vehicle 1 for customer A had a booking time of 09:00, it didn't arrive till 09:45 and left at 10:15. We'd like for that 'Customer' how many booking slots were missed and by how long and they how long it took for the vehicle to leave in this case it would have been : 45 minutes late 30 minutes 'turn around time' Also one of the main things we require is multiple users can see/edit live data which is not possible at the moment. You can find the spreadsheet here on my webspace http://www.nickyboyc.force9.co.uk/BOOKING.xls Any thoughts and suggestions will be very much appreciated Many thanks in advance Nick |
Need multiple users advice
Many thanks again JLatham, I've sent you an email.
Best Regards Nick "JLatham" wrote: Nick, I don't know that I'd charge for providing assistance to you. Lord knows I've given away a lot of help with Access and Excel things to many others here and in a couple of other places I hang out. This doesn't look like a massive project so why don't you drop me an email to 2kmaro @ dslr.net (remove spaces to get legit email addy from that) and we can discuss details of things - number of users, connections available for networking the project, etc. JLatham "Nick Cartwright" wrote: Hi JLatham, Many thanks for the great reply, I'm hopeless with access etc... Is it possible you could develope this for me and obviously charge me accordingly ? Best Regards Nick "JLatham" wrote: Good morning, Nick C., When I read your first post above, Access came to mind almost immediately. It is better suited overall because of your needs to analyze the data and for several people to be able to use the program at the same time. The problem with Excel in doing analysis on data as you have it is that it's difficult to collate data from numerous worksheets and I suspect there are a lot more going to be involved than the week's worth in the sample you provided. The other problem is the one of simultaneous use by several people. I see a need for that here - you can't have two people scheduling the same resource for use at the same time by two or three different people for one thing. To prevent that, you need keep up to date on when resources are scheduled to prevent possible conflicts. With an Access database you can check almost instantly to see if someone has scheduled the use of something that would potentially conflict with a booking you are in the process of making. Nick Hodges noted you could use Excel as a front end and as an output for reports to be generated. True. But I'd consider building an Access application to be used as the front end and data storage with either output via Access reports or exported to Excel for even further data analysis. Actually I often prefer to export to Excel because it's easier to distribute an Excel workbook and do further analysis on it than it is an Access report. Another nice thing about Access is that in the final product you should have your back end, with the data tables, separated physically into a separate .mdb file that is linked to with a front end with the business logic, user interface, reports, etc built in to it. Then everyone uses the front end to communicate with the centralized back end and so you have a 'real time' view of resource allocation. You can also be making changes to the front end to revise business rules, revise the user interface, etc. without taking the whole thing off line. My only reservation in recommending Access to look at would be one of size - Access tends to get bogged down when there are huge numbers of records (as in 6-digit numbers of them) to query or of you have a very large number of simultaneous users. It is also still limited to 2 GB for any single table or total .mdb file size - although for most cases that's plenty and if your data tables approach that size you've either come up with work arounds (spreading tables across several .mdb files) or switched to a more robust solution such as SQL Server or another database. For your use, my initial design of the database would be centered around making a booking one record in a table. Other tables I would probably have would be one containing a list of all available resources (trailers, vans, etc) and probably another one with my customer list in it. Well, just about any of the information for a booking that could be drawn from a list of that information would make things go smoother - insuring consistent spelling, use of abbreviations, names, etc. which will make your data analysis more accurate later. "Nick Cartwright" wrote: Hi Nick many thanks for the reply, Would it be possible to add this spread into for example an access data base or another program ? Or have you a suggestion on how you would solve this ? Regards Nick "Nick Hodge" wrote: Nick Based on the theory that Excel struggles with negative times (early), but I guess with deliveries you would count early as 'on time', you can achieve most of what you want with formulae. The bit that you *will* struggle with in Excel is live, read/write capabilities. Excel doe share workbooks, but it would not be live and it really doesn't work well. To the lateness, use a formula like (Expected and arrival times in B2 and C2 respectively) =IF((C2-B2)<0,0,C2-B2) result formatted as time. Remember if deliveries are likely to be days late sometimes, you will need to have a date column also. (If this happens, add the date and time columns for expected and arrival first and then subtract one from the other), eg =IF(((C2+D2)-(B2+A2))<0,0,(C2+D2)-(B2+A2)) For the standing time, just take the departure from the arrival (Say H2 and F2) =IF((H2-F2)<0,0,H2-F2) formatted as time... Hopefully this is a start -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk HIS "Nick Cartwright" wrote in message ... Hi guys, I'm not sure where to post this but I'm looking for some assistance/guidance on what to do with a spreadsheet we are current'y using. We use a 'booking' sheet at work which has a series of colums which has fields such as booking ref,booking time,arrival time,departure time etc... of the vehicles that have been in and out of our warehouse. What we require is for this spreadsheet to become a little more 'advanced' and be a little easier to use and also generate reports for example : Vehicle 1 for customer A had a booking time of 09:00, it didn't arrive till 09:45 and left at 10:15. We'd like for that 'Customer' how many booking slots were missed and by how long and they how long it took for the vehicle to leave in this case it would have been : 45 minutes late 30 minutes 'turn around time' Also one of the main things we require is multiple users can see/edit live data which is not possible at the moment. You can find the spreadsheet here on my webspace http://www.nickyboyc.force9.co.uk/BOOKING.xls Any thoughts and suggestions will be very much appreciated Many thanks in advance Nick |
| All times are GMT +1. The time now is 11:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com