Template where rows and tabs will be variable for each use

[Joanne]

Hello all

I need some advice for a template I'm creating. It is very complex and I
need to make it as easy as possible for the users. It will be used to track
product report that are sent from central site to country affiliates, when
they receive the report they have to return a tracking sheet stating when and
whether they submitted the report to their local authority. We do this
tracking summary once a month. So one month's summary will cover x number of
products and each product will cover x number of countries.

The current design is that each worksheet contains data for one specific
product report, and the rows of the worksheet are the list of countries that
the report was sent to. These all map to a summary worksheet which calculates
the statistics.

Each month will be different and so what worries me is how to keep all the
formulas and summary statistics whilst being able to varry the number of
products/countries.

My ownly real workable idea is that all the possible countries will be
entered on each worksheet and there will be a large number of worksheets so
the user will have to delete what they don't need. But that is not only
confusing, but also time consuming. So I would really appreciate other ideas!

Thank you in advance
Joanne

 

[Spiky]

How are the countries to be selected?

I like to make an extra sheet with all the lists and databases I need,
and hide it from the users. Then you can reference the lists however
necessary. Perhaps using drop-downs for them to select the proper
location. Or VLOOKUPs if possible.

 

[Joanne]

Hi thanks for your suggestion

A drop down list would be the best way to select countries I think.

So if I referenced the countries from another list would it automatically be
able to copy all the equations over for that row?

Any ideas for allowing users to add the required number of worksheet tabs
for each product? So that each tab they add already has the required
equations in?

 

[Spiky]

So if I referenced the countries from another list would it automatically be

able to copy all the equations over for that row?

If you can set your data up in the proper format, which it sounded
like you already have, you can use a drop-down to select the country
in the first column, and then use VLOOKUP in the other columns to
automatically pull up other data using the country name as a
reference.

Any ideas for allowing users to add the required number of worksheet tabs
for each product? So that each tab they add already has the required
equations in?

1) Advanced, reliable users: Have a blank template sheet that can be
copied each time a new sheet is needed.
2) Less reliable users: Have a blank template sheet that is hidden
with a macro button somewhere which will copy it. They can just click
on the button.