Improving Excel Skills

[KingOfJericho]

Can't you just suppress notifications in the open command?

[fecurtis]

Originally Posted by KingOfJericho

Can't you just suppress notifications in the open command?

You mean by using Application.DisplayAlerts = False? Yeah but it's good to know they're there so you can remove them since they can slow down the workbook significantly.

[M3 Number 86]

Originally Posted by Verbiage

You're a good man for the macro formatting. Lord knows that fries my brain like nothing else should. But dammit if it doesn't work magic for data dealings and consolidation.

I was JUST about to bring up the externally linked workbooks. Or failed updating links within the sheet.

Those are ok but I've had files I open and it says circular reference. WTF CHECK YOUR WORK BEFORE SENDING IT OUT!

[M3 Number 86]

Is it weird that this has become my new favorite thread now? Lol

[fecurtis]

Originally Posted by M3 Number 86

I've had files I open and it says circular reference.

[Verbiage]

Originally Posted by M3 Number 86

Those are ok but I've had files I open and it says circular reference. WTF CHECK YOUR WORK BEFORE SENDING IT OUT!

But but but...that's too much work.

Originally Posted by M3 Number 86

Is it weird that this has become my new favorite thread now? Lol

Excel gurus ITT.

[BMW F22]

Okay here's one for you guys: how do I go about auto-summing lines?

I have a sheet with a bunch of accounts and dollar amounts associated with them. If I want to do an executive summary and pool those similar accounts (broken up by regions), how do I go about it? The number of lines associated with those accounts do change from week to week.

Only thing I can think of right now is SUMIF. I'm currently adding each section up manually. Would be nice to automate it so they all fall into certain buckets.

[Verbiage]

Is there a predetermined set of lines that you will be using for data content/dollar amount input for each account? For example, just adding trend amounts on a weekly basis like a YTD file, or something?

[BMW F22]

Originally Posted by Verbiage

Is there a predetermined set of lines that you will be using for data content/dollar amount input for each account? For example, just adding trend amounts on a weekly basis like a YTD file, or something?

No. Basically trying to get an aggregated sum of all those lines under the same account name and region.

[MKSixer]

Originally Posted by KingOfJericho

You will get nowhere in high finance without advanced excel skills. Sure, there are a rare few who shoot right out of Wharton into high positions but the vast majority of us have to work our way up and you can not do that without stellar excel knowledge. Every single job posting in my field lists excel VBA and advanced excel proficiency as a requirement - not suggestion - requirement.

There are more than you think. Understanding and knowing exactly the outcome they want, yes. Experts with MS Certification...no. I know this for a fact. Some of my best friends are C-Suite in public and private companies. Do some of the CFO's have the advanced skill...yes. All...no.

I stand behind my statement that a great mentor, improving your EQ, etc., AND having the Excel skills are the best combination. It depends on where you aspire to finish your career.

Cheers-mk

[Kidscollege$]

Originally Posted by BMW F22

No. Basically trying to get an aggregated sum of all those lines under the same account name and region.

There is an auto sum button in the upper right...highlight and end the highlighted area on a blank cell. it will auto sum.

[Verbiage]

Originally Posted by BMW F22

No. Basically trying to get an aggregated sum of all those lines under the same account name and region.

Originally Posted by Kidscollege$

There is an auto sum button in the upper right...highlight and end the highlighted area on a blank cell. it will auto sum.

That was my next suggestion, but I figured OP already knew how to do that.

[BMW F22]

I don't think I communicated my question clearly. Lol i'll provide more info later today.

[fecurtis]

Originally Posted by MKSixer

There are more than you think. Understanding and knowing exactly the outcome they want, yes. Experts with MS Certification...no. I know this for a fact. Some of my best friends are C-Suite in public and private companies. Do some of the CFO's have the advanced skill...yes. All...no.

I stand behind my statement that a great mentor, improving your EQ, etc., AND having the Excel skills are the best combination. It depends on where you aspire to finish your career.

Cheers-mk

Oh absolutely. Excel skills get your foot in the door but you have to understand the industry, your business, and your strategy and put it altogether in your analysis to move up.

[MKSixer]

Originally Posted by fecurtis

No freshman in college is a guru at Excel. They put a pivot table together and think they've mastered Excel.

Lol.
You have no idea.
Cheers-mk

[M3 Number 86]

Originally Posted by BMW F22

I don't think I communicated my question clearly. Lol i'll provide more info later today.

I think based on the fact that you are using sumif, you want to sum all data that pertain to a certain criteria? And that the data set has a different number of rows all the time?

You can do a counta(A:A) and it will give you a number that will be the number of rows. Then for the formula you can reference the value from above. So if counta returns 102, your sum formula will say sum or sumif (A2:A102).....the next time if it says 30, your sum formula will say (A1:A30). You can do this with indirect....

..if counta formula is in cell B1..
..column A represents the column you want to sum....

+sum(indirect("A1:A"&B1))

when you use indirect you need text out the formula using "" but you can reference parts of it with cell references.

see if that works

Edit: if data is in another sheet

+sum(indirect("sheetname'!A1:A"&B1))

[BMW F22]

Originally Posted by M3 Number 86

I think based on the fact that you are using sumif, you want to sum all data that pertain to a certain criteria? And that the data set has a different number of rows all the time?

You can do a counta(A:A) and it will give you a number that will be the number of rows. Then for the formula you can reference the value from above. So if counta returns 102, your sum formula will say sum or sumif (A2:A102).....the next time if it says 30, your sum formula will say (A1:A30). You can do this with indirect....

..if counta formula is in cell B1..
..column A represents the column you want to sum....

+sum(indirect("A1:A"&B1))

when you use indirect you need text out the formula using "" but you can reference parts of it with cell references.

see if that works

Edit: if data is in another sheet

+sum(indirect("sheetname'!A1:A"&B1))

Not sure what I'm doing wrong but I got #ref.

you understood it right. I have rows that sum up at the bottom. Let's say:

Product 1- region a and then the sum next to it
Product 1- region

Then On another sheet I want to pool those numbers onto a table summarizing all those sums. That table would show product 1- regions a b c d, product 2- regions a b c d, etc.

Number of rows change from time to time. Not sure what I'm doing wrong. Haha

[Verbiage]

You must have a formula referencing an invalid cell. I wish I had a screenshot.


*hint*

[M3 Number 86]

Originally Posted by BMW F22

Not sure what I'm doing wrong but I got #ref.

you understood it right. I have rows that sum up at the bottom. Let's say:

Product 1- region a and then the sum next to it
Product 1- region

Then On another sheet I want to pool those numbers onto a table summarizing all those sums. That table would show product 1- regions a b c d, product 2- regions a b c d, etc.

Number of rows change from time to time. Not sure what I'm doing wrong. Haha

edit: the formula is off in the text because I spelled it out and then moved the cells around (should be G2)

simplified

I think your #Ref is because the way you spelled out the sheet name? Make sure the syntax is correct:

if its one word then it's Sheet1!
if it's more than one word then it's 'Sheet 1'!

[M3 Number 86]

sumif with indirect

edit: shit again the formula should say H5 instead of J5

[Verbiage]

Boy you slippin'!

[M3 Number 86]

Originally Posted by Verbiage

Boy you slippin'!

bored as shit. my last day here is tomorrow - might as well be in off topic. here's what he may be trying to do....