Basic database in Excel?
Discussion
Hey guys, im trying to create a basic database for logging daily activities, like a register.
Ive got a list of items in Excel, just not sure how to go about making a form where I type the name in to each cell, and it brings up a suggestion based on the list of items so I can press enter and move onto the next one. Is anyone able to help?
Ive got a list of items in Excel, just not sure how to go about making a form where I type the name in to each cell, and it brings up a suggestion based on the list of items so I can press enter and move onto the next one. Is anyone able to help?
Do you mean something like you click on a cell & see a list which you can then select from? If so then right click on a cell & then look for 'Pick From Drop-down List'. You need to create the list first of course.
https://support.office.com/en-us/article/Create-a-...
https://support.office.com/en-us/article/Create-a-...
Excel will autofill once you've got the record in the list. Format your table as a table and it definitely works, though later versions can pick out patterns and do this for you as well.
If you want to limit the options available and predefine them, use Data Validation to restrict cell contents for each column.
If you want to limit the options available and predefine them, use Data Validation to restrict cell contents for each column.
Thanks guys. I watched that tutorial and I've got my list and converted it to a table.
I want to make it fill in the gaps based on what's next to it - for example:
| Name | Surname | Occupation | Details | Etc |
If I enter a name, i'd like to to automatically fill in the rest of the columns based on whats on my list. At the moment it wants me to fill in each column individually.
The other issue i've got is that I have to click on the dropdown and select each name one by one, if i type a name it simply wont recognise it and will give me an error, 'This value doesn't match the data validation restrictions defined for this cell'.
For example:
If I type 'Dave' it will give me an error. if i select 'Dave' from the dropdown it works fine. Once i've selected 'Dave' using the dropdown, if i try to type 'Dave' it will now autofill.
I want to make it fill in the gaps based on what's next to it - for example:
| Name | Surname | Occupation | Details | Etc |
If I enter a name, i'd like to to automatically fill in the rest of the columns based on whats on my list. At the moment it wants me to fill in each column individually.
The other issue i've got is that I have to click on the dropdown and select each name one by one, if i type a name it simply wont recognise it and will give me an error, 'This value doesn't match the data validation restrictions defined for this cell'.
For example:
If I type 'Dave' it will give me an error. if i select 'Dave' from the dropdown it works fine. Once i've selected 'Dave' using the dropdown, if i try to type 'Dave' it will now autofill.
Edited by fiju on Monday 12th August 13:49
Regarding the auto population, I assume what you want is when you select "John" it fills in all the corresponding values for John" - sounds like you need a VLOOKUP?
So you can have a static list of all the names with their details
and when you pick one name it will populate the lookup
so something like this
VLOOKUP(A2, Sheet 2!A:H, 3, FALSE)
would bring back the third column from sheet 2, based on value in A2
FALSE means exact match on A2
So you can have a static list of all the names with their details
and when you pick one name it will populate the lookup
so something like this
VLOOKUP(A2, Sheet 2!A:H, 3, FALSE)
would bring back the third column from sheet 2, based on value in A2
FALSE means exact match on A2
Edited by hungry_hog on Monday 12th August 18:07
Definitely sounds like VLOOKUP will do what you want
Let's say you have the following in columns C and D
E F
John Smith
Alan Turing
Jim Turner
Then if you want to enter a first name in cell A1, and auto-populate B1 with the surname, you'd have data validation on cell A1 to pick from a list C1:C3 and the formula you'd put in cell B1 is:
=VLOOKUP(A1,E:F,2,FALSE)
That says find the first occurence of John (A1) in the two column table represented by C through D and return the matching value in the 2nd column (D in this case) which would autofill it with 'Smith'
There are plenty of prettier examples on the web that illustrate it better than this.
This relies on the first column of whatever you define as the table having unique values in it. So if you have John Hansen it would still return Smith by default. I also find it better to have the lookup tables on a different worksheet to confuse things less as per the previous poster's example.
Let's say you have the following in columns C and D
E F
John Smith
Alan Turing
Jim Turner
Then if you want to enter a first name in cell A1, and auto-populate B1 with the surname, you'd have data validation on cell A1 to pick from a list C1:C3 and the formula you'd put in cell B1 is:
=VLOOKUP(A1,E:F,2,FALSE)
That says find the first occurence of John (A1) in the two column table represented by C through D and return the matching value in the 2nd column (D in this case) which would autofill it with 'Smith'
There are plenty of prettier examples on the web that illustrate it better than this.
This relies on the first column of whatever you define as the table having unique values in it. So if you have John Hansen it would still return Smith by default. I also find it better to have the lookup tables on a different worksheet to confuse things less as per the previous poster's example.
otolith said:
Excel isn't a database. I'm not sure whether what you are proposing building is either, but if it is, you'll be better in the long term using something like MS Access or one of the free alternatives (LibreOffice Base, etc)
Versions post 2013 allow you to build a relational database that can be queried with Pivots within Excel, which would be powerful enough for what the OP is trying to do I think. If it's not then an SQL solution is likely to be more appropriate than Access, but Access would be an OK solution.The problem with what the OP wants to do is that he's heavily restricting how his database can be used to the extent that I'd be fairly confident he'll get errors if he uses VLOOKUP as suggested. As soon as there's a second 'John' on the list, it will fall down, it sounds like an administrative nightmare.
OP - if I was doing this in Excel I would normalise the data requirements into a relational state, then build those tables in Excel on Worksheets. Format as tables and use Data Connections to add them to the Data Model. Go into Relationships and you can then define the links between the tables. You enter data by maintaining the tables of data and you query the database by drawing the Pivot Table you need.
In your example, you would setup John in the Person table, with columns for the rest of his details in there, then you would add information about John on the tables for that information, those tables would only need Johns Unique ID to allow whatever your outputs are to pull all of John's details into those tables.
The next step is to maintain the tables using UserForms so that you can select John from a drop-down list there, when you add your information about John, the UserForm will ensure the correct UserID is pulled from the User Table.
I reckon at that point if you need to go further it's time to move to a 'proper' database and I'd then look at moving it to an Azure setup on the Cloud personally, but that's a business decision as much as a technical one.
The Excel option is very good for designing a static database and checking it has all the data you need, as a way of developing a proper 'live' system.
paulrockliffe said:
otolith said:
Excel isn't a database. I'm not sure whether what you are proposing building is either, but if it is, you'll be better in the long term using something like MS Access or one of the free alternatives (LibreOffice Base, etc)
Versions post 2013 allow you to build a relational database that can be queried with Pivots within Excel, which would be powerful enough for what the OP is trying to do I think. If it's not then an SQL solution is likely to be more appropriate than Access, but Access would be an OK solution.Can you assign each contractor an ID number?
As in 001, 002 etc etc and then vlookup on the ID to auto complete the table.
Or if you could afford to spend maybe £250 you could get something like a Paxton Net2 controller and prox reader.
They then fob in and out, then you can do a report based on this time in/time out.
As in 001, 002 etc etc and then vlookup on the ID to auto complete the table.
Or if you could afford to spend maybe £250 you could get something like a Paxton Net2 controller and prox reader.
They then fob in and out, then you can do a report based on this time in/time out.
Certainly the simplest solution that isn't too likely to cause issues is a VLOOKUP on a unique ID, but then you have to remember the Unique IDs to grab the other details.
You could do a double VLOOKUP, where you use Concatenate to create a list of =CAT(FirstName, " ", LastName) to create a list of all people (assuming you don't have two John Smiths), then use Data Validation to restrict your input to just those in that list. VLOOKUP to get the ID from that combination, then VLOOKUP to get the rest of the details. That would let you select from a drop-down.
We're heading towards the territory where I'd generally expect that if you could implement this approach properly, you wouldn't need to ask the question. That's a fairly loose rule and I know that you can learn how to use lots of Excel to an expert level without knowing this sort of stuff.
There is quite a lot of nuance in getting any of these solutions to work.
You could do a double VLOOKUP, where you use Concatenate to create a list of =CAT(FirstName, " ", LastName) to create a list of all people (assuming you don't have two John Smiths), then use Data Validation to restrict your input to just those in that list. VLOOKUP to get the ID from that combination, then VLOOKUP to get the rest of the details. That would let you select from a drop-down.
We're heading towards the territory where I'd generally expect that if you could implement this approach properly, you wouldn't need to ask the question. That's a fairly loose rule and I know that you can learn how to use lots of Excel to an expert level without knowing this sort of stuff.
There is quite a lot of nuance in getting any of these solutions to work.
otolith said:
paulrockliffe said:
otolith said:
Excel isn't a database. I'm not sure whether what you are proposing building is either, but if it is, you'll be better in the long term using something like MS Access or one of the free alternatives (LibreOffice Base, etc)
Versions post 2013 allow you to build a relational database that can be queried with Pivots within Excel, which would be powerful enough for what the OP is trying to do I think. If it's not then an SQL solution is likely to be more appropriate than Access, but Access would be an OK solution.I only have Excel for delivering data to my users, so I've become quite adept at building spreadsheets that handle huge data volumes, can be refreshed remotely and insulate the users from anything that isn't a Pivot Table. The Data Model in Excel is brilliant for that sort of stuff, even if Excel is the wrong way to do it unless you absolutely have to.
paulrockliffe said:
Could be worse, they could have come to you with their idea and asked you to make it from scratch. Excel is a great way for users to translate their issue into a working (ish) design that can easily be explained to a database designer, if you can stop them building an ecosystem around it anyway - "Here's this thing that does what I want" is much more useful than "Can you build me a thing thing please?".
I only have Excel for delivering data to my users, so I've become quite adept at building spreadsheets that handle huge data volumes, can be refreshed remotely and insulate the users from anything that isn't a Pivot Table. The Data Model in Excel is brilliant for that sort of stuff, even if Excel is the wrong way to do it unless you absolutely have to.
Really I'd rather have a business analyst look at their problem and come up with suitable technical and process improvements. Otherwise you end up with a solution designed to work around a process which was built around the limitations of Excel.I only have Excel for delivering data to my users, so I've become quite adept at building spreadsheets that handle huge data volumes, can be refreshed remotely and insulate the users from anything that isn't a Pivot Table. The Data Model in Excel is brilliant for that sort of stuff, even if Excel is the wrong way to do it unless you absolutely have to.
otolith said:
paulrockliffe said:
Could be worse, they could have come to you with their idea and asked you to make it from scratch. Excel is a great way for users to translate their issue into a working (ish) design that can easily be explained to a database designer, if you can stop them building an ecosystem around it anyway - "Here's this thing that does what I want" is much more useful than "Can you build me a thing thing please?".
I only have Excel for delivering data to my users, so I've become quite adept at building spreadsheets that handle huge data volumes, can be refreshed remotely and insulate the users from anything that isn't a Pivot Table. The Data Model in Excel is brilliant for that sort of stuff, even if Excel is the wrong way to do it unless you absolutely have to.
Really I'd rather have a business analyst look at their problem and come up with suitable technical and process improvements. Otherwise you end up with a solution designed to work around a process which was built around the limitations of Excel.I only have Excel for delivering data to my users, so I've become quite adept at building spreadsheets that handle huge data volumes, can be refreshed remotely and insulate the users from anything that isn't a Pivot Table. The Data Model in Excel is brilliant for that sort of stuff, even if Excel is the wrong way to do it unless you absolutely have to.
Maybe you have better Business Analysts, I'd rather see what the end user has done and interrogate them and that in the context of what's possible technically and within the business ecosystem, stuff a Business Analyst usually doesn't really understand in my experience. The alternative is you get a spec from a Business Analyst and then get the blame for building something the user doesn't want. Then they go back to their spreadsheets and make sure they never tell anyone about them again.
fiju said:
I think were veering a bit off course here...
Would I be better off using Access to achieve this? I'm not as competent as some of you with excel or access, I pretty much do basic stuff with it, so go easy on me
Have a look at Monday.com - very slick online tools including time recording which is fundamentally what you want. Would I be better off using Access to achieve this? I'm not as competent as some of you with excel or access, I pretty much do basic stuff with it, so go easy on me
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff