SOLVED
Home

Dynamic Named Range Failure

%3CLINGO-SUB%20id%3D%22lingo-sub-489570%22%20slang%3D%22en-US%22%3EDynamic%20Named%20Range%20Failure%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-489570%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20created%20a%20workbook%20that%20will%20be%20used%20as%20a%20checkbook%2C%20made%20up%20of%20a%20tab%20for%20each%20month.%26nbsp%3B%20There%20are%20also%20categories%20for%20register%20entry.%26nbsp%3B%20There%20is%20also%20a%20summary%20tab%20that%20will%20pull%20info%20from%20each%20sheet%20into%20an%20income%2Fexpense%20statement.%26nbsp%3B%20That%20was%20built%20with%20a%20named%20range%20that%20included%20the%20tab%20names%20(Jan%20thru%20Feb)%2C%20and%20the%20following%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMPRODUCT(SUMIF(INDIRECT(%22'%22%26amp%3BMonths%26amp%3B%22'!d9%3Ad101%22)%2C%24D22%2CINDIRECT(%22'%22%26amp%3BMonths%26amp%3B%22'!f9%3Af101%22)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20formula%20works%20AWESOME%2C%20however%2C%20because%26nbsp%3Bthe%20tabs%20are%20pre-populated%20with%20regular%20monthly%20expenditures%2C%20I%20wanted%20to%20find%20a%20way%20to%20automatically%20update%20the%20named%20range%20as%20each%20month%20passed.%26nbsp%3B%20that%20led%20me%20to%20a%20formula%20to%20create%20a%20Dynamic%20Named%20Range%20using%20this%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DOFFSET(SUMMARY!%24A7%2C1%2C0%2CCOUNTA(SUMMARY!%24A%3A%24A)-1%2C1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20where%20I%20get%20a%20%23REF!%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHELP!%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-489570%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-489658%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20Named%20Range%20Failure%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-489658%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F328864%22%20target%3D%22_blank%22%3E%40Trisa58%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20it%20works%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20555px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F110761iC41A2FE0908133F0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EMost%20probably%20you%20need%20to%20change%20second%20parameter%20in%20OFFSET%20on%20zero%20and%20don't%20deduct%201%20from%20COUNTA.%20Last%20parameter%20is%20not%20required%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-489667%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20Named%20Range%20Failure%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-489667%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20I%20upload%202%20files%20and%20have%20you%20look%20at%20them%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-489670%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20Named%20Range%20Failure%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-489670%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F328864%22%20target%3D%22_blank%22%3E%40Trisa58%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20sure.%20Only%20bit%20later%2C%20I'll%20have%20a%20break%20right%20now.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-489688%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20Named%20Range%20Failure%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-489688%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20will%20upload%2C%20you%20take%20your%20time.%26nbsp%3B%20I%20really%20appreciate%20any%20and%20all%20help%20you%20can%20give.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-489811%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20Named%20Range%20Failure%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-489811%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F328864%22%20target%3D%22_blank%22%3E%40Trisa58%3C%2FA%3E%26nbsp%3B%2C%20no%20problem.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20test%20file%20in%20the%20formula%20you%20started%20range%20from%20A8%2C%20actually%20it's%20from%20A1.%20In%20main%20file%20you%20have%20the%20cell%20with%20empty%20string%20below%20your%20months%2C%20thus%20COUNTA%20returned%20incorrect%20result.%20Since%20we%20have%20only%20twelve%20months%20in%20the%20year%20I'd%20recommend%20to%20limit%20the%20range%20accordingly%2C%20not%20use%20A%3AA.%20Finally%20the%20formula%20is%3C%2FP%3E%0A%3CPRE%3E%3DOFFSET(SUMMARY!%24A%242%2C0%2C0%2CCOUNTA(SUMMARY!%24A%241%3A%24A%2413)-1%2C1)%3C%2FPRE%3E%0A%3CP%3EI%20corrected%20in%20both%20files.%20Please%20be%20carefull%20with%20relative%20and%20absolute%20references%2C%20here%20all%20are%20to%20be%20absolute.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-489821%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20Named%20Range%20Failure%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-489821%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%20I%20will%20check%20these%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%2C%20I%20greatly%20appreciate%20all%20your%20help.%26nbsp%3B%20I've%20worked%20with%20Excel%20for%20years%2C%20but%20never%20any%20formal%20training%20on%20formulas.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-489833%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20Named%20Range%20Failure%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-489833%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F328864%22%20target%3D%22_blank%22%3E%40Trisa58%3C%2FA%3E%26nbsp%3B%2C%20me%20too%2C%20never%20had%20any%20formal%20training.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20inform%20if%20something%20doesn't%20work%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-489885%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20Named%20Range%20Failure%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-489885%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPERFECT!!!!%26nbsp%3B%20Thank%20you!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Trisa58
Occasional Contributor

I've created a workbook that will be used as a checkbook, made up of a tab for each month.  There are also categories for register entry.  There is also a summary tab that will pull info from each sheet into an income/expense statement.  That was built with a named range that included the tab names (Jan thru Feb), and the following formula:

 

=SUMPRODUCT(SUMIF(INDIRECT("'"&Months&"'!d9:d101"),$D22,INDIRECT("'"&Months&"'!f9:f101")))

 

This formula works AWESOME, however, because the tabs are pre-populated with regular monthly expenditures, I wanted to find a way to automatically update the named range as each month passed.  that led me to a formula to create a Dynamic Named Range using this formula:

 

=OFFSET(SUMMARY!$A7,1,0,COUNTA(SUMMARY!$A:$A)-1,1)

 

This is where I get a #REF! error.

 

HELP!?

 

8 Replies

@Trisa58 ,

 

In general it works

image.png

Most probably you need to change second parameter in OFFSET on zero and don't deduct 1 from COUNTA. Last parameter is not required here.

@Sergei Baklan 

 

Could I upload 2 files and have you look at them?

@Trisa58 ,

 

Yes, sure. Only bit later, I'll have a break right now.

@Sergei Baklan 

I will upload, you take your time.  I really appreciate any and all help you can give.

Solution

@Trisa58 , no problem.

 

In test file in the formula you started range from A8, actually it's from A1. In main file you have the cell with empty string below your months, thus COUNTA returned incorrect result. Since we have only twelve months in the year I'd recommend to limit the range accordingly, not use A:A. Finally the formula is

=OFFSET(SUMMARY!$A$2,0,0,COUNTA(SUMMARY!$A$1:$A$13)-1,1)

I corrected in both files. Please be carefull with relative and absolute references, here all are to be absolute.

@Sergei Baklan 

 

Thank you, I will check these out.

 

Again, I greatly appreciate all your help.  I've worked with Excel for years, but never any formal training on formulas.

 

 

@Trisa58 , me too, never had any formal training.

 

Please inform if something doesn't work

@Sergei Baklan

 

PERFECT!!!!  Thank you! 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies