Forum Discussion
The Cell Currently Being Evaluated Contains a Constant | #REF! Error
I am attempting to make a timesheet (Timesheet Breakdown tab) that auto-fills data from the "Time" tab. via a pivot table that is located in the "Backend" tab. I'm copying the concept from a co-worker that had built a very similar setup in the past, although I am receiving a #REF! error when trying to fill in hours using: =GetPivotData("Hours", Backend!E2, "Date", C9, "District", D6, "Category", D8)
I looked all over, and the only suggestions I could find were to check the "General" option for formatting, and or make sure that the commas are placed correctly. All appears to be in order to me, so hopefully one of you can help sort out my issue! I attached the document in question to this post as well.
Please let me know if there are any further questions, and I will try my best to answer them. I'm not super fluent with Excel, and I'm sure the work shows!
~Seth
What's the advantage of building sheet Timesheet Breakdown? It is the same structure as the pivot table.
Your mistakes in Timesheet Breakdown:
You have to repeat the headers in row 6 so that the formula can pick up the correct value when copying to the right.
You have to convert the date in exactly the same format as in the pivot table: TEXT($C9;"DD. MMM")
You have to use exactly the same headers in row 8 as in the pivot table: Vacation becomes Vacation Leave and Sick becomes Sick Leave.
Personal does not appear in the pivot table. Either it is a mistake or you have to wrap the function in IFERROR().
3 Replies
What's the advantage of building sheet Timesheet Breakdown? It is the same structure as the pivot table.
Your mistakes in Timesheet Breakdown:
You have to repeat the headers in row 6 so that the formula can pick up the correct value when copying to the right.
You have to convert the date in exactly the same format as in the pivot table: TEXT($C9;"DD. MMM")
You have to use exactly the same headers in row 8 as in the pivot table: Vacation becomes Vacation Leave and Sick becomes Sick Leave.
Personal does not appear in the pivot table. Either it is a mistake or you have to wrap the function in IFERROR().
- I'm not very good at trimming the pivot table to show only what I want, so I guess that is my main issue, and the reason why I'm wanting to make a secondary table. I'll work on this, and see if I can resolve the issue!
To show only certain parts of the data you can filter the pivot table or insert slicers and timeline for filtering.
