Posts by r_hind

    Bear in mind that you're probably better working with data validation rather than lots of combo boxes.


    Data validation allows you to control the input to effected cells and allows multiple choice through drop down lists in exactly the same way that a combo box does. It has the advantage (occassionally a disadvantage) of returning the actual selected value rather than the position number in the list.

    I like your solution thomach


    You can also do the same sort of thing by taking the data (named range) into a vba array ...manipulate with code into another array then dump it out to a new named range.


    look carefully at the named ranges (some are dynamic ...and "data" had to be extended...

    I was going to suggest using named ranges...but I think it boils down to how you delete the data.


    If you delete the whole sheet then the formulas that refer to the cells on the deleted sheet will have their links broken (refering to a cell on another sheet always includes a reference to the sheet too. If you apply a named range that also implies a sheet as well as cells).


    If you delete individual cells or groups of cells there shouldn't be a problem.'


    Alternatively if you use Paste, Special, Values to the write the new values over the top of the old values you shouldn't have a problem.

    For quite a number of years I've used Excel's Data Table Feature to show / compare the results for a number of scenarios.


    For example:
    Do a complex calculation involving several pieces of capital equipment (say trucks) in different configurations (capacities / trailers etc) and different manning and utilisation levels and get the result.


    Include depreciation schedules, cash flows, full profit analysis.


    Now do the same thing (in the same spreadsheet!) for say 20 completely different scenarios.


    Compare the results in one spreadsheet!


    Don't fall into the trap of taking too seriously the help file instructions associated with Data Tables. They will lead you to think you can only change one or two variables!!


    By having one of the variables act as a pointer to a LIST of variables you can change as many variables as you like within a single scenario.

    The attached spreadsheet is intended only to demonstrate the concept of data tables and scenarios.


    The calculations are really only nonsense but should indicate the technique...

    Don't overlook the use of data tables.


    Whilst in their standard form data tables use either a single or two variables(changing) you can actually use one of the variables as an index or pointer to scenario tables with an unlimited number of variables.


    That way the model that does the number crunching doesn't change ...but the variables pumped through does ....and you can see the complete set of results for all scenarios.

    You can always use VBA to save records to say a "*.dat" file and use vba code to read in, search or manipulate data.


    I frequently use excel to open large quantities of (csv) files extract data that matches certain criteria (using a filter) then combine the required info.