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

 

[SkipVought]

Refresh PT

https://support.microsoft.com/en-us...ble-data-6d24cece-a038-468a-8176-8b6568ca9be2

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

Oh COOL...

I found the refresh option but had NO CLUE all of the options available.
Thanks for the TIP!

 

[combo]

Nice you solved it.
Pivot table is a great tool, but sometimes not intuitive for a beginner. I would copy a small set of data to new workbook, create a new pivot table and experiment with lauout and aggregations. Additionally, some web tutorials have workbooks with described examples to download. All that will benefit in required reports created mostly with mouse and rarely requiring additional formulas.

combo

 

[ladyck3]

combo,

Thanks but I would not have done it without your guidance and response with the sample. So thank you. Skip, thanks for stepping in over the weekend with the "Refresh" option/tip

I have to admit, my mind is racing with things I might be able to use this for.. such as... Skip? All of those formulas that you helped me with for amounts by week, month, year and other data analysis... this would work in a Pivot table, right?

Memory Refresh on one of the formulas:

Formula for Year: In Value Field =SUMPRODUCT((YEAR(Table2[Date added])=A2)*(Table2[Cost]))​

Formula for Month: In Value Field =SUMPRODUCT((MONTH(Table1[Date added])=A11)*(Table1[Cost]))​

Formula for Quarter: In Value Field: =SUMPRODUCT((YEAR(Table2[Date added])=$A15)*(MONTH(Table2[Date added])>=(C$14-1)*3+1)*(MONTH(Table2[Date added])<(C$14-1)*3+4)*(Table2[Cost]))
​

Now that I'm NOT-THE-Pivot-Table-MOMMA LOL... a Pivot Table might work for the same purposes? Or too many tables I'd have to make for each entity?

Just throwing it out there...

Laurie

 

[SkipVought]

Regarding PTs and your data, Yes, you can get various aggregations (sum, count, average etc) based on the field(s) in the row area. I personally like the "Classic" rendering that displays the field groupings.

It's been a while since I used PTs but Yes, you can have aggregations by all sorts of date divisions as complex as, for instance, having Designers in rows down and Dates in columns across (although I see no date column), aggregating by count and/or cost.

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]

WHEW.. I'm feeling aggregration... (ie: aggravation)
I'll leave all that be for another time, I believe.

Geeze Laurie, one thing at a time...

PPPpssssttttt, y'all are creating a monster... Love learning (with help)

me...

 

[SkipVought]

We'll have to deal with...

Aggregation aggravation agitation

Or how about an Alaskan islander who makes a reference to an imaginary thing being an...

Aleutian illusion allusion.

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]

..... you win! See attached, it says it all....

 

[SkipVought]

Well is it

Aggregation aggravation agitation alleviation?

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]

Kinda, not really... I just had a major mess, figured it out, copied crappy data to a new file to save for posterity. Then took the good parts and moved them to the "Fixed" workbook... I now have a solid Pivot Table just to my liking...

Now as for aggr'ing etc.. well that's for another day.. but that doesn't mean I'm not giving it serious thought. I assume and correct me if I'm totally wrong in my thinking... I should take the dates involved and put them in a format where each section has a column/[table identifier] Like Mo Dy Yr ... in order to sort out the data by month, quarter, year? or am I needing somehow to put formulas.. I think I like what I have with your original formulas and am going to quit while I'm ahead.

I'll play at a WAY LATER DAY/TIME... not right now.

now .. I've got so much work to do, to get it all catalogued and not have duplicates and verify I have the files as things have been a mess and are also catalogued on my Pattern Resource site Library... If you purchase a pattern, its automatically added to your online library but if you have an option to obtain the file either by it being offered as a full free pattern, or offered to use "printfriendly.com" to download the designer's pattern from their blog/website... all these 10+ years, I didn't catalog the free ones.. I'm doing it now!!! If I've already spent 4 years on this project, I want it complete (before I die and it dies with me cuz no one cares but me) my 50+ years of collecting patterns and (now too busy in excel to crochet cuz I love my PC and I love EXCEL even when I get "aggitated" (referencing previous comments... see what I did there?) hehehe well... POOF Thousands of dollars in 50+ years in PDF and actual room full of books will probably be "Farenheit 451'd" cuz have no crochet buds... But I'm having fun... and I have SO MUCH I want to make... I'll be here at 251 years and still be working in excel... LOL

Hopeless... YOU BETCHYA!!
A little bit crazy,
A whole lot consumed in Excel and Crochet (both having their own "hooks")
C'mon guys, ya know ya wuv me... LOL

Laurie

 

[combo]

I should take the dates involved and put them in a format where each section has a column/[table identifier] Like Mo Dy Yr ... in order to sort out the data by month, quarter, year?

The beauty is that you can keep dates as regular dates in a single column in your source data table. When a pivot table based on the table is created and you drag date label as a row field, Excel typically adds three aggregation fields: years, months, quarters, so the pivot table ends up with four fields based on the date, one exact date and three aggregations. This happens both in reusable fields list and in rows area in the design. Unused fields can be dragged off from the rows area.

If Excel does not group dates, or you need other aggregations or adding limits to reported dates range, right-click any cell related to date (as year or exact date), click 'group...', and select required settings. First part of this short MS video refers to grouping dates:

https://support.microsoft.com/en-au...vottable-c9d1ddd0-6580-47d1-82bc-c84a5a340725

combo