Help with #REF! Error

%3CLINGO-SUB%20id%3D%22lingo-sub-3254038%22%20slang%3D%22en-US%22%3EHelp%20with%20%23REF!%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3254038%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(15).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F354916i40853B1B404A2159%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%20(15).png%22%20alt%3D%22Screenshot%20(15).png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(16).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F354917i4A1297358A64B2A0%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%20(16).png%22%20alt%3D%22Screenshot%20(16).png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(14).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F354894iD66E4FC56811B86C%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%20(14).png%22%20alt%3D%22Screenshot%20(14).png%22%20%2F%3E%3C%2FSPAN%3EHi!%20I%20have%20a%20spreadsheet%20set%20up%20which%20talleys%20sales%20for%20each%20type%20of%20service%20sold%20each%20month%20(each%20month%20is%20an%20individual%20sheet)%26nbsp%3B%20and%20then%20it%20has%20a%20summary%20sheet%20which%20talleys%20the%20amount%20of%20each%20service%20sold%20per%20month%20and%20annually.%26nbsp%3B%20The%20sheet%20for%20March%20was%20accidentally%20deleted%20and%20I%20had%20to%20readd%20the%20month%20-%20I%20copy%20and%20pasted%20the%20set%20up%20of%20the%20sheet%20from%20another%20month.%26nbsp%3B%20Now%20the%20summary%20sheet%20is%20giving%20%23REF!%20errors%20for%20the%20entire%20March%20column%20as%20well%20as%20the%20total%20column.%26nbsp%3B%20Ive%20tried%20Find%20and%20Replace.%26nbsp%3B%20I%20cant%20seem%20to%20figure%20it%20out.%3C%2FP%3E%3CP%3EPlease%20Help%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3254038%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3255256%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20%23REF!%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3255256%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much%20for%20your%20help!%40%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3254304%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20%23REF!%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3254304%22%20slang%3D%22en-US%22%3EIn%20addition%20to%20that%20Table%20Name%20the%20formula%20in%20the%20Mar%20Column%20was%20using%20incorrect%20column%20names.%20Try%20this%3A%3CBR%20%2F%3E%3DSUMIFS(ExpMar%5BAnnual%20Amount%5D%2CExpMar%5BCategory%5D%2C%5B%40Category%5D)%3CBR%20%2F%3EI%20updated%20the%20file%20that%20was%20linked.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3254175%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20%23REF!%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3254175%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1329589%22%20target%3D%22_blank%22%3E%40Danelle13%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20table%20on%20the%20Mar%20sheet%20should%20be%20named%20ExpMar%2C%20but%20it%20is%20named%20ExpJan5.%3C%2FP%3E%0A%3CP%3EClick%20in%20any%20cell%20of%20that%20table%2C%20for%20example%20in%20A2%20on%20the%20Mar%20sheet.%3C%2FP%3E%0A%3CP%3EActivate%20the%20Table%20Design%20tab%20of%20the%20ribbon.%3C%2FP%3E%0A%3CP%3EChange%20the%20Table%20Name%20in%20the%20Properties%20group%20to%20ExpMar.%3C%2FP%3E%0A%3CP%3ESwitch%20to%20the%20Summary%20sheet.%3C%2FP%3E%0A%3CP%3ERemove%20the%20apostrophe%20from%20before%20the%20formula%20in%20D5.%20It%20should%20now%20return%20a%20value.%3C%2FP%3E%0A%3CP%3EDouble-click%20the%20fill%20handle%20in%20the%20lower%20right%20corner%20of%20D5%20to%20fill%20the%20formula%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3254147%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20%23REF!%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3254147%22%20slang%3D%22en-US%22%3E%3CP%3Eremoved%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3254114%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20%23REF!%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3254114%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3254109%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20%23REF!%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3254109%22%20slang%3D%22en-US%22%3EI%20tried%20that%20and%20it%20will%20not%20reference%20march.%20I%20cant%20seem%20to%20figure%20out%20why.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3254108%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20%23REF!%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3254108%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%20%26nbsp%3BHere%20is%20the%20Month%20of%20March%20as%20well%20as%20the%20summary%20sheet%20with%20the%20formula%20for%20Feb.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3254059%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20%23REF!%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3254059%22%20slang%3D%22en-US%22%3Ewithout%20the%20sheet%20it%20is%20hard%20but%20assuming%20it%20is%20all%20consistent%2C%20you%20should%20be%20able%20to%20Copy%20the%20formula%20in%20another%20month's%20column%20and%20then%20replace%20the%20sheet%20name%20in%20the%20formula%20with%20'Mar'.%20Note%20that%20when%20you%20copy%20the%20formula%2C%20if%20it%20wasn't%20done%20with%20absolute%20references%20(%24)%20then%20the%20columns%20may%20shift%2C%20but%20hopefully%20not.%20Otherwise%20please%20provide%20the%20sheet%20or%20at%20least%20a%20sample%20of%20the%20formula%20that%20goes%20there.%3C%2FLINGO-BODY%3E
Occasional Contributor

Screenshot (15).pngScreenshot (16).pngScreenshot (14).pngHi! I have a spreadsheet set up which talleys sales for each type of service sold each month (each month is an individual sheet)  and then it has a summary sheet which talleys the amount of each service sold per month and annually.  The sheet for March was accidentally deleted and I had to readd the month - I copy and pasted the set up of the sheet from another month.  Now the summary sheet is giving #REF! errors for the entire March column as well as the total column.  Ive tried Find and Replace.  I cant seem to figure it out.

Please Help? 

8 Replies
without the sheet it is hard but assuming it is all consistent, you should be able to Copy the formula in another month's column and then replace the sheet name in the formula with 'Mar'. Note that when you copy the formula, if it wasn't done with absolute references ($) then the columns may shift, but hopefully not. Otherwise please provide the sheet or at least a sample of the formula that goes there.

@mtarler   Here is the Month of March as well as the summary sheet with the formula for Feb.  

I tried that and it will not reference march. I cant seem to figure out why.

removed

 

@Danelle13 

The table on the Mar sheet should be named ExpMar, but it is named ExpJan5.

Click in any cell of that table, for example in A2 on the Mar sheet.

Activate the Table Design tab of the ribbon.

Change the Table Name in the Properties group to ExpMar.

Switch to the Summary sheet.

Remove the apostrophe from before the formula in D5. It should now return a value.

Double-click the fill handle in the lower right corner of D5 to fill the formula down.

In addition to that Table Name the formula in the Mar Column was using incorrect column names. Try this:
=SUMIFS(ExpMar[Annual Amount],ExpMar[Category],[@Category])
I updated the file that was linked.
Thank you so much for your help!@