In the screen capture you see below, I am using the formula
=GETPIVOTDATA("Referral Number",RefStart!$A$3,"Ref Start", ROW(A4),"Years",2018)
in cell P2 where Apr-18 shows 38 referrals. My questions:
1) I had to manually type "Row (A4)" to pull in April data, "Row (A3)" to pull in March data, etc. Is there a command that will allow me to copy the formula and return the correct month's Referral Starts from that tab?
2) In order to incorporate my May-18 data, do I add a column and header, then the GETPIVOT command will pull all that in?
And a more pedestrian question:
When I try to insert my screenshot, I get a box asking for source--but nothing to choose from. How do I insert?
Thanks to whomever can help!
Sandra
Hello Sandra,
I will try to give some answers to your question, let's begin with the easiest first.
To insert a picture I use the Attachments button below the text field when you type in a new post or a reply, by doing so you can use pictures that are saved on your computer. You can also use the Insert image button on the toolbar, but then the picture must be stored online and shared so others can see it.
Now to your questions.
Without knowing how you have setup your data I can't really give any advice, but based on your question number 2 I understand it that you have the months horizontally and that you copy your formula horizontally, from left to right. If so, do use COLUMN(A1) instead for ROW(A1) when retrieving data for January. When you then copy the formula it will automatically change to COLUMN(A2), COLUMN(A3) and so forth. It is when you copy vertically you should use ROW function.
As for your question number 2, you will have to copy the formula to any new columns you create, it is only when you create a new row the formulas will be inserted automatically (if it is an Excel table that is).
I hope I have been able to give you some answer to your questions.
Br,
Anders
Hi Sandra,
1. From what I've seen of your screenshots emailed last week, another option is if the dates in your top row are actual dates, and not text, then you can simply use MONTH and YEAR to extract the values for GETPIVOTDATA like the formula below, which would be in cell E2:
=GETPIVOTDATA("Referral Number",RefStart!$A$3,"Ref Start",MONTH(E1),"Years",YEAR(E1))
2. Yes.
In future it's much easier if you can share a sample Excel file. Even a screenshot does not tell the whole story and can end up taking far longer to resolve your questions. If you have sensitive information then you can anonymize it or create some dummy data. It's a bit more work in advance but can save a lot of time.
Mynda
