Can you shorten my work flow please

[Beau the dog]

Good morning all,

My explanation is going to sound way more complicated than it is I think! Just trying to give you an overview...

I am creating an audit spreadsheet. The audit has 10 tabs (titled section 1 - section 10) and a summary page, each tab relates to a different category.

Within each tab are questions (obviously!) these are divided into further categories within the main category. The biggest sub section maybe around 10 questions, answers in column D, but not next each other.

Each question will have one of 4 answers: "Standards met", "standards part met", "standards fully met" or "not applicable". The answers will always be in column D, but not next to each other.

My summary page will link to the sections and return symbols based on a hierarchy. If ANY of the questions in the sub section have "not met", than it is not met (represented by a symbol, using wingdings2), then working down to fully met, . If not applicable, this is the same as standards met.

I have this formula that works

=IF(OR(ISNUMBER(SEARCH("not met", D5)), ISNUMBER(SEARCH("not met", D8)), ISNUMBER(SEARCH("not met", D11))), CHAR(86),
IF(OR(ISNUMBER(SEARCH("part", D5)), ISNUMBER(SEARCH("part", D8)), ISNUMBER(SEARCH("part", D11))), CHAR(88),
IF(OR(ISNUMBER(SEARCH("fully", D5)), ISNUMBER(SEARCH("fully", D8)), ISNUMBER(SEARCH("fully", D11))),
CHAR(80),
IF(OR(ISNUMBER(SEARCH("applicable", D5)), ISNUMBER(SEARCH("applicable", D8)), ISNUMBER(SEARCH("applicable", D11))),
CHAR(80),
CHAR(182)))))

So my ask of you fine freaky excel gods is this: I have to input this formula into 72 cells on the summary page, linking to different tabs and sections, this is going to take ages! IS THEIR A BETTER WAY?!!!

The example formula above is obviously not referencing a separate tab, I did this to shorten the formula on this thread.

 

[Skybluekid]

Are all the cells next to each other? Also, have you thought about the IFS funcion, which will make the formula more readable?

 

[Beau the dog]

Are all the cells next to each other? Also, have you thought about the IFS funcion, which will make the formula more readable?

Unfortunately the audit workbook doesn't have all the cells together, to help with selecting a range...can't change that I'm afraid. The IFS formula would be better, I just thought there would be something to revolutionize my mind!!

 

[Skybluekid]

On what frequency does this need to be done? Do the cell references change with each placement?

What I am thinking is an Addin that contains a macro that will place the formula into each cell