Excel database modification

[Charmwah]

Hi all

I have an existing database in a worksheet, with customers arranged down the rows, and variables for the customers arranged along the columns. I then have a separate worksheet which acts as an 'At-a-glance' page whereby you choose the customer from a drop down list and a series of cells with INDEX & MATCH formulas retrieve the information for that customer from the database.

So far so good. What i'd then like to be able to do is, using the 'At-a-glance' page, let the user modify the result of the retrieved data (either in the same cell, or an adjacent cell) and then to copy that modified result back to overwrite the database.

Is this even possible? Can anybody think of a sensible way to do this? I presume it would involve some sort of macro but i'm not sure where to start...

Thanks in advance!

 

[Charmwah]

Hi Mumps

Please accept my apologies for the delay, i've been away on holiday and then on a business trip.

The product references are important, my labeling is somewhat irrelevant in that for all groups they could be just AA & BB. What important is that you can narrow down your search criteria with the drop-down steps: Customer>Type>Product. This then displays the variables for those choices.

Kind regards

Dave

 

[mumps]

A macro that works on a sample file will most often not work on the actual file. Can you upload a copy of the actual file? De-sensitize the data if necessary.

 

[Charmwah]

Hi Mumps

I'm happy to do that, but i'd rather DM you the link to the file...

Kind regards

 

[mumps]

If it's possible to replace any confidential data with generic data, it would be best to post the link here so that other Forum members can follow, otherwise, PM me the link.

 

[Charmwah]

If it's possible to replace any confidential data with generic data, it would be best to post the link here so that other Forum members can follow, otherwise, PM me the link.

Hi Mumps

Again, sorry for the delay, I've been travelling a great deal with work. I've paired down the actual model of the database and made it into ordinary data so that I can post a link here:

https://www.dropbox.com/s/ciqb7n8spvfkxxc/Example4.xlsm?dl=0

Hopefully this will help.

Again, thanks for your help on this, really appreciate it!

Dave

 

[mumps]

Click here to download your file.
I have had to make modifications to your workbook in order to get it to work. First of all, the merged cells were creating havoc for the macro so I had to eliminate them but still achieved the same look by using "Centre Across Selection". You should avoid using merged cells in your workbooks if at all possible. Secondly, since you are using named ranges which don't allow spaces, I have had to change some of the headers in the "Database" sheet. I also had to add numbers to some of the headers to distinguish between some duplicate headers. Enter some data in the "Database" sheet. In the "Lookup" sheet, make a selection in D3 and D4 first and the selection in D5 must be made last. Everything in the "Lookup" sheet will be filled in automatically. The macro is also based on only 4 variables. Please note that transferring the macro to your actual workbook will not work because it won't have the modifications that I have made in the attached version.

 

[Charmwah]

Thanks Mumps.

I think you've misunderstood me, there seems to be none of the variables on your revised document, only the dimension fields. The variables below these were supposed to be populated and edited as well.

I apologise if I didn't make it clear. I can appreciate you've spent a while on this now and you probably have better things to do. Perhaps i'd be best trying to learn the theory behind what you're doing from a tutorial or book.

Kind regards

Dave

 

[mumps]

Because the variables change with each room and feature, the variables will be populated automatically, when you make your selections in D3 and D4 first and then in D5. That is why they start out as blank. Enter some data in the "Database" sheet and give it a try.

 

[Charmwah]

You're quite right, my bad! Thanks again Mumps, that does exactly what I wanted.

Really appreciate your time and patience!

Because the variables change with each room and feature, the variables will be populated automatically, when you make your selections in D3 and D4 first and then in D5. That is why they start out as blank. Enter some data in the "Database" sheet and give it a try.

 

[mumps]

You are very welcome.