Good morning,
I have a report that is automatically refreshed everyday from my SalesForce addin to excel 2007. I have a pivot table that automatically refreshes based on the data in the master sheet (sf document). I also have another sheet that summarizes the information based on Managers that gives the total amount of open accounts and line items they have from the master data. I would like to hyperlink the name on the summary sheet to the master sheet so that you can select the manager on the summary page and it will pull up only their rows of data on the master sheet. My thoughts are to find a way to name the row range based on what name is in column A (manager) and to hyperlink to the named rows.
I also have another question on this project. I have to get a count of distinct accounts from the master sheet for each manager. One account can have many line items, so a countifs will only give me the # of line items. I have a workaround right now to add a column to the changing sheet (master data from SF report), but I afraid it will not update correctly when the data updates. Is there a formula that I can put in my summary sheet that correctly gives me the count of distinct account numbers based on manager?
My current formulas are:
//on the master data sheet -- added extra column (HATE)
=IF(SUMPRODUCT(($A$2:$A2=A2)*($E$2:$E2=E2))>1,0,1)
//on the summry sheet to count the totals from the above formula
=SUMIFS('Employee Open Items - SF'!$L:$L,'Employee Open Items - SF'!$A:$A,Main!$A4)
I know these are two different issues, but any help is very appreciated. I know formulas but very limited on vba. I don't mind using vba if I can here, but I am an accountant -- fair warning.
I have a report that is automatically refreshed everyday from my SalesForce addin to excel 2007. I have a pivot table that automatically refreshes based on the data in the master sheet (sf document). I also have another sheet that summarizes the information based on Managers that gives the total amount of open accounts and line items they have from the master data. I would like to hyperlink the name on the summary sheet to the master sheet so that you can select the manager on the summary page and it will pull up only their rows of data on the master sheet. My thoughts are to find a way to name the row range based on what name is in column A (manager) and to hyperlink to the named rows.
I also have another question on this project. I have to get a count of distinct accounts from the master sheet for each manager. One account can have many line items, so a countifs will only give me the # of line items. I have a workaround right now to add a column to the changing sheet (master data from SF report), but I afraid it will not update correctly when the data updates. Is there a formula that I can put in my summary sheet that correctly gives me the count of distinct account numbers based on manager?
My current formulas are:
//on the master data sheet -- added extra column (HATE)
=IF(SUMPRODUCT(($A$2:$A2=A2)*($E$2:$E2=E2))>1,0,1)
//on the summry sheet to count the totals from the above formula
=SUMIFS('Employee Open Items - SF'!$L:$L,'Employee Open Items - SF'!$A:$A,Main!$A4)
I know these are two different issues, but any help is very appreciated. I know formulas but very limited on vba. I don't mind using vba if I can here, but I am an accountant -- fair warning.
