SOLVED
Home

Counta/CountIF autopopulate range from different sheet, where the sheet name may change

%3CLINGO-SUB%20id%3D%22lingo-sub-784220%22%20slang%3D%22en-US%22%3ECounta%2FCountIF%20autopopulate%20range%20from%20different%20sheet%2C%20where%20the%20sheet%20name%20may%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-784220%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20know%20if%20this%20is%20possible%2C%20I%20have%20a%20very%20unique%20situation.%20We%20use%20Excel%20for%20our%20test%20cases%2C%20usually%20multiple%20sheets%20in%20one%20file.%20The%20main%20sheet%20is%20a%20summary%20results%20page%2C%20where%20we%20summarize%20how%20many%20tests%20have%20been%20executed%2C%20how%20many%20passed%2C%20failed%20etc.%20To%20do%20this%20we%20utilize%20the%20COUNTA%20and%20CountIF%20formulas%20and%20reference%20the%20other%20sheets.%26nbsp%3B%3C%2FP%3E%3CP%3Efor%20example%3C%2FP%3E%3CP%3E%3DCOUNTA(Sheet2!A2%3AA25)%3C%2FP%3E%3CP%3E%3DCOUNTIF(Sheet2!M2%3AM300%2C%22P%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOur%20issue%20is%2C%20the%20name%20for%20the%20sheets%20change%20by%20project%2C%20and%20it%20takes%20a%20long%20time%20to%20change%20all%20the%20sheet%20names%20in%20all%20the%20formulas%20in%20this%20page.%20Especially%20when%20we%20have%2030%20sheets.%20In%20the%20summary%20sheet%20we%20have%20a%20column%20with%20all%20of%20the%20sheet%20names%2C%20is%20there%20a%20way%20to%20autofill%20the%20sheet%20name%20from%20the%20previous%20column%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIncluding%20a%20screenshot%20to%20help%20visualize%20what%20I%20am%20trying%20to%20explain.%20Each%20cell%20in%20columns%20b%2C%20c%2C%20e%2C%20f%2C%20and%20g%20have%20a%20formula%20referencing%20another%20sheet.%26nbsp%3BIs%20there%20a%20way%20to%20take%20the%20sheet2%20name%20from%20the%20previous%20column%20and%20make%20it%20so%20that%20it%20has%20the%20!%20to%20reference%20the%20other%20sheet%20in%20the%20formulas%2C%20automatically%20without%20having%20to%20fill%20it%20in%20manually%20each%20time%20a%20sheet%20name%20is%20changed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20and%20all%20help%20is%20much%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-784220%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-784413%22%20slang%3D%22en-US%22%3ERe%3A%20Counta%2FCountIF%20autopopulate%20range%20from%20different%20sheet%2C%20where%20the%20sheet%20name%20may%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-784413%22%20slang%3D%22en-US%22%3ETry%20this%20formula%20in%20G2%2C%20and%20copy%20down%20rows%3A%3CBR%20%2F%3E%3DCOUNTIF(INDIRECT(A2%26amp%3B%E2%80%9D!M1%3AM300%E2%80%9D)%2C%3CBR%20%2F%3E%E2%80%9CN%2FA%E2%80%9D)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-784616%22%20slang%3D%22en-US%22%3ERe%3A%20Counta%2FCountIF%20autopopulate%20range%20from%20different%20sheet%2C%20where%20the%20sheet%20name%20may%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-784616%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20worked!!%20For%20some%20reason%20it%20didn't%20like%20the%20formatting%20from%20here%2C%20but%20when%20I%20typed%20it%20into%20my%20formula%20your%20recipe%20worked!%20thanks%20so%20much!!%3C%2FP%3E%3CDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-785061%22%20slang%3D%22en-US%22%3ERe%3A%20Counta%2FCountIF%20autopopulate%20range%20from%20different%20sheet%2C%20where%20the%20sheet%20name%20may%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-785061%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385826%22%20target%3D%22_blank%22%3E%40Renee_Gaudet%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20very%20much%20welcome!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Renee_Gaudet
New Contributor

Hello, 

I don't know if this is possible, I have a very unique situation. We use Excel for our test cases, usually multiple sheets in one file. The main sheet is a summary results page, where we summarize how many tests have been executed, how many passed, failed etc. To do this we utilize the COUNTA and CountIF formulas and reference the other sheets. 

for example

=COUNTA(Sheet2!A2:A25)

=COUNTIF(Sheet2!M2:M300,"P")

 

Our issue is, the name for the sheets change by project, and it takes a long time to change all the sheet names in all the formulas in this page. Especially when we have 30 sheets. In the summary sheet we have a column with all of the sheet names, is there a way to autofill the sheet name from the previous column?

 

Including a screenshot to help visualize what I am trying to explain. Each cell in columns b, c, e, f, and g have a formula referencing another sheet. Is there a way to take the sheet2 name from the previous column and make it so that it has the ! to reference the other sheet in the formulas, automatically without having to fill it in manually each time a sheet name is changed.

 

Any and all help is much appreciated!

 

 

3 Replies
Solution
Try this formula in G2, and copy down rows:
=COUNTIF(INDIRECT(A2&”!M1:M300”),
“N/A”)
Highlighted

@Twifoo 

That worked!! For some reason it didn't like the formatting from here, but when I typed it into my formula your recipe worked! thanks so much!!

 
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Teams Calling - Dial pad missing
Chris Cooper in Microsoft Teams on
73 Replies