agwalsh
asked on
Changing a formula to reference a cell instead of a static pivot table reference
I've got this formula set up
=SUMIFS(D:D,A:A,$N17)/GETP
How do I reference the [Dave] part of it so that it references a cell I can copy down rather than me having to type in the name each time. Thanks
=SUMIFS(D:D,A:A,$N17)/GETP
How do I reference the [Dave] part of it so that it references a cell I can copy down rather than me having to type in the name each time. Thanks
ASKER
That doesn't work. I had tried that already. All I want to know is what do I put into the formula instead of "[Range].[Employee].&[Dave
Please post a sample workbook, let me have a look.
ASKER
Here we go
EE_Pivot_distinct_count_reference.xlsx
EE_Pivot_distinct_count_reference.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ah, excellent. Thank you. One last question :-) I reproduced the formula and it worked fine. However I found I had to add the array element individually to each entry for the week number - what would be the easier way to do that? Thanks :-)
I think my solution is bit easier than the way you were handling. :)
As you are handling individual Employees, you need to have Week No's separately as per Employees.
As you are handling individual Employees, you need to have Week No's separately as per Employees.
ASKER
Don't disagree...but still do I have to enter each array formula individually? or is there a way to copy them down?
You enter just once, then drag down until you have Employee Names in Column A.
ASKER
Yes, tried again and it did work. Not sure what I was doing that it didn't work but it's working now! Thank you :-)
ASKER
Thrilled with this solution - really helped. Thank you so much
You're welcome! Please to help
Assuming you are referring A1 cell, change as needed, if yes then try below;
=SUMIFS(D:D,A:A,$N17)/GETP