Auto populating Excel worksheets

PhilGQ · 29 Sep 2008 at 20:55

Hi guys

In my job I have to use lots of spreadsheets for different things. What I would like to do is just have one big spreadsheet which adapts its cells to what is put into it. I understand about making one cell conditional upon others, but what I would like to be able to select details form 2 or 3 drop downs and then the correct formulas appear, so I simply have to fill in the blank fields. I could probably achieve this by individually customising each cell with IFs but I was looking more for a quicker solution than that.

Does anyone know of one.

Participant · 29 Sep 2008 at 21:31

For me, you need to be clearer about what you what as your requirements make no sense

for instance

"I would like to be able to select details form 2 or 3 drop downs and then the correct formulas appear, so I simply have to fill in the blank fields".

PhilGQ · 30 Sep 2008 at 17:11

Ok sorry.

There are about 100 spreadsheets that I need to use at work. Each represents a product that I need to do formulas about.

The the products belong to one of several brand names. Each brand name has several products.

I would like to be able to have a drop down box that lets me select the brand name and then depending on what is selected, then lets me select the product in another dropdown. This I can do with the INDIRECT function using validate.

However once I have selected the brand and product, I would like to the appropriate spreadsheet to appear either automatically or at the click of a button.

Participant · 30 Sep 2008 at 19:57

Ok, I think you can achieve this using macros. However, your problem smacks of needing Access as a solution. Product codes as the the primary key, then linking to a brand table, and then you could run queries to produce the information you want from drop down boxes very efficiently.

I know you don't want to come on here and ask how to do it using Excel only to be told to use Access, but re. the macros I personally can only record and not code that extensively so I doubt I can help you further.

PhilGQ · 1 Oct 2008 at 17:28

Thanks very much, I will have a look at using access to do these things, many thanks again.

Dolph · 1 Oct 2008 at 17:58

Robbie G has said the key thing, you want to be using access to drive the issue, even if the start and end are in excel.

You can use each of the source spreadsheets as a linked data source in access (basically it treats the excel spreadsheet as a table) which eliminates the data import problems and means the data in Access is always the same as the data in excel, and create a front end form to manage it all and create the final record entry that you need.

gacameron01 · 1 Oct 2008 at 21:34

PhilGQ said:
Ok sorry.

However once I have selected the brand and product, I would like to the appropriate spreadsheet to appear either automatically or at the click of a button.

You can use the concatenate function to add a number of those values together to create a target filename to open.