A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

single report on data in 20 tables



 
 
Thread Tools Display Modes
  #1  
Old April 19th, 2010, 03:18 PM posted to microsoft.public.excel.worksheet.functions
UKMAN
external usenet poster
 
Posts: 70
Default single report on data in 20 tables

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
  #2  
Old April 19th, 2010, 06:49 PM posted to microsoft.public.excel.worksheet.functions
Hugo Jorgensen[_2_]
external usenet poster
 
Posts: 2
Default single report on data in 20 tables

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

"UKMAN" wrote:

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:04 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.