I use the GETPIVOTDATA function frequently to build formulated summary tables (non-pivot tables) that tie into pivot tables. If you have a cell and enter "=" and click on a pivot table, then Excel writes a formula using the GETPIVOTDATA function. To enhance the formula, I edit it to use a cell link instead of any fixed reference, and I can then copy/paste so that my table reads various reference points. This works great for pivot table row or column fields. However, when I try to do this with a data field, I get a #REF! error.
Example
=GETPIVOTDATA("Hours",'PivotTableSheet'!$A$1,"Product Group","State Tax")
The above tells me how many hours of state tax work were charged from a pivot table. Hours is one of many data fields in the pivot table, and Product Group is the primary Row field.
=GETPIVOTDATA("Hours",'PivotTableSheet'!$A$1,"Product Group",A5)
In the above, I switched the "State Tax" reference to refer to cell A5, that has State Tax entered in it, and cell A6 has Federal Tax, which allows me to copy the formula down. I might have 30 - 50 different types of services.
Now I might want to pull more than just hours (i.e. fees, expenses, etc) and when I try to use a cell reference instead of "Hours" in the formula, I get an error. Picture a table with the services listed in column A (A5:A100) and the metrics listed in row 4 (B4:N4). I want a versatile formula that pulls in the data into a simple matrix where I can copy that formula over the entire range (B5:N100).
I would think the below would work in cell B5, but it does not.
=GETPIVOTDATA(B$4,'PivotTableSheet'!$A$1,"Product Group",$A5)
Any help would be appreciated. Thanks.
Hi, I need help in writing functions maybe for example to find if named sheet exist with x name...etc if not do this else that...or
to see if col has dates...
My actual question would be to learn how to write this kind of function.
Thanks in advance.
Pedie
I have just a very simple thing to accomplish but I guess I am just not getting it. I am comparing two columns of data, larger against small column, taking the matching results to a separate column then the non-matching column to another. I have used a dynamic range for each column.
Biglist Littlelist Matches NonMatches
123456789 123654789 123654789 236548925
123456789 245987445 665713235 461876212
236548925 124598792 123456789 114532215
461876212 665713235 165645136
124598792 123436789 245987445
114532215 124598792
665713235
165645136
I have used the differant examples here, and elsewhere and I am not able to get the Functions to work with INDEX or MATCH even when nesting the Formula.
Any help would be very appreciated.
Hello All,
I have a small but annoying issue. I have a spreadsheet with two columns in it. The A column has finished part numbers in it and the B column has the exact same finished part numbers but I need to remove anywhere from 1 to 4 zero's that are at the front of the finished part numbers.
Is there a way I can use the TRIM function to go through the entire column instead of one cell at a time. I have 5789 lines to go through.
Any assistance would be greatly appreciated. Thank you
i have a task of producing a spreadsheet that asks questions and takes figures then displays them as a report.
e.g how much is your energy bill? the user types a figure, its calculated in a formula, and the results displayed in a report.
I need it set out so it looks professional and user friendly. Has anyone got any examples of well laid out user friendly colourful spreadsheets?
Would appriciate it if some one could help me.
Thanks a great deal
Hi,
I have many strings of arbitrarily length. Each string always
has one number (0-9) component and one alpha-character (A-Z) component.
The order of the components in the string is entirely random.
Sometimes numbers come first and sometimes letters/characters.
Also, the length of each separate component is also varies and
there is no fixed rule to how long it will be each time.
Examples a
String: RGH45 Alpha: RGH Num: 45
String: 4589THF Alpha:THF Num: 4589
String: FGGFFF56464645464 Alpha:FGGFFF Num: 56464645464
String: sdgdfgdfg874645 Alpha:sdgdfgdfg Num: 874645
String: 54sfsdfdsf Alpha:sfsdfdsf Num: 54
String: s54654646 Alpha:s Num: 54654646
etc..
I want to have two strings. One called Alpha and One called Num
I want to have the alpha-character component to be extracted from the string
and set to Alpha and the numerical component to be extracted & set to Num.
How/what is the Excel Macro VB code to accomplish this from within the macro?
Thanks a lot!
Hi,
I am looking for a good website specially a video tutorial that shows you how to create dashboards or charts. I am looking for similar website like http://www.datapigtechnologies.com/
this is very good website for those of you who need to do some fancy staff with excel. Any help will be appriciated. Thanks.
hi, i would like to know the site or resource for mastering Loops like DO loops and other loops with examples.if any body knows some sites do list here.
Hello everybody,
Can anybody upload the
2500 Excel VBA Examples, if you have.
