If then..

[Andy D 2002]

hi i have olumns of data arranged as, for example, below

Col A Col B Col C ***** ****** ******
Client 1 Region 1 A
Client 1 Region 2 B
Client 1 Region 3 C
Client 2 Region 2 A
Client 2 Region 3 B
etc


If column D2 i have the following type formula:
=If(a1&b1=a2&b2,"",A2&" / "&b2)

Question: if i've hidden columns A and B and i select, say, all "B" records in column C then in example above the cells in column D will (correctly) show ""..

can i modify my formula in column D in any way so show client name and region in these cases..hope makes sense

any help appreciated thanks Andy

[sen_edp]

hello Andy

Please explain further , what exactly you want, be more concise about it.
I tried your example , but i miss something !
thanks

[Chris Davison]

Andy,

drag the formula down from D2 to D6 before you filter ?

(sorry, your example isn't very clear, as Andreas pointed out !)

[DZarlengo]

I've carefully worked through your data, and cannot understand what you want either.

How about explaining it in words rather than formulas? Use an approach such as:

1. I need to show which regions our clients have activity in.
2. I don't want to show duplicates.
3. If a client has no activity, then...
4. If a client has activity, then...

That will make it easier for us to assist you.

[Andy D 2002]

hi all
thanks for replies and sorry as was unclear

my aim is basically not to show "repeats" in a list

for example (and sorry i can't put in this words quite so easily)

In Col A:
Cell A1: "Client"
Cell A2 - A7 i have 1,1,1,2,2,2
Cell B1: "Region"
Cell B2 - B7: i have A,A,A,A,A,A
Cell C1: "Type"
Cell c2 - c7: i have X,Y,Z,X,Y,Z
Cell D1: "Client/Region"
In cell D2 i have formula:=IF(AND(A1=A2,B1=B2),"",A2&" / "&B2)
and this formula is copied down to D7

This formula will show one of each client/region in column D which is good - it looks nice and clear when is printed off - however if i filter records of type Y in column C, my entries in D will now (correctly) be blank

Is there a way i can modify the formula so i still see the relevant client / region in column "D" regardless of my filter on column C?

hope is clearer, really sorry if is not
thanks for reading, any help appreciated
Andy

p.s. i have a work around, but i've never posted this question - if you don't ask you don't get....

[sen_edp]

hello Andy

put this formula in d2 and copy it down

=IF(AND($A$1=A2,$B$1=B2)," ",A2&"/"&B2)

FROM YOUR LAST REPLY ,i think THIS IS WHAT YOU WANT , IF NOT GIVE MORE DETAILS, ON WHAT YOU WISH TO MAKE -

[PDuPre']

I am able to reconstruct your example.
So, basically your formula is comparing the cell reference to the cell reference above it and if they are different then it will display the client and region in the D column.
However, when you apply an autofilter the formulas are comparing to the rows that are hidden. This results in blank labels, when you would like to see the label.
What you need is a dynamic reference that will compare the cell to the cell "shown" above it in the filter. However the data is filtered. Once the comparison is made you will decide if you need the label or not.
Hope that makes sense to everyone.
-------------------------------
Is there a way to have a dynamic reference that is sensitive to the cell "shown" above it in the filtered data?