Help on resolving "Excel found a problem with one or more formula references in this worksheet"

%3CLINGO-SUB%20id%3D%22lingo-sub-1591470%22%20slang%3D%22en-US%22%3EHelp%20on%20resolving%20%22Excel%20found%20a%20problem%20with%20one%20or%20more%20formula%20references%20in%20this%20worksheet%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1591470%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20create%20a%20line%20chart%20that%20changes%20dynamically%20when%20I%20select%20months%20in%20the%20slicer%20for%20month.%20This%20line%20chart%20is%20part%20of%20the%20dashboard%20that%20I'm%20creating%20and%20it%20has%20many%20calculations.%20I%20encounter%20error%20whenever%20I%20click%20one%20month%20in%20the%20slicer%20but%20ironically%2C%20I%20don't%20encounter%20any%20issue%20when%20selecting%20multiple%20months%20in%20the%20slicer.%20I'm%20attaching%20the%20scaled-down%20version%20of%20the%20Excel%20dashboard%20in%20the%20post.%20See%20screenshot%20below%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202020-08-16%20at%208.43.49%20PM.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F212654i941D5E01C47EA429%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Screen%20Shot%202020-08-16%20at%208.43.49%20PM.png%22%20alt%3D%22Screen%20Shot%202020-08-16%20at%208.43.49%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAgain%2C%20the%20line%20chart%20is%20part%20of%20the%20dashboard%20that%20I'm%20building%20and%20the%20end%20goal%20looks%20like%20in%20below%20screenshot%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202020-08-16%20at%2010.19.15%20AM.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F212655i09426B0CC42A8C9C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Screen%20Shot%202020-08-16%20at%2010.19.15%20AM.png%22%20alt%3D%22Screen%20Shot%202020-08-16%20at%2010.19.15%20AM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20am%20using%20Excel%202016%20for%20Mac%20and%20I%20used%20named%20range%20in%20the%20line%20chart%20so%20it%20can%20change%20dynamically%20with%20option%20to%20change%20the%20start%20and%20end%20date.%20I%20looked%20into%20the%20code%20and%20there%20doesn't%20seem%20to%20be%20anything%20wrong%20e.g.%20index%2C%20match%2C%20etc.%20I'm%20not%20sure%20why%20this%20annoying%20message%20keeps%20popping%20up%20and%20so%20appreciate%20if%20anyone%20can%20help.%20Perhaps%2C%20there%20is%20some%20simple%20solution%20that%20eliminates%20the%20error%20(something%20I%20might've%20overlooked%20as%20I've%20kept%20on%20working%20on%20this%20for%20so%20long)%20but%20of%20course%2C%20retaining%20all%20the%20intended%20functionality%20of%20the%20dashboard.%20Otherwise%2C%20let%20me%20know%20too%20if%20there%20are%20Excel%20limitations%20that%20I%20didn't%20know.%20I%20was%20also%20hoping%20to%20suppress%20the%20error%20popup%20message%20if%20it's%20possible%20because%20logically%2C%20nothing's%20wrong%20with%20the%20line%20chart.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20makes%20sense.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWashington%26nbsp%3B%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1591470%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1595028%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20on%20resolving%20%22Excel%20found%20a%20problem%20with%20one%20or%20more%20formula%20references%20in%20this%20worksh%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1595028%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F760986%22%20target%3D%22_blank%22%3E%40washingtonalto%3C%2FA%3E%26nbsp%3B%2C%20just%20an%20update%20that%20I%20was%20able%20to%20find%20a%20simple%20workaround%20for%20this%20issue%20by%20breaking%20the%20connection%20between%20the%20month%20in%20the%20slicer%20with%20the%20pivot%20table.%20See%20attached%20below.%20The%20issue%20is%20now%20closed.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20kept%20on%20thinking%20of%20the%20solution%20for%20days%20but%20then%20the%20solution%20came%20when%20I%20think%20out%20of%20the%20box.%20This%20helped%20me%20resolved%20the%20issue%20I%20raised.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi,

 

I'm trying to create a line chart that changes dynamically when I select months in the slicer for month. This line chart is part of the dashboard that I'm creating and it has many calculations. I encounter error whenever I click one month in the slicer but ironically, I don't encounter any issue when selecting multiple months in the slicer. I'm attaching the scaled-down version of the Excel dashboard in the post. See screenshot below:

Screen Shot 2020-08-16 at 8.43.49 PM.png

Again, the line chart is part of the dashboard that I'm building and the end goal looks like in below screenshot:

Screen Shot 2020-08-16 at 10.19.15 AM.png

I am using Excel 2016 for Mac and I used named range in the line chart so it can change dynamically with option to change the start and end date. I looked into the code and there doesn't seem to be anything wrong e.g. index, match, etc. I'm not sure why this annoying message keeps popping up and so appreciate if anyone can help. Perhaps, there is some simple solution that eliminates the error (something I might've overlooked as I've kept on working on this for so long) but of course, retaining all the intended functionality of the dashboard. Otherwise, let me know too if there are Excel limitations that I didn't know. I was also hoping to suppress the error popup message if it's possible because logically, nothing's wrong with the line chart. 

 

Hope this makes sense.

 

Thank you in advance.

 

Washington 

1 Reply

@washingtonalto , just an update that I was able to find a simple workaround for this issue by breaking the connection between the month in the slicer with the pivot table. See attached below. The issue is now closed. 

 

I kept on thinking of the solution for days but then the solution came when I think out of the box. This helped me resolved the issue I raised.