Excel and Forms

[Martynt74]

My dads sent me a spreadsheet and asked me to set Excel up with a form type view which i've done. So it gives the option to click a dropdown and then view various bits of info based on index/match formulae.

The issue is that the data is split into 3 sections

Actual Data------Calculated Data---------Proposed Data (Usually a compromise between actual and calculated)

He's then asked if i can update it so that he can update the proposed data using the form.

Naturally using this Excel formulae based form, anything which gets entered will just overtype the the formula and break things.

I did suggest that the best option would be to just use Access rather than Excel but he'd prefer it in Excel for some reason.

I'm now wondering whether i can use some VBA magic and have a button to say

If Data on Form is not based on Formula, then update data on Data tab then replace formula on the form to lookup the value.

That all sounds great....if i actually knew VBA and i don't even know if it's possible. Although it would be good to learn.

The second approach is to use the actual Excel Form function. The issue here is that there are 43 fields so i'd need to look at splitting it into either 2 forms or only having a form based on the proposed values and the rest being formula based.


Does anyone have any suggestions for how best to approach this? I'm beginning to think of going for option 2, it's easier but may not look quite as good.

 

[AHarvey]

Does it have to be in excel?

Webform with database backend would make it easier?

if it has to be excel you may be better using the form function to gather data, then setup the sheets differently to then refer to that data and apply formulas to that.

 

[Martynt74]

Sadly i think it does need to be Excel. The data will likely be imported from an external system.

As a bit of background, the data is usually product driven, with stock management info for reorder points, min/max quantities etc. There will then be formula to calculate ideal levels for these figures.

He then wants to be able to analyse the original and calculated data and suggest proposed recommendations (where the form will be needed)

As you say i think i'll just have to restructure the sheets and use a table for the proposed data which then links through to the other tables. That way i can use the FORM function to set it up as he wants.

 

[gpuerrilla]

To be honest I would be tinkering with it in an acces DB till I understood what he wants to mine. I know people have a hard on for spreadsheets but when you get a ton of records it belongs in a database and I would be surprised if you could not store it in Access and retrieve specific info to handle in excel. These days m$ make them work well together.

 

[wesimmo]

So you're not actually using VB Forms, you've just created a form like look?

What are the drop downs, data validation in the cell or a combobox or similar?

 

[Grrrrr]

I don't entirely follow what you're asking but it certainly sounds possible. Can you mock up something that shows what you have and what you need?