populating value from one tab into a pivot

%3CLINGO-SUB%20id%3D%22lingo-sub-1548504%22%20slang%3D%22en-US%22%3Epopulating%20value%20from%20one%20tab%20into%20a%20pivot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1548504%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20day%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20figure%20out%20how%20i%20can%20have%20a%20summed%20value%20on%20my%20working%20tab%20update%20on%20my%20Pivot%20tab.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%20if%20I%20note%20a%20line%20item%20as%20PAID%20in%20one%20column%2C%20I%20would%20like%20the%20value%20of%20that%20line%20to%20be%20populated%20on%20my%20PIVOT%20tab%20as%20well%20as%20if%20there%20are%20multiple%20lines%20associated%20with%20one%20customer%20the%20total%20%22PAID%22%20value%20will%20populate%20and%20update%20on%20my%20Pivot%20as%20I%20make%20changes%20on%20my%20working%20spreadsheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20that%20makes%20sense.%3C%2FP%3E%3CP%3EThank%20you%20Carol%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1548504%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1548605%22%20slang%3D%22en-US%22%3ERe%3A%20populating%20value%20from%20one%20tab%20into%20a%20pivot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1548605%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F741438%22%20target%3D%22_blank%22%3E%40carolmcphail%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWithout%20more%20details%20(or%2C%20better%20yet%2C%20a%20copy%20of%20your%20spreadsheet)%20it's%20hard%20to%20give%20specific%20guidance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20general%2C%20so%20long%20as%20the%20new%20data%20are%20within%20the%20previously%20defined%20data%20range%20used%20by%20the%20Pivot%20Table%2C%20it%20should%20update%20%22automatically%22%20once%20you%20hit%20the%20Refresh%20button%20on%20the%20PivotTableAnalyze%20toolbar.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1595877084968.png%22%20style%3D%22width%3A%20392px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F208273i760ECBBD30FB5513%2Fimage-dimensions%2F392x199%3Fv%3D1.0%22%20width%3D%22392%22%20height%3D%22199%22%20title%3D%22mathetes_0-1595877084968.png%22%20alt%3D%22mathetes_0-1595877084968.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EIf%20that's%20not%20working%20for%20you...please%20post%20either%20a%20copy%20of%20your%20actual%20sheet%2C%20or%20a%20reasonable%20resemblance%20of%20it%2C%20just%20devoid%20of%20confidential%20or%20private%20info.%20Actual%20excel%3B%20not%20just%20an%20image%2C%20please.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1548696%22%20slang%3D%22en-US%22%3ERe%3A%20populating%20value%20from%20one%20tab%20into%20a%20pivot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1548696%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%20-%20Thank%20you%20for%20responding.%26nbsp%3B%20I%20have%20attached%20my%20spreadsheet.%26nbsp%3B%20I%20typically%20update%20the%20information%20on%20the%20tab%20name%20%22page%22%20in%20column%20L%20with%20a%20word%20or%20phrase%20such%20as%20%22PAID%22%20or%20%22DISPUTE%22.%26nbsp%3B%20What%20I%20am%20looking%20to%20do%20is%20when%20I%20mark%20a%20line%20item%20in%20column%20L%20with%20%22PAID%22%20have%20the%20total%20value%20Column%20I%20of%20what%20is%20noted%20as%20paid%20for%20that%20customer%20%22NAME%22%20column%20C%20but%20updated%20on%20my%20PIVOT%20tab%20in%20a%20column%20that%20has%20been%20added.%26nbsp%3B%20Hopefully%20when%20you%20look%20at%20my%20spreadsheet%20it%20will%20make%20more%20sense.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1548802%22%20slang%3D%22en-US%22%3ERe%3A%20populating%20value%20from%20one%20tab%20into%20a%20pivot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1548802%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F741438%22%20target%3D%22_blank%22%3E%40carolmcphail%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20need%20to%20take%20that%20spreadsheet%20down%20as%20soon%20as%20you%20can.%20It%20does%20contain%20quite%20a%20bit%20of%20private%20or%20company%20confidential%20records...%26nbsp%3B%20In%20fact%2C%20just%20given%20that%20you%20might%20not%20see%20this%20for%20a%20while%2C%20I%20have%20alerted%20the%20board%20moderators%20to%20remove%20it%20for%20your%20own%20sake.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20said%2C%20I'll%20offer%20the%20observation%20that%20I've%20never%20seen%20the%20PivotTable%20used%20as%20you're%20using%20it.%20It%20obviously%20is%20working%20to%20a%20certain%20extent%2C%20but%20usually%20the%20Pivot%20Table%20is%20used%20for%20much%20more%20of%20a%20cross-tabulated%20summary.%20You're%20using%20it%20...well%2C%20it's%20not%20totally%20clear%20to%20me.%20Almost%20looks%20more%20like%2C%20if%20you%20were%20to%20un-collapse%20each%20row%2C%20it%20would%20be%20just%20as%20long%20as%20your%20raw%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20does%20look%20as%20if%26nbsp%3B%20you're%20wanting%20to%20create%20a%20top-level%20summary%20of%20where%20things%20stand%20with%20each%20client%3B%20but%20that%20aside%2C%20I'm%20not%20sure%20what%2C%20say%2C%20all%20the%20colors%20mean--are%20they%20manually%20put%20in%20there%3F%20Do%20clients%20that%20share%20a%20color%20also%20share%20some%20other%20characteristic%20such%20that%20if%20you%20could%20group%20the%20salmon%20color%20clients%20together%20that%20would%20be%20meaningful%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I'm%20getting%20at%20is%20that%20there%20might%20be%20a%20different%20way%20to%20create%20a%20%22dashboard%22%20to%20put%20all%20this%20summary%20data%20together%20even%20more%20effectively.%20But%20to%20do%20that%20you'd%20need%20to%20articulate%20the%20ground%20rules.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20have%20the%20most%20recent%20Excel%20update%3F%20Here's%20a%20test%20for%20you.%20I'm%20going%20to%20write%20a%20formula%20below.%20I%20want%20you%20to%20create%20a%20new%20tab%20in%20your%20workbook.%20And%20then%20enter%20this%20formula%20into%20a%20cell%20somewhere%20in%20the%20top%20left%20corner%20(say%20in%20cell%20B4%2C%20to%20give%20yourself%20a%20little%20room).%20%3CSTRONG%3E%3DSORT(UNIQUE(page!A2%3AC654))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20formula%20will%2C%20if%20you%20have%20the%20most%20recent%20update%2C%20generate%20a%20list%20with%20roughly%20188%20rows%2C%20consisting%20of%20every%20unique%20combination%20of%20columns%20A%26nbsp%3B%20through%20C%20of%20your%20database%20on%20the%20%22page%22%20tab.%20That%20single%20formula%2C%20entered%20only%20once%2C%20will%20generate%20all%20that%20data.%20It's%20one%20of%20a%20set%20of%20what%20are%20called%20%22Dynamic%20Array%20Functions%2C%22%20newly%20released.%20And%20it%20strikes%20me%20that%20you%20(possibly%20with%20some%20help)%20might%20be%20able%20to%20create%20a%20different%20summary%20page%20using%20that%20function%20to%20generate%20that%20primary%20list%2C%20and%20then%20another%20one%20(FILTER)%2C%20perhaps%20in%20combination%20with%20SUM%20and%20MAX%20and%20MIN--other%20functions--to%20generate%20summary%20data%20that%20doesn't%20need%20to%20be%20refreshed%2C%20because%20it%20will%20automatically%20incorporate%20the%20changes%20when%20things%20are%20%22paid%22%20or%20whatever....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20I%20can't%20post%20a%20copy%20of%20your%20spreadsheet%20back%20with%20that%2C%20because%20it%20really%20does%20have%20too%20much%20private%20customer%20data%20on%20it......%20I'll%20send%20you%20a%20private%20message.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20does%20look%20to%20me%20as%20if%20you're%20pretty%20savvy%20with%20Excel....so%20let%20me%20suggest%20you%20study%20this%20YouTube%20video...it%20may%20give%20you%20what%20you%20need%20to%20create%20your%20own%20Dynamic%20Array%20solution...%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1548813%22%20slang%3D%22en-US%22%3ERe%3A%20populating%20value%20from%20one%20tab%20into%20a%20pivot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1548813%22%20slang%3D%22en-US%22%3EHow%20do%20I%20remove%20the%20spreadsheet%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1548817%22%20slang%3D%22en-US%22%3ERe%3A%20populating%20value%20from%20one%20tab%20into%20a%20pivot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1548817%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F741438%22%20target%3D%22_blank%22%3E%40carolmcphail%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3EClick%20on%20that%20little%20downward%20pointed%20symbol%20in%20the%20top%20right%20corner%20of%20the%20post%20containing%20the%20sheet.%3C%2FLI%3E%3CLI%3ESelect%20%22Edit%22%3C%2FLI%3E%3CLI%3EThen%20you%20should%20be%20able%20to%20see%20a%20small%20%22x%22%20in%20the%20corner%20of%20the%20link%20to%20your%20spreadsheet.%20Select%20that%20%22x%22%20and%20it%20should%20delete%20the%20attachment.%3C%2FLI%3E%3C%2FOL%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1550717%22%20slang%3D%22en-US%22%3ERe%3A%20populating%20value%20from%20one%20tab%20into%20a%20pivot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1550717%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F741438%22%20target%3D%22_blank%22%3E%40carolmcphail%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EI've%20removed%20the%20spreadsheet%20from%20your%20post%20above%20for%20you.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Good day

 

I am trying to figure out how i can have a summed value on my working tab update on my Pivot tab.  

 

For example if I note a line item as PAID in one column, I would like the value of that line to be populated on my PIVOT tab as well as if there are multiple lines associated with one customer the total "PAID" value will populate and update on my Pivot as I make changes on my working spreadsheet.

 

I hope that makes sense.

Thank you Carol 

6 Replies

@carolmcphail 

 

Without more details (or, better yet, a copy of your spreadsheet) it's hard to give specific guidance.

 

In general, so long as the new data are within the previously defined data range used by the Pivot Table, it should update "automatically" once you hit the Refresh button on the PivotTableAnalyze toolbar.

 

mathetes_0-1595877084968.png

If that's not working for you...please post either a copy of your actual sheet, or a reasonable resemblance of it, just devoid of confidential or private info. Actual excel; not just an image, please.

@mathetes  - Thank you for responding.  I have attached my spreadsheet.  I typically update the information on the tab name "page" in column L with a word or phrase such as "PAID" or "DISPUTE".  What I am looking to do is when I mark a line item in column L with "PAID" have the total value Column I of what is noted as paid for that customer "NAME" column C but updated on my PIVOT tab in a column that has been added.  Hopefully when you look at my spreadsheet it will make more sense.

@carolmcphail 

 

You need to take that spreadsheet down as soon as you can. It does contain quite a bit of private or company confidential records...  In fact, just given that you might not see this for a while, I have alerted the board moderators to remove it for your own sake.

 

That said, I'll offer the observation that I've never seen the PivotTable used as you're using it. It obviously is working to a certain extent, but usually the Pivot Table is used for much more of a cross-tabulated summary. You're using it ...well, it's not totally clear to me. Almost looks more like, if you were to un-collapse each row, it would be just as long as your raw data.

 

It does look as if  you're wanting to create a top-level summary of where things stand with each client; but that aside, I'm not sure what, say, all the colors mean--are they manually put in there? Do clients that share a color also share some other characteristic such that if you could group the salmon color clients together that would be meaningful?

 

What I'm getting at is that there might be a different way to create a "dashboard" to put all this summary data together even more effectively. But to do that you'd need to articulate the ground rules.

 

Do you have the most recent Excel update? Here's a test for you. I'm going to write a formula below. I want you to create a new tab in your workbook. And then enter this formula into a cell somewhere in the top left corner (say in cell B4, to give yourself a little room). =SORT(UNIQUE(page!A2:C654))

 

That formula will, if you have the most recent update, generate a list with roughly 188 rows, consisting of every unique combination of columns A  through C of your database on the "page" tab. That single formula, entered only once, will generate all that data. It's one of a set of what are called "Dynamic Array Functions," newly released. And it strikes me that you (possibly with some help) might be able to create a different summary page using that function to generate that primary list, and then another one (FILTER), perhaps in combination with SUM and MAX and MIN--other functions--to generate summary data that doesn't need to be refreshed, because it will automatically incorporate the changes when things are "paid" or whatever....

 

But I can't post a copy of your spreadsheet back with that, because it really does have too much private customer data on it...... I'll send you a private message.

 

It does look to me as if you're pretty savvy with Excel....so let me suggest you study this YouTube video...it may give you what you need to create your own Dynamic Array solution...

https://www.youtube.com/watch?v=9I9DtFOVPIg

 

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, bu...
How do I remove the spreadsheet?

@carolmcphail 

 

  1. Click on that little downward pointed symbol in the top right corner of the post containing the sheet.
  2. Select "Edit"
  3. Then you should be able to see a small "x" in the corner of the link to your spreadsheet. Select that "x" and it should delete the attachment.

Hello, @carolmcphail 

I've removed the spreadsheet from your post above for you.