calling Excel Studs and Studettes
by vivaflanner (2017-09-12 18:16:52)
[ cannot delete ]   [ Edit ]   [ Return to Back Room ]   [ Show All Thread ]   [ Ignore Poster ]   [ Report Post ]   [ Highlight Poster ]   [ Reply ]

 


I make frequent use of pivot tables.

One limiting/annoying factor is that when setting up other tables/reports that link to pivot table data, one cannot set up the formulas that reference the pivot table by dragging.

Instead, you have to select cell by cell in in pivot table.

By this I mean, Lets suppose you have a 10 column wide by 40 row high pivot table. If you need to grab the first 4 columns and rows 12-32 for a smaller report, you cant just link the upper left corner of your new report to the pivot, and drag over 4 and down 20 rows. You have to select each of the 40 reference individually. Takes forever and you're bound to make a mistake.

Anyone know how to work this better (I know I could make another pivot table, but they arent as easy to format/work with as plain cells)


=getpivotdata() *
by airborneirish  (2017-09-12 19:38:16)     Delete  |  Edit  |  Return to Board  |  Ignore Poster   |   Highlight Poster  |   Reply to Post


Manually reference the first upper left cell
by Corrleone  (2017-09-12 18:43:46)     cannot delete  |  Edit  |  Return to Board  |  Ignore Poster   |   Highlight Poster  |   Reply to Post

say =Sheet1!B32; then just drag the formula from there.


thank you Don Correleone
by vivaflanner  (2017-09-13 12:10:21)     Delete  |  Edit  |  Return to Board  |  Ignore Poster   |   Highlight Poster  |   Reply to Post

by typing in the reference vs selecting, the new table picks up the cell as opposed to a piece of pivot.


Thanks! *
by rick  (2017-09-13 08:53:13)     Delete  |  Edit  |  Return to Board  |  Ignore Poster   |   Highlight Poster  |   Reply to Post


If it's a one-time thing, I just cut and paste values
by rick  (2017-09-12 18:35:41)     cannot delete  |  Edit  |  Return to Board  |  Ignore Poster   |   Highlight Poster  |   Reply to Post

for the whole pivot table on a new sheet.
If it's something I'll use again, I'll use SUMIF instead of a pivot table.


ive been doing the cut and paste
by vivaflanner  (2017-09-12 18:40:54)     cannot delete  |  Edit  |  Return to Board  |  Ignore Poster   |   Highlight Poster  |   Reply to Post

but that gets old

I am hoping someone knows how to make cell references work. many times there is a way, I need an Excel Jedi to show me


Keep the pivot table and replace the underlying data
by fontoknow  (2017-09-12 19:22:02)     Delete  |  Edit  |  Return to Board  |  Ignore Poster   |   Highlight Poster  |   Reply to Post

Might be able to do this by linking the pivot table to an external file that you just overwrite.


The contents of this post represent the views of the author. NDNation.com is not responsible for its contents.