Database/Spreadsheet experts help!!!

[Ashtonian]

This is probably a lost cause so feel free to close this in an hour or so if noone can help but.....

I am trying to create something within Microsoft Excel, but want to essentially do things that Access could do, with simple buttons and input devices and the like.

Is there any way to link the two up?

The end result i want is that a very simple way of inputting data into a spreadsheet that will calculate the total number of people who need appointments booking. Apart from the click in the box and change it nonsence, the idea is for something someone who cant be arsed to do it will use, with the data accessable to allow someone to keep an nukbers of people, but the person using it to input has the easiest job in the world.

Thank you in advance for any advice on the matter, i am hoping to get a system up and running like this asap. I have the simple excel table set up with sum in the right columns and all that. But as i am not the end users i want to create something fantastic if possible to revolutionise a department.

 

[Garrick]

I am merely a journeyman Excelman, but having seen such devices as your describing, what you need to do is think in terms of multiple sheets. Have one sheet that is the main data entry point, then have other sheets that keep the preset lists for you to reference. You'll be using a lot of IF() commands, but it allows you to make something that appears smart and automated.

Also, keep in mind the presentation helps a lot too to simplify it for the less-inclined. Color code things, gray out and lock unused areas, the like. It makes a spreadsheet a lot less intimidating and easier to understand.

Not really a concrete suggestion, but hopefully it at least keeps you aware to some of the tools that you can use.

 

[Ashtonian]

Sounds great. The person who will use it won't use the change numbers each time on the table. However even if they could potentially click the box say and be able to add or subtract rather than re input the number would be a massive improvement and simplify the system.

That way if it was 25 and they wanted to add or subtract 3 they could type 3 and it would do that.

 

[bluejim95]

How embarrassing... Yes, I am an excel junkie. I can make excel dance using VBA...

Visual Basic for Application. Its a cut down version of the Visual Basic language. Hidden within Excel is a developer tab where you can begin to programme VBA. Here you can create user forms that do all sorts of fancy stuff. They link to the basic spreadsheet and populate the data.

As we would like to think, it sounds simple to set up but the reality is a need for in depth Vba coding knowledge and here is the reason why you haven't already done this.

As you're a blue and we look after our own, I am happy to answer questions when I can and I would even offer to set it up for you but right now I'm doing the same thing for a European transport network and a food manufacturing plant... So working 7 days a week for the foreseeable.

To start, Google the developer tab to find that. Then userforms. When searching for info, start your search by typing VBA, then Excel and the version.. This way your searches will be much more meaningful.

Ozgrid, MrExcel, VBAC and stack overflow are great sites to visit.

You can link excel to access very easily if you know your code by the use of ADO objects. But I won't for one second pretend is easy. You could achieve the whole thing by writing user forms for access as it is a database program. But there is a need to be into relational tables... I think most on here would have bleach bottle in hand at this stage, so I'll just let you Google that one!

I hope that helps

 

[icburns]

I will take a few moments to ponder this in case I can help beyond bluejim's contribution.

 

[Woolly Blue]

I could help but my day rate is £650.

To be helpful for a moment. I'd just write it in Access if I were you. Excel is horrible and needs copious amounts of sawdust.

 

[bol-uk]

How is this getting more action than my conveyancing thread?

 

[Ashtonian]

That is very helpful thank you ! I will look into thta.

Now is this possible, and if so how?

COuld i potentially link Excel and Access together where by the data input could be done in access in the very simple interface of boxes and stuff like a system (what i remember from school) but have the results change in excel and therefore then reflect back in access?

I remember creating systems in school where it was like a software but obviously making access easier to use, where by you had a simple input box rather than a grid to type into if that makes sense So if the calculations and stuff could be done in excel when changing values then in theory this would make a very easy to use system that could be expanded to even print the information off for the user which would be fantastic and make the whole department run a lot smoother in the future.

Now does that make sense? Basically the sums would be done in excel, but the input done in access through a simple pick the month, enter how many patients need to be added or subtracted and then click ok and the system then can show you the correct data, and possibly printed off at a click of a button

 

[SerenityNigh]

COould i potentially link Excel and Access together where by the data input could be done in access in the very simple interface of boxes and stuff like a system (what i remember from school) but have the results change in excel and therefore then reflect back in access?

Can't you create custom forms in Access? Not sure why you'd want to go through the trouble of creating an Excel environment for simple user input when you can do it in Access (unless you have user revolt, or no user access to the db ).

 

[SerenityNigh]

Visual Basic for Application. Its a cut down version of the Visual Basic language. Hidden within Excel is a developer tab where you can begin to programme VBA. Here you can create user forms that do all sorts of fancy stuff. They link to the basic spreadsheet and populate the data...

Embarrassed to say that as much as I use Excel, didn't know this was in it. I think it was out of previous Mac Excel versions, but I'll have to check this out... Could turn hours of nerdy fun into real productivity.

 

[Ashtonian]

Another solution i have to the problem would be to have, probably in access, input the patient details themselves and be able to pull the information off that way. Again not sur eif that is possible or how user friendly it is but somethoing else i need to look into.

First and foremost i will educate myself on the advice above and see how possible it is for me to learn and do. After that i will try and learn how to use access again haha

 

[Coach]

Sounds great. The person who will use it won't use the change numbers each time on the table. However even if they could potentially click the box say and be able to add or subtract rather than re input the number would be a massive improvement and simplify the system.

That way if it was 25 and they wanted to add or subtract 3 they could type 3 and it would do that.

Fairly easy.

Have one box for input, say B2. Have another for the running total, say C2.

Create a button anywhere on your sheet. The idea being that the user enters a number in box B2 and clicks on the button to update the running total in C2.

To add the button,

1. show the developer tab

http://office.microsoft.com/en-gb/e...tab-or-run-in-developer-mode-HA010173052.aspx

2. Click on insert controls

3. Click on the button control and draw yourself a button on the worksheet by holding and dragging the mouse.

4. You will be asked to assign a macro. Click on new and a dialog box will open in VBA development environment. Paste in the following between the Sub and End for the Module,

Worksheets("Sheet1").Activate
Range("C2").Value = Range("C2").Value + Range("B2").Value
Range("B2").Value = 0

5. Close the VBA development environment.

You are good to go. To try it out, type in a value in B2 and C2 and then click the button.

Hope this helps.