LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 193
Default revised q on report from tables

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?


"UKMAN" wrote in message
...
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" wrote:

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)+$A 2

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" wrote in message
...
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" wrote:

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" wrote in message
...
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



 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
single report on data in 20 tables UKMAN Excel Worksheet Functions 1 April 19th 10 06:49 PM
Pivot Tables vs. Report in Access LOST Excel Discussion (Misc queries) 4 July 24th 07 07:34 PM
How do I consolidate 2 tables into 1 pivot report? Deb-tasha Excel Discussion (Misc queries) 2 October 5th 06 08:09 AM
Pivot tables - Format Report hiunnati Excel Discussion (Misc queries) 1 May 13th 06 04:46 PM
Pivot tables - Format Report hiunnati Excel Discussion (Misc queries) 0 May 13th 06 02:23 PM


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

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

About Us

"It's about Microsoft Excel"