Costs per Month

[morerockin]

Hey guys,

I am trying to insert a formula into Sheet 2 that will tally the costs per month found on Sheet 1.

See Attached.

I'd like to go a step further and insert a formula into Sheet 5 that will tally the costs per week found on Sheet 4.

[morerockin]

This time with correct attachment!!

Boo1k9.xlsx

[MarvinP]

Hi,

Find the attached where this is a very easy problem for Pivot Tables.

[berlan]

I would personally use pivot tables here. See the attached as an example.

Cheers,
berlan

EDIT: didn't see MarvinP's very similiar approach before posting. Only comment to add is that you group the "Posting Date" field to months (and years if not already declared).

[morerockin]

Hey guys,

My boss hates pivot tables. Would it be possible to put together a non pivot table solution?

[MarvinP]

Hi morerockin,

I think you need to show your boss all the features Pivot Tables offer including the Slicer tool.

To get all the features of Pivots you sometimes need Control+Shift+Enter (array formulas) or long SumProduct() functions and even the Scripting.Dictionary Reference added in the VBA Add-In section.

Learn to like Pivots. You are asking the experts! Right??

[morerockin]

sure thing. I would love to be able to use the slicer tool

Right now, my problem is I have no idea how to use Pivot Tables so I'm not able to reverse engineer what you did into my spreadsheet of 33000 rows.

What are the steps you took to do this?

[MarvinP]

Hey,

On the net are LOTS of Web sites and even hundreds of YouTube examples. Start with this one.

http://www.excel-easy.com/data-analy...ot-tables.html

or for a YouTube

http://www.bing.com/videos/search?q=...3773973530BF8B

[morerockin]

I edited my last post because I'm a bone head!

SO, what is the trick to getting the Pivot Table to list

- 2015 first descending from March to Jan
- 2014 after January . . so after Jan 2015 would be Dec 2014
- How can I customize the order in which my rows are displayed?

2015-03-08 15_05_50-Microsoft Excel - Labor Report.xlsx.png

[oeldere]

Please Login or Register  to view this content.

Not seen your file, but I bet pivot table is a very good (offered) solution.

Ask te boss WHY he / she hates pivot table.

Pivot table expands in every new version; so it is also very usefull in the future.

[morerockin]

Please Login or Register  to view this content.

Not seen your file, but I bet pivot table is a very good (offered) solution.

Ask te boss WHY he / she hates pivot table.

Pivot table expands in every new version; so it is also very usefull in the future.

I will learn the pivot table and present it the best way I can! Do you think you could take a stab at my question in regards to the pivot table?

All of you guys are awesome.

[MarvinP]

On a Pivot table if you Right Click on any of the cells you get a submenu. Sort, Group and other things are in this submenu list.

If you click on the dropdown for both rows or columns you can Filter and Sort also.

If you want some other order you can simply drag the headings of rows or columns to where you want them.

Does that answer your question above? If not read/watch these:
http://chandoo.org/wp/2012/05/31/cus...-pivot-tables/
or watch
http://www.bing.com/videos/search?q=...DCC813DD306EEA

[morerockin]

This works like a freaking charm!!!! Any advice on how to make the pivot table look a little more pretty?


Sent from my iPhone using Tapatalk

[oeldere]

I would make the pivot table like shown in the file.

Year in column
Project And Nane And week in row
Cost in value

Subtotal below the week

Endtotal per column

No endtotal per row (useless information in this example)

Any advice on how to make the pivot table look a little more pretty?

PT are not made for design.

They are made for easy analysing data.

If you want pretty look, you could make an PT graph.

See the attached file.

[morerockin]

Hey guys,

How do I add data from another sheet to my Pivot table? Can you do a vlookup?

2015-03-09 08_58_30-Clipboard.png

[morerockin]

Desperate bump!

Is it possible to add a column so I can see what my budget is? Is it also possible to add a column so I can see my average burn rate per week/month/year, etc.

[morerockin]

Is my question really that difficult or am I not doing a good job of explaining? Does this help? Please let me know.

2015-03-09 15_01_07-Microsoft Excel.png

[morerockin]

If this is any help, here is a sample Excel file.

Book9.xlsx

[MarvinP]

Ok - morerockin,

See the attached where I've done a running total of budget and amt spent. See if this helps.

[oeldere]

Don't use PNG files.

We can't work in those files (and I can't open them)

[morerockin]

Ok - morerockin,

See the attached where I've done a running total of budget and amt spent. See if this helps.

Hey MarvinP,

I do appreciate you trying. My goal was for the Pivot Table to be able to contain the budget information like the table. The first column in the pivot table should contain the employee category number, and the second should contain the budget per each activity code. After that is when I want all the good stuff from the pivot table.

[MarvinP]

Hi morerockin,

If you need more table columns to include in your Pivot Table, you might need to add them before creating the Pivot. I believe I did this to include an Absolute Value of your spending as I wanted a positive instead of negative number.

I think your best effort now would be to watch a few of the YouTube Training Videos on Pivots and play with what you have and might want. You can drag a value column down multiple times and show one as Sum and another as Running Totals or even Percent of Column or Row. After you see more of these examples Pivots become more versatile for you and understandable.