single report on data in 20 tables

[UKMAN]

Hi

In 1 sheet have 20 tables of holding the same except for col 1 in each that
hold a unique id for that table. Each table can hold up to 50 rows, so some
cpuld be blank.

I need to produce a specially formatted report that will allow me to select
the unique ID from a drop down and so only that data will be listed

format of table data

ID | Name | R | B | S | X | L1
pc001 colin 200 2 1 1 5

I will insert the same format into the report.

As an add on I have 12 of these sheets each holding 20 tables so it would be
wonderful if I could have 1 report that has a drop down to select the sheet
(each sheet is name after a calander month i.e. jan) and then a drop down to
select the ID. But then I want to win the lottery as well.

many thanks

UKMAN

 

[Hugo Jorgensen]

Hi UKMAN

I hope that I have understood what you want.
I use the following assumptions.
I assume that you have your tables side by side so the only difference is
that they are in different columns. All tables also have the same distance
between them.
The first data cell (ID) in the first table is let us say in B3.
The report starts at A60

1. Create a dropdown list in a cell and give that cell the name Start_value
Use 0 for the first table, 1 for the second etc
2. In another cell enter the number that represents the differrence in
columns between the tables and give that cell the name Distance
3. In A60 insert the following function =OFFSET(B3, Start_value *
Distance,0,0)
4. Use Fill to copy the formula

When you in the dropdownlist select another table the value will be changed.
An improvement to this is also to that in the dropdownlist use the ID and
then in another cell use a VLOOKUP function to select the number. The cell
that keeps that number should then have the name Start_value instead.

Hugo Jorgensen