I am trying to display data (in Sheet 2) based on what has been
entered in a seperate spreadsheet (i.e., in Sheet 1).

If there is a positive value in column C (in Sheet 1), then i want to
show the contents of that entire row (in Sheet 2). If there is not a
positive value in column C (in Sheet 1), then I want that row to be
hidden or not show up at all (in Sheet 2).

Is there a formula or technique that can be employed to accomplish
this "report" view.

Any suggestions would be appreciated.

Thanks!

Copy sheet1...........name it sheet2

Use Data>Filter>Autofilter on sheet2 and filter for negatives in C

Otherwise, you need VBA to copy over just sheet1 C negatives and their row
to sheet2

OR.............just do the filtering on sheet1 and forget sheet2

Check out help on how to create custom views so's you can switch from one
view to another.


Gord Dibben  MS Excel MVP


On Fri, 7 May 2010 14:09:22 -0700 (PDT), Dave K <fred.sheriff@gmail.com>
wrote:

>I am trying to display data (in Sheet 2) based on what has been
>entered in a seperate spreadsheet (i.e., in Sheet 1).
>
>If there is a positive value in column C (in Sheet 1), then i want to
>show the contents of that entire row (in Sheet 2). If there is not a
>positive value in column C (in Sheet 1), then I want that row to be
>hidden or not show up at all (in Sheet 2).
>
>Is there a formula or technique that can be employed to accomplish
>this "report" view.
>
>Any suggestions would be appreciated.
>
>Thanks!

Hi Dave,

Sorry this is a bit late (I don't normally frequent this group, but I'm 
having a very slow day at work!)

Put this in the top left cell of your report range:

=INDEX(Sheet1!$A$1:$F$50,SMALL(INDEX((Sheet1!$C$1:$C$50>=0)*
(ROW(Sheet1!$A$1:$F$50)-CELL("ROW",Sheet1!$A$1:$F$50)+1),),
COUNTIF(Sheet1!$C$1:$C$50,"<0")+ROW(1:1)),COLUMN(A:A))

then copy it across and down as required.

You may need to change the comparison operator depending on how you want to 
handle 0s; the formula above treats 0s the same as positive values, to treat 
it the same as negative values try:

=INDEX(Sheet1!$A$1:$F$50,SMALL(INDEX((Sheet1!$C$1:$C$50>0)*
(ROW(Sheet1!$A$1:$F$50)-CELL("ROW",Sheet1!$A$1:$F$50)+1),),
COUNTIF(Sheet1!$C$1:$C$50,"<=0")+ROW(1:1)),COLUMN(A:A))


HTH
Steve D.


"Dave K" <fred.sheriff@gmail.com> wrote in message 
news:c5ccd902-18a3-42c7-b897-13b421a3dd7d@o14g2000yqb.googlegroups.com...
>I am trying to display data (in Sheet 2) based on what has been
> entered in a seperate spreadsheet (i.e., in Sheet 1).
>
> If there is a positive value in column C (in Sheet 1), then i want to
> show the contents of that entire row (in Sheet 2). If there is not a
> positive value in column C (in Sheet 1), then I want that row to be
> hidden or not show up at all (in Sheet 2).
>
> Is there a formula or technique that can be employed to accomplish
> this "report" view.
>
> Any suggestions would be appreciated.
>
> Thanks!