Need help on conditional formatting and extracting data

[ashwithonline]

Hi,

Please find the attached workbook which has 2 sheets, i need help on both the sheets,

1.In the ‘Database’ sheet extract the name starting with ‘A’ from the database and show the extracted data in the other sheet.
2. In the second sheet highlight the complete data row whose department wise Basic is more than the given table range K1:L5. Highlight with ‘Light Green’ fill color

Regards,
Jagdish

 

[Khalid NGO]

Hi Jagdish,

1:
=IFERROR(INDEX(Database!$F$2:$F$999,AGGREGATE(15,6,1/((LEFT(Database!$F$2:$F$999)="A")/(ROW($A$2:$A$999)-1)),ROW(A1)),),"")

2: not clear to me.

Regards,

 

[ashwithonline]

Hi Khalid,

Thanks for the reply.

In the second sheet, I have to highlight the cell and row which has the basic higher than the one given in the table n cell L1. For example E2 has basic of 5600 for acct department but the basic for acct dept is 6500, so in this case its less so no issues but if it would have been higher it should be highlighted

 

[Khalid NGO]

Hi again,
Thanks for clarification.

Select your range A2:H11 > Go to Home > Conditional Formatting > Formula:

=$E2>VLOOKUP($D2,INDIRECT("Table3"),2,0)

or:
=$E2>SUMIF($L$2:$L$5,$D2,$M$2:$M$5)

Set Format and Press Ok

Regards,