worksheet setup for ever increasing data base

[marinesurvey]

[SIZE="2"]I am trying to create a simple spreadsheet to calculate total weight from a series of known reference weights (records) and variable user input quantities. For example, in plain terms:

KNOWN REFERENCE WEIGHTS

Unique Name, Value, Units

Ref weight #213, 10, kg
Ref weight #214, 9, kg


LIST OF KNOWN ROOMS

Room 1
Room 2
Room 3

USER INPUT VARIABLE QUANTITIES

Room, Contains, Of,
Room 1, 1 each, Ref weight #213
Room 2, 2 each, Ref weight #214
Room 3, 2 each, Ref weight #214

====================

The results are then displayed / totalled / averaged for various reports.

====================
The lists or table of reference weights and Rooms (records) will change from time to time, particularly for the addition of more reference weight data records and new rooms. So in the above example, a new Ref weight #215 or Room 4 might be added.

====================

Problem:

When an additional "reference weight" (#215 for example) or Room (Room 4) is added to the reference lists, I would like the Report containing the calculations for each room in the reference list to update automatically to always include a calculation for the new reference weight item (#215) and display the results.

Similarly, if reference weight (#213) or Room (Room 2) is deleted in the lists, I want the Report calculations sheet to not show it anymore.

I prefer to use columns for the Rooms in the report, because I can filter out the rows (reference Weights) not containing results and make shorter reports. But perhaps this is not the best way and I am stumped.......... so I put it to the forum.

I have tried to avoid using terms like "records" and "database" and "table", so as not to prejudice the solutions offered, but suggest away..

The list of reference Weights will be perhaps 500 records long at most. The number of Rooms will be 100 at most.

======================

I know it can be managed by inserting rows or columns and dragging formulas to have the Report sheet perform calculations on the new Weights or Rooms, but I want to automatically include every record that is in the reference weight and room lists without having to drag down formulas in the columns for say 100 rooms

=======================

Based on what I see on some of the threads, I am a beginner user compared, but am familiar with many Excel functions.

Arranging this spreadsheet most efficiently from the start would be great, because it is certain new reference Weights and Rooms (records?) will be added to the reference weight list in the future, and I want to take advantage of automatic updating.

The calculation performed (user input quantity * Reference Weight) will always be the same.

Any ideas from thise of far greater experience would be appreciated. Thanks very much.

[Cheeky Charlie]

I think you should use a database, and I hate using databases.

CC