RockandGrohl
Well-known Member
- Joined
- Aug 1, 2018
- Messages
- 810
- Office Version
- 365
- Platform
- Windows
Hello, let's say I'm doing a COUNTIF across two sheets.
The first sheet is Dashboard, the second is data. The formula is being entered on "Dashboard"
When I am flicking between the two sheets as I fill in the range and criteria, I end up with something like:
[criteria_range1] [criteria1]
Data!F:F Dashboard!$C$4
F:F are month's in numerical format and C4 is a date, like... Feb-2017
The problem comes when you want to do a nested formula, this breaks the syntax and gives an error until you manually clear the "Dashboard!" part of the syntax. As an example, you would flick back to your Dashboard to fill in the criteria, the formula auto-fills the "Dashboard" and you end up with "Dashboard!Month($C$4)"
Unfortunately, this gives an error, and out of annoyance, you can't use tab to auto-complete formula syntax.
The only solution is to get your whole formula out, then laboriously manually delete every reference to the origin sheet that the formula sits on.
I don't recall encountering this in Excel 2013... am I stuck with this or is there a way to fix it? Would save us a lot of headache. Thanks!
The first sheet is Dashboard, the second is data. The formula is being entered on "Dashboard"
When I am flicking between the two sheets as I fill in the range and criteria, I end up with something like:
[criteria_range1] [criteria1]
Data!F:F Dashboard!$C$4
F:F are month's in numerical format and C4 is a date, like... Feb-2017
The problem comes when you want to do a nested formula, this breaks the syntax and gives an error until you manually clear the "Dashboard!" part of the syntax. As an example, you would flick back to your Dashboard to fill in the criteria, the formula auto-fills the "Dashboard" and you end up with "Dashboard!Month($C$4)"
Unfortunately, this gives an error, and out of annoyance, you can't use tab to auto-complete formula syntax.
The only solution is to get your whole formula out, then laboriously manually delete every reference to the origin sheet that the formula sits on.
I don't recall encountering this in Excel 2013... am I stuck with this or is there a way to fix it? Would save us a lot of headache. Thanks!
