Subtotal + Conditional Format Question

[Martin Ho]

Hi Gurus,

I have 2 input tables like follows:

(Table A)
ID WK1 WK2
Mr. A 0.2 0.0
Mr. A 0.3 0.0
Mr. A 0.0 0.2
Mr. A 0.0 0.6

Ms. B 0.1 0.2
Ms. B 0.1 0.3
Ms. B 0.1
Ms. B 0.1 0.2
Ms. B 0.7 0.0

Mr. C 0.2 0.3
Mr. C 0.2 0.4
Mr. C
Mr. C 0.2 0.0
Mr. C 0.1 0.0

(Table B)
ID LIMIT
Mr. A 0.4
Ms. B 0.8
Mr. C 0.9

I want to create a table as below:

LIMIT ID WK1 WK2
0.4 Mr. A Total _0.5_ _0.8_
0.7 Ms. B Total _1.0_ 0.7
0.9 Mr. C Total 0.7 0.7

I.e. if Wk1 or Wk2 subtotal in Table A is greater than the limit
specified in Table B, the subtotal sum will be conditionally bolded
(here represented as underscored).

Although the subtotal comes in handy to get the subtotal table, I
don't know how to create a program which accomodates the constantly
changing contents in Tables A and B.

As you can tell from the question I asked, I am newbie in Excel
programming and I appreciate any help from the community.

Thank you very much in advance.

Martin Ho

 

[Kieran]

The subtotals can be provided by the SUMIF function, or the SUMPRODUCT
function.

I think that SUMIF should be adequate for the task, SUMPRODUCT is a
little slower in calculation sometimes.

Please look the function up in help and let the forum know if you need
further help.

Regarding the automatic bolding, the FORMAT.CONDITIONAL FORMATTING tool
can accomplish this. Form your post it is not clear if you are already
aware of this. Please post back if you need further clarification.