Avoiding Cells

[SamuelT]

Hi all,

I've got a report spreadsheet, that shows the status of around 30
projects as either "Red", "Amber" or "Green".

In another worksheet of the report I want to display all the projects
that are displayed as "Red".

Now, I know that I could have a column that looks at a specific cell
with the project status in and displays it if is says "Red":

=IF(A2="Red",A2,"")

However, this would also mean that where the projects aren't "Red",
there would be gaps in the table.

Is there a formula I can use that looks at specific cells (for example
- A2, A102, A202, A302 and A402), and if they are "Red" displays the
status as such, but if it's not, not leave to a gap in the table.

I hope that makes sense - it's quite hard to explain!

TIA,

SamuelT

 

[R.VENKATARAMAN]

highlight database
data-filter-autofilter-click the arrow in the colum where red,green, amber
are entered-click RED-
you will get only the rows of RED
highlight visible database
click control+G
click specia cell at the bottom left
click visiblecellsonly at the righthand side column in the middle
now click edit copy
paste in somother sheet
go to original sheet data-filter-autofilter so that auato filter is remoed
and original data base is back;.


is this what you wnat?

 

[SamuelT]

That's along the right lines, yes.

I'm just wandering if there is a formula that does this rather tha
using the filters.

Thanks for you help!

Samuel