Excel 365 - Working with Tables - Calculating data from one worksheet to another. 2

[ladyck3]

Calculataing data from a "Table" on one worksheet to another. (I'm still learning how to work with "TABLES".
SAMPLE SHEET ATTACHED!

I'm usually pretty wordy so I've done my best to minimalize what it is I'm seeking to achieve. Please see the Designer Tab for a repeat of the information below for ease of access.

As stated below, the one existing forumula in Col. F / Count of the Library tab... I figured it would be easy to adapt... WRONG. Well its not for me. As for the Cost request, I'm totally stumped as to where this can actually be achieved.
---------------------------------
Column B: Designer Count - Total of patterns per designer as in Column F "Count" on the "Library Tab". I tried to use the same formula as used in Count on the Library Tab. However, I’m not keen on using table names (the syntax) when creating formulas.
---------------------------------
Column C: Total Cost - Per designer, I'd like to get a total from the "Library" tab Column G "Cost" and for this, I'm not certain how to pull that data together as its entered manually for each line item.
---------------------------------

Thanks in advance.... for whatever insight you may be able to lend here... MUCH appreciated.
Laurie

 

[ladyck3]

Geeze I went to PREVIEW and hit submit in error, find the attachment here.

 

[combo]

I can't download this file, it looks like it is a problem with '#' in file name, that truncates the link.

combo

 

[ladyck3]

OOPS, combo...

Thanks... attaching renamed spreadsheet. (One can learn from one's mistakes)

 

[combo]

Laurie,
I slightly modified the 'Library":
- added 'Group' column, that builds the group string (as \A - -\),
- changed formula in 'Count' to use table references.

Instead of counting data with formulas I created pivot table based on 'Inventory' table, new sheet 'Designer_Fldrs PT'. It uses two row fields (Group and Designer) and calculation by count of 'Designer' and sum of 'Cost'.

As the source of pivot table is table, when you add or delete new data it will automatically adjust calculated region. The only thing that is required is to refresh pivot table (right-click any cell inside pivot table and click 'refresh' from the pop-up menu).

The pivot table does not use the 'Count' column. I left it, as pivot table does not automatically accept it.

You may see small [-] buttons, as on the left in A2, A8 etc. cells. It is built in pivot table feature, that allows compact and extract names.

Have fun exploring pivot tables.

combo

 

[ladyck3]

EEEEEKKK combo,

I used to do pivot tables, but was not proficient at it. I did look at them again about a month ago, said EEEEEK and left it go. It is totally true, as is said... if you don't use it you lose it....

Its going to take me some time to reflect on this... not sure what I'm getting into here. I thank you very much! It will be awhile before you hear back, my cat is in the beginning of his "time to spend an hour in mommy's lap" time... so I'll delve in later.

I'm gonna need some time.. I'm afraid to open and look. LOL
I appreciate it... very much!!

Laurie

 

[ladyck3]

combo...

This Pivot table is precisely what I'm looking for but I need to know how to do this because that was a mere sample of almost 8,000 records and growing. So, in order to do this for myself, I see the pivot table and I see the panel on the right, but can you "pivot" me [see what I did there] LOL to an good "How To" resource so I can teach myself how to get started or how, if I move this to my master file adn then set the parameters for the size of my table?

Its a Holiday and weekend of said holiday... I sure would like to spend time teaching me (hopefully)?

This is perfectly what I was seeking to accomplish.
Thanks... (I'm a bad Googler) cannot ever find what I want to find... just a link? Or whatever would be terrific. If not.. I certainly will do what I can on my own.

Great, I appreciate your help!!
Laurie

 

[combo]

You've already arranged input data in table: categories and values in columns. Pivot table allows to create a dynamic report of the data, you manipulate and customize layout in the panel using labels of the fields (columns). There are four areas of the pivot table:
- values: for the fields here you can sum (default), count, calculate max, min, average and some other values. Fields can be placed more in once, so one can be used for summing up, second to calculate average, etc.,
- rows: a structured hierarchy in rows,
- columns: hierarchy in columns,
- filters: a set of fields to filter.

Pivot table formatting can be tuned up using ribbon commands and labels in the panel. Default titles can be overwritten.

In your pivot table there are two fields for values, two in row hierarchy. I renamed text in pivot table as in your template.

Some interesting tutorials I found:

https://www.ablebits.com/office-addins-blog/excel-pivot-table-tutorial/

https://www.simplilearn.com/tutorials/excel-tutorial/pivot-table

Finally, mostly advanced, but you can see the full power of pivot tables here:

https://www.exceldemy.com/advanced-pivot-table-in-excel/

combo

 

[ladyck3]

Quicky question, combo...

I know that in the upper left the table is listed, mine says Inventory, I was going to add the 'Group' column (I sent a truncated table, the actual one goes out to Column P, I have the remaining ZILLION columns hidden. So when I tried to add the new column I was told I don't have room. I had to unhide them all to add the new one, no big deal, just 're-hid' them all.

But the question is, and I know I was able to do this but forgot how/where, to extend the range if needed. I tried to find out how it shows the cell range of the table but can't find it all of a sudden and do not remember.

I use ASAP utilities, have used it for years, over a decade, maybe 15 years and I recently got Ablebits but there is so much to that, that I'm just not familiar with and get lost in it... WOW!

I had some fixing to do with a folder of patterns but with that done, I added the GROUP column, and am about to add the data. (I had to customize the links I have in the table, I didn't like the font / color / size and wanted them to be bold. DONE!!

Thanks More learning and studying like I said... EEEEKKKK but thanks for making me jump into the pivot table pool again... baby steps!

I don't know where in the world you are, I'm in the Dallas, Texas area... if you are in the US, HAPPY THANKSGIVING to you and all who you love and care about!!

Plugging away... whoosh...
I truly do appreciate the help.

Laurie

 

[combo]

You need to resize the table. When any cell in table is telected, you have access to table tools, the command is just below the table name, MS support here.

Before finalizing the summary with pivot table it is worth to analyse the structure of the table, remove unnecessary calculations that will be in the pivot table, add helper columns (as 'Group' in your file) and have short and meaning headers.

I am in Warsaw, Poland (so the time shift), I've looked at the weather forecast for Dallas - nice, we have first snow yesterday...

combo

 

[ladyck3]

1. Warsaw, cool (literally) what I wouldn't give for a bit of your weather though. Its been SOOOOOOO dry and SOOOOOOO hot for the last 6-8 months... YIKES! From about May through mid/late October, temps were in the (F) 110-115 degrees and no rain... Then there is the other side of the flip... February we had temps below zero and our electric (whole house is electric) go out for a few days. Thank goodness I crochet, we have LOTS of blankets and a fireplace Here, don't blink or the weather will change and with climate change, we now only get about 3 days of perfect weather between SpingSumer and FallWinter... we have only 2 seasons now. I jest but only a little.

Now to business... thanks so much for the "here" resource above... I have so much to learn/relearn. My brain is fried but I want to know this! Now let me tell you my biggest hurdle right now. On the sample you sent, it showed the Pivot Table elements/fields on the right. That closed somehow and I don't know how to get that back. (told you I lost it.. I've got a long way to go) LOL

Don't cringe too much, and run if you want to...

THANKSSSSSSS!!
Ok, see ya later

 

[combo]

It takes some time to get used to work with pivot tables. Most of work can be done in the pivot table panel and pivot table tools in the ribbon.

You can experiment and drag fields in the pivot table panel: from rows to filter (as 'Group' in the example file), rows to columns, drag new fields to category (rows/columns/filters) or values areas - or drag off existing fields, in case of multiple value fields drag sigma field between rows and columns regions, test field settings (small drop-down on the right of the field label: pivot table layout changes after each action.

What is nice at the end: you can summarize your 8000 records quickly and without any formulas, just by telling Excel how the summary should look like.


combo

 

[ladyck3]

Morning/Afternoon/Evening

My sample is a VERY small slice of the pie. For that reason I'm working on the list of designers right now to clean it up, remove duplicates (I had some strange stuff in my library) and I read to not have leading or trailing spaces in the data... so I'm cleaning that too.

So once I get that part of everything down, I'm going to start really digging in. Hopefully within the news 4 hours or so... interruptions 'n things. So I'll check back later. I do have to ask a quicky since I have not "played" with it yet... please correct me if I'm wrong or missing something.

1. I add a new worksheet to create the Pivot Table in.
2. Create a Table with the headers I want, as the sample Designers, Designer Count, Cost Total.
3. Now this is where I need a little nudge I believe,

- Select the columns from the full "Designers" table (the full one)​

- Then for the Rows I'm unsure what to select from​

Oh combo.. I'm frustrated. The sample, any time I want to see the pivot setup, I have to re-download the file to see the pivot setup on the right where you drag what is needed to drag and I don't know where to open it back up again. When I click on things a lot of stuff happen and I freak out cuz I don't know how I'm seeing what I'm seeing.

I think I'm hopeless. I just don't get it I went to bed, could not sleep because I thought I was so close and wanted to keep going, tried for 2 hours and then forget it, I'm just going to get up and work on it. But I'm lost. I think you made a table from the designer folders list sheet and now I see a Pivot thing on the top row that I didn't have before. Not sure how I got it and if it goes away, I don't know how to get it back...you know up there by Home, Insert, there is one that say Power Pivot and anothere PivotTable Analysis but they pull up things I'm lost over.

Its perfect what you did, I'm just at a loss as to how to create it in my full inventory file on my own
I don't know what to do.

and I did add a column on my inventory worksheet with the Group labels \A, \B, etc. I wrote down on a pad what fields were in that pivot table field picker window on the right, if I can get that back.. but not sure if you picked those up from the first worksheet, the Inventory one, like the Costs 'n stuff. <sigh>

ending here... I'm probably frustrating you more than I'm frustrated with me. HOPELEsS!

Laurie

 

[ladyck3]

OK, less frustrated. I found the pivot table part Insert > Pivot Table (DUH me) and I tried to enter the criteria but it is locked up.

I have the designer names all in alphabetical order but there are multiple(blank)lines and it said it could not load all of the information should it just show me what it could and I said yes, then I got more blanks and I don't know why, but the patterns are listed in reverse alphabetical order, so the Z designers are listed under the A Designer names... I mean really...

Its stuck in "waiting for the query to be executed... and my processor is running and nothing is happening.
I'm going to go lay down not and come back to this in awhile. It also shots a yellow part that says (near the top of the pivot layout box) "Relationships between tables may be needed. Options: Auto-Detect... Create...
Now for THAT... I'm lost.

At least I got this far, right? its messed up but its a pivot LOL! A screenshot of my screen is attached. I'm hoping its just a matter of the selections being in the wrong places? I have the "Library" tab selected and am pulling the data from that worksheet. for the Title, Count and Cost and I did insert a "Group" into that Library worksheet.

Be back later....

 

[combo]

You probably do not need 'Cost' as category, (at the bottom of 'Rows' area in the pane). If so, drag it off.

If in the source table you have manually entered 'Count', 'Sum of Count' is ok. Otherwise, if you need do count items in specific aggregation, you can drag any label to the values area and set count instead of sum as aggregation (click a drop-down of a field in values area and display properties, you can see differnt types of aggregation available).

In the rows area you can see four level hierarchy. Even after removing 'Cost' (as it should be rather in values section, unless you need it as category), do you need it all?

You could check source data, ir seems that, at least at the beginning, you have emptu group with no designer.

Generally, the pivot table seems to be very big, maybe bigger than the source, so the time of processing issue. Moreover, sum of cost is empty, is it ok (you have no values in source table.

combo

 

[ladyck3]

[highlight #FCE94F]*****************************************[/highlight]​

combo - combo - combo​

[highlight #FCE94F]*****************************************[/highlight]​

WOO HOO !!!
* * * * * ! ! ! ! ! S - U - C - C- E - S - S ! ! ! ! ! * * * * *

THANK YOU combo !!! I owe you ONE TRILLION, BILLION, MILLION !!!​

​

It may have taken me awhile... but thanks to YOU... I got there!!!​

... ... ... ... ​

And to think I thought I'd never get it...
I appreciate the help more than you will ever understand....
OOOOO, now my mind is racing.... the possibilities!! LOL
In case you couldn't tell... I'm HAPPY!!

{ { { { { HUGS } } } } }​

 

[ladyck3]

Uh oh... trouble

I used my Inventory Table, but it is a growing table, I add information and new data almost daily. HOWEVER, I'm unable to add new lines or "insert rows" to the existing table and I'm assuming its because of the relation between that main table and the Pivot Table.

NOW WHAT? I sure hope I don't have to disregard the Pivot table and just work with my standard inventory. I will if I have to, but I'm unsure what to search for to find an answer to this prediciment.

 

[SkipVought]

HOWEVER, I'm unable to add new lines or "insert rows" to the existing table

Why is that? That should not happen. Data/rows can be added/deleted at will any time.

You only need REFRESH your Pivot Table to reflect any changes in your source table.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[ladyck3]

Skip (HI I've missed you ... thought you quit) GLAD to see you...

Well the data table or Inventory table will not allow me to add lines, I've got a bunch to add and I'm simply putting it all down/cataloging it, on a separate page until I can find out why in the world, all of a sudden, I am unable to add records.

Ok, well how do I "Refresh" the pivot table. It was working when I went to bed. But today it shows like it did before... I moved some unlinked data to a "save just in case file" because they were not related in any way to the Pivot Table, just extra junk pages .. oh I did get an error about broken links but they were to worksheets that did not involve the Pivot... so I let it break the links. it was to 2 unused worksheets, they were not used.

How does on "Refresh" a pivot table.. I'm going a googling....

GREAT to see you!
Laurie

 

[ladyck3]

Oh good heavens... and HOLY HILL... MOUNTAIN...

I just tried to insert a new row and POOF it let me. <sigh>

I'm flying blind, sometimes and afraid I'm going to blow something up. This is 4 years of work now... for it to blow sky high... I've learned so much and pray I can retain it all... and take OODLES of notes... I started using OneNote about a year ago... I save EVERYTHING... They should rename the app to "Laurie's Brain" LOL

You know me... panic mode ensued... oh well...

If I don't make it back before the holidays... I hope you had a great Thanksgiving and
MERRY CHRISTMAS and HAPPY NEW YEAR! (2023 has been tough, we lost our pup in August) I'm hoping we can get a new one early in 2024 cuz the rest of the year I think is going to .. umm be very rough. I'll leave it at that.

Hugs to all... I appreciate you folks so very much!!

hmmm, I wonder though, what happened that it wouldn't let me add any records. It just needed me to come here and beg for mercy .. LOL