Look up formulas

[matrixpom]

Hey Guys,

I have a spread sheet that tracks a build status as a %, at certain stages throughout the build there are numerous certificates that need to be completed e.g. certificate 1 needs to be chased at 10%, certificate 2 at 20% & so on. As there are hundreds of builds at any one time, I need a "at a glance" chart that tells me which certs I need to chase & to which build they relate to. The workbook attached explains further, as always guys, any assistance would be greatly appreciated, thanks again & have a great day - Marc

[FlameRetired]

Edited Overlooked detail in previous forumula.

Try this in O8 filled down and across column X until you get all blanks.

Please Login or Register  to view this content.

[matrixpom]

Hi FlameRetired, your code is working but not exactly as I had planned, firstly I need the code to show builds that have the words "Applied For" or "Required" in the respective column, once the user marks the cell as "Received" it should disappear. Secondly I need the code to work on the required percentage & anything above that percentage e.g. Cert 1 needs to be chased if the building is 10% or more & the cell does NOT state "Received". Sorry for the confusion...hopefully this clears things up? Thanks again FlameRetired - Marco

[matrixpom]

I've worked a basic formula out that returns the results I need

Please Login or Register  to view this content.

, but this code will need adapting to every cell...don't fancy doing this over multiple columns & thousands of rows & it will leave gaps in results where there is no result I've attached the updated spreadsheet as an example ok, hope this helps? - Marco

[FlameRetired]

It appears all that is needed is attention to absolute addressing details.

Try this in O5 filled down and across.

=IF(AND($L2 > =O$2,OR(B2="Applied For",B2="Required")),$A2,"")

[matrixpom]

You Sir...are a genius, I'm embarrassed to say I've never really fully understood the use of absolute until now. Thank you FlameRetired!! Marco