New project that is completely out of my scope, help me understand!

nexus

New Member
I have been working at my new finance job for little over a week but the work load has been getting very tough. Especially this one project that is giving me a hard time understanding so I am hoping that someone here can help me understand it better.

My boss made a P&L (profit and loss) excel spreadsheet for FY13 and she wants me to simplify it for FY14 with various different drop down menus added. Now before I get into that, I still need to understand the big picture. The Excel sheet has two "master" sheets titled "Pivot_Master" and "Pivot_FuncDetail". I assume that the information is gathered from those sheets but why is there two?

In addition, there are about 10 different separate slides with headcounts and expenses (operating expensive and cost of goods sold). They all have different expense reports for the different sectors of the business.

I understand the basics of Excel. I understand how Power Pivot and Macros work, along with the formulas. However, I am having a hard time seeing where the data is pulled from. For example, in one of the expense sheets under headcount, this is the formula used for the Research and Development cell:

=IFERROR(GETPIVOTDATA(+\$H\$9,INDIRECT(\$H\$11),"Fiscal Month",\$E\$6,"Line Item",\$D14,"Profit Center SEC Func Area2", \$C14), 0)

Now can someone explain this to me. Where is the Excel sheet pulling the data from? Is it Pivot_Master or Pivot_FuncDetail? Both Pivot sheets go down to about 700 rows with numbers and data and I am having a hard time seeing where they are pulling the information from.

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

nexus

New Member
Have a look at the second argument in the GETPIVOTDATA function, hence cell H11.
GETPIVOTDATA - Excel - Office.com

Thank you for that link. However, I looked at cell H11... it is a blank cell. I don't even understand which Pivot Table it is pulling the data from... Is it Pivot_Master or Pivot_FuncDetail? What is the point of having two pivot sheets?

wigi

Well-known Member
Hello

You will need to provide an example file with fake data and information, but for the rest the same as your file.
For example, through some filesharing website. Then I will have a look at it.

nexus

New Member
Hello

You will need to provide an example file with fake data and information, but for the rest the same as your file.
For example, through some filesharing website. Then I will have a look at it.

Unfortunately, there is too much data for me to insert fake data. However, I figured out where the data was being pulled from. Some of the columns and rows were hidden so I had to unhide it to view it. I have a few more specific questions now.

The formula at the ‘Incentive Compensation’ column and row ‘Actual’ for the Expense Sheet is:

=IFERROR(GETPIVOTDATA(1:1+\$H\$2,INDIRECT(\$J\$11),"Profit Center SEC Func Area2",\$A22,"Class", \$B22,"Fiscal Month",\$E\$6,"Exec Function Summary",\$L\$1,"Sub Class",\$D22,"Group",\$E22,"Line Item",\$C22), 0)

1. The data field for the GETPIVOTDATA is 1:1+\$H\$2. What does that mean and what is the point of 1:1? H2 is a cell with the word “Actual” in it with no formula. In that case, is there even a point to do \$H\$2 instead of just H2?

2. Explain =IFERROR, so if the value is 0, it’s an error?

3. Why the need for an INDIRECT(\$J\$11) function? J11 has the following formula ‘MercPivot_Master!\$B\$20’ – Why the need for \$J\$11 and not just J11? Seems like it is just pulling the data from J11 anyway?

4. Why is there an exclamation point between MercPivot_Master and \$B\$20?

Thank you.

Last edited:

wigi

Well-known Member
Hello

Sorry to say, but most of these questions are basic and you will want to frist get that straight before you delve into (difficult) formulas and pivot tables.

For example,

2. Explain =IFERROR, so if the value is 0, it’s an error?

This says Excel to have 0 as the formula result in case the first part of the formula results in an error.
This information can be obtained in the help files.

Or:

3. Why the need for an INDIRECT(\$J\$11) function? J11 has the following formula ‘MercPivot_Master!\$B\$20’ – Why the need for \$J\$11 and not just J11? Seems like it is just pulling the data from J11 anyway?

4. Why is there an exclamation point between MercPivot_Master and \$B\$20?

\$ signs keep the cell references the same when you drag down or accross or you copy the formula.

Replies
7
Views
638
Replies
2
Views
5K
Replies
19
Views
1K
Replies
3
Views
753
Replies
7
Views
2K

1,195,594
Messages
6,010,625
Members
441,558
Latest member
lambierules

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back