Setting up a Database

Hi all,

I have a query surrounding databases.

I want to set up a sheet within Excel where i can select a Student name from a drop down list, that bit i'm fine with. When selected i want another cell, say A2 to display a list of information, it may be several cells, i have yet to decide.

Now the tricky part (for me anyhow) i want to be able to edit the information in cell a2 if it is wrong or has changed, but for this info to back fill to the original data sheet.

Any ideas?

Chris

yes, use a database rather than excel Relational databases (like access) do this by default

Thats all well and good, but the end users of this a) dont have access on their machines, but have Excel and b) wouldn't know how to use Access, but have a basic understanding of Excel.

You're knocking a round peg into a square hole but I get where you're coming from.

Conceptually, you need one data sheet with a unique identifier on each row that doesn't change - you need this constant since it will be the id of the user, this id is then used as a reference point - so when you select a user, this is returned also. That allows you to edit all the user's details whilst keeping a constant reference to the row.

I guess you'd probably then want a macro that knows when a cell in the range has been changed and updates the original record.

Maybe like the attached - try changing the name in the drop down and the values in G2:G4

Hi Kyle,

Thanks for the database that you included it is working a dream.

I've moved the cells around a little bit so that i can make some sort of form for the user to fill out. I've got one further change that i want make to it and wanted your help on the VBA script.

On the sheet that allows you to input the data for the write back, is it possible to have three columns? I've moved the input cells to now be C7:C9 instead of G2:G4 in your example. So I want to have three headings Monday, Wednesday and Thursday under which i can enter two lines of info.

I've tried amending the script to go between C7:E9 but this isn't working properly.

Can you help?

chris

You'd need to attach your workbook so I can have a look - though this:

Monday, Wednesday and Thursday under which i can enter two lines of info

Doesn't sound right, you'd only one line of data which relates to one line in your database. You don't want multiple lines in the database per user

Kyle,

how do i upload the spreadsheet to here?

Chris

If you reply > go advanced there's an option to upload attachments

DB Database v2.xlsm

There you go...

Originally Posted by Kyle123

If you reply > go advanced there's an option to upload attachments

Hi Kyle, did you get chance to have a look at the file i uploaded with the amended script?

Chris