Using a Named Range within a Formula

[LGMan]

Hi, I been getting some help over on the VBA forum regarding Conditional Formats, although nobody grumbled, I hold my hand up for my mistake for posting a non VBA question the that forum ;-)

The help got me over that hurdle, but I have another similar one now with a Formula
I have currently been able to get some logic working via this Formula so am happy with that.
=IF(AND($B$3="COM",$B$4="ABC"),(B9),(LEFT($B$3,5)&$B$4))

However, I'd like to substitute the $B$4="ABC" part for a named range, but have no idea how to achieve this and get the logic to work as it should. Obviously I have defined the named range.

Thanks in advance

 

[SkipVought]

Hi,

Suppose that A4 has a range name of SelectedValue.

In your expression...

SelectedValue="ABC

 

[LGMan]

My named range has 5 cells with values one of which is ABC.

The existing formula works for 'ABC', but I'd like it work with any of the other 4 codes that I have in the range.

Thanks

 

[SkipVought]

I thought you had a combo box that placed the selection in A4???

 

[SkipVought]

The measure description appears in A4, e.g Employee Sickness
So Targets are in B4:M4
Data appears in A5:M8, where A5 is 'North' A6 is 'East', A7 is 'South' and A8 is 'West'

 

[SkipVought]

Okay I see that this is a different issue.

If you named range is List1, then

MATCH("ABC",List1,0)>0

Will be true it "ABC" is in List1

But this does not make sense, as You'ld already know whether ABC were in List1 or List2.

So I don't understand what you're attemting to accomplish!

 

[LGMan]

Hi Skip, sorry I misunderstood.
Both B3 and B4 are the results of the Comboboxes. B3 is the level or Deparment within the company,and B4 is the Measure.

My problem with trying to sort out the correct green or red indicator against Target via CFs, has been futher complicated by 5 measures having individual targets for each of the 12 departments within the Company (which hadn't orginally been explained to me, or present in the test data that I was given) The other 7 measures have the same company wide Target.
So when the value of B3 is COM, the Measure (within B4) is shown against all 12 Deparments, so I'll need to compare their results to indivual targets. The Targets dont go any lower than Dept Level, so that Sub_Dept level essentially has it's parents Target.

Hope that's a bit clearer

 

[SkipVought]

Well then B3 or B4 would be the first argument in the MATCH() and the second argument would be the appropriate named range.

 

[LGMan]

Hi Skip, I'm stumpted, thanks for the help though

 

[SkipVought]

Please explain.

You have one expression to test a selected value in B3 and another expression to test a selected value in B4.

Each of these can be substituted with a MATCH()>0 expression, to match the selected value to the appropriate list.

Maybe you could explain what your lists are and what it is you're attemptin to achieve, rather than how you think it ought to be done.

 

[LGMan]

I think it will be easier to achieve the end result if I insert a new row above each of the existing 13 rows (12 depts plus the overall Company result) and pull in the appropriate target. I can then use the CF which you helped me with.
Hide those new rows and the end user still see thier desired report with correctly coloured cells

 

[SkipVought]

Good luck!