Nested IF function?

[jimonfly]

I am trying to hide rows in a sheet once the end of data is reached. I have code already setup to hide the row if Column A contains "H". The end of data is marked by the data in Column E being "Percent of Total Expiring". In column A I have the following formula so if the Column E data is blank or text, it returns "S" for show the row. When the logical_test is met it returns "X" to signify the end of data.

=IF(E19="Percent Of Total Expiring","X","S")

(E19 just happens to contain "Percent Of Total Expiring" on the table I am working with)

I want to amend this formula so if the cell above (in Column A) contains "X" or "H", then the cell value will be "H". This should result in Column A containing "S" above the logical_test, "X" for the logical_test, and "H" for everything below. The VBA code will then hide all the "H" rows and I will have a nice looking table.

Can someone help, please?

Jim

 

[ukmikeb]

Jim

Try -

=IF(E19="Percent Of Total Expiring","X",IF(OR(OFFSET(A19,-1,,1,1)="H",OFFSET(A19,-1,,1,1)="X"),"H","S"))

hth

 

[jimonfly]

Works perfectly. Thanks.

 

[ukmikeb]

Jim

Pleased to have helped solve your problem.

Thanks for the feedback.