revised q on report from tables

[UKMAN]

Hi

Would still love help on previous regarding 20 tables but in a panic.

I have now ref all tables to 1 long list so as a quick fix:

the col headings of the list are PC | Name | Rate | B | S |X | L1

In the report sheet I have a drop down list of the PC col.

I would like to be able to select the PC and the report be filled with the
following data only:

| Name | Rate | B | S |X | L1

I have used vlookup and tried to hack an old code that can be copied down
but it will only ever show the first record

Sorry for being thick..

Cheers
for all help and I am learning

UKMAN

 

[Roger Govier]

Hi

If all the data is now in one list, then Data>Filter>Autofilter
Select the PC required from the PC column, and all related data will be
visible.

 

[Steve Dunn]

Hi,

I'm assuming that you have your drop-down in A1 on the report sheet, and you
want a list of multiple items to be brought from Sheet1!$B$2:$G$100 to
Sheet2, filtered by the value in A1. This can be done without any figures
in column A, using an array formula, but the following method should be
easier to understand and modify to your own requirements:

In A2:

=MATCH($A$1,Sheet1!$A$2:$A$100,0)

In A3:

=MATCH($A$1,OFFSET(AutoFilter!$A$2:$A$100,$A2,),0)+$A2

In B2:

=INDEX(AutoFilter!B$2:B$100,$A2)

Copy B2 across and down B2:G3

Then copy A3:G3 down as far as required.


HTH
Steve.

 

[UKMAN]

Roger,

would use filtering but (a) the report is preformat i.e. text surrounds (b)
the tables are on a seperate sheet.

Many thanks anyway

Ukman

 

[UKMAN]

Steve,

many thanks for your help.

after removing the autofilter I got it to work on the same sheet but
couldn't work across sheets? Autofilter gave errors???

The other problem was that when a blank row in the table it just repeated
the first record?? Below is acopy & paste of the table. it goes on for 983
rows and the Project code goes from PC01 to PC20. Hope this helps. The lookup
shows the Proj Code for selection.

Proj Code Name Charge B S L1 NB

PC01 colin $400.00 $1.00 $0.50 $0.50
PC01 fred jones $200.00 $1.00


hope this helps

Many thanks

 

[Steve Dunn]

Oops, forgot to change the sheet name in the second and third formulae (I
was working with a sheet called AutoFilter). Should have been:

In A2:

=MATCH($A$1,Sheet1!$A$2:$A$1000,0)

In A3:

=MATCH($A$1,OFFSET(Sheet1!$A$2:$A$1000,$A2,),0)+$A2

In B2:

=INDEX(Sheet1!B$2:B$1000,$A2)

Sorry about that. Can't see a reason why blank rows would cause a problem.
If you'd like to e-mail your file to me, I'll take a look at it.

 

[UKMAN]

Steve,

Absolutly no problem as I made an error in my copying down i.e. a2 I didn't
copy down with the other cells

I have now moved the formulas to the report on a differant sheet and works
great.
One question though, due to the way the data is collated in the report it
will have blank lines between records in some circumstances so could the code
be modified so to ignore blank rows??

If not no worries but many thanks for your fanastic help. You made a hard
problem sound easy to resolve.

Cheers
UKMAN

 

[Steve Dunn]

I can't see why there would be a problem with blank rows. Would you like to
send your workbook to me, so I can investigate more directly?