Evaluate all data on 1st sheet and only bring lines with a specific criteria to 2nd sheet
Posted by sandy on February 02, 2002 2:49 AM
I posted a different version of this on 1st Feb but
the responder (unhelpfully) replaced my post with someone else's -so I'll have to try and explain it all again !
1st sheet is a list (21 columns/5000lines )of sales product data, ("data") mostly text. All this data pertains to 3 different sales areas:- "POLH", "POP", "POH" (shown in column R)
I have a sheet each for POLH,POP,POH set up in exactly the same format as the 'data' sheet.
I need the (say POLH) sheet to show only the lines pertaining to POLH from those listed on the data sheet.
For example, this formula works - in F5 of POLH sheet it shows the data contained in F5 of the "data" sheet
Trouble is, because POLH does appear in R5:R5000
(although not necessarily on every line) when I fill
this formula down the cells show the data exactly as listed on the "data" sheet !
I know I could get round this in a more simplistic way
by returning blank cells if POLH isnt there, but I don't want the 3 "area" sheets to show loads of blank cells, or be 5000 lines long.
I know this is a bit long winded - but in short I'm
looking for a formula (or macro?) that says :-
Go and have a look at all of the 'data' sheet, If "POLH" shows up in column R return cell xxx
on that line. If POLH isn't shown on that line ignore it.(report if POLH, but not if POP or POH)
I've tried all sorts of permuatations and types of formula ...There must be a way to do this..can anyone