Improving Excel Skills

[BMW F22]

Originally Posted by fecurtis

Since a lot of people in this thread work with Excel, I HAVE to ask this....

Who the fuck uses a vlookup and uses TRUE for the range look up field????? I have never understood why someone would use TRUE and why Excel defaults to TRUE.

I have never used True before. So I can't really answer this question.

[Sidewinderpb]

Originally Posted by KingOfJericho

Can I look over your shoulder since we live in the same town? lol

Only if you take me on as a summer intern

[Craigy]

Go get a PHD in decision science.

Those guys know how to use excel. It's incredible.

[BMW F22]

Originally Posted by Sidewinderpb

Actually just purchased this; really need to start building up my skill set.

It's on sale for $29 (v. $100) for 12-month access:

https://www.groupon.com/deals/goskills-2

Buying this as well. Doesn't hurt to learn some more for $29.

[M3 Number 86]

Originally Posted by fecurtis

Since a lot of people in this thread work with Excel, I HAVE to ask this....

Who the fuck uses a vlookup and uses TRUE for the range look up field????? I have never understood why someone would use TRUE and why Excel defaults to TRUE.

I think I may have used it before but can't really remember. In entertainment we have contractual terms with licensees that give us license fees based on box office performance (eg. $10M box office gives us LOW, the next tier say $50M gives us MED and $100M gives us HIGH - simplified and would be values but gotta keep it a secret! )

You do a lookup to the table with actual box office, say $45.184637M, and it'll give you the fee for the $10M tier. The lookup table has to be in either ascending (or descending I don't remember) order for this to work though or it'll give you the $50M tier as your actual performance which would be wrong since it hasn't reached it.

So basically it can be used to give values based on a tier system or a range of values associated with the lookup metric.

[fcman]

Writing some macros for the first time this week. So much easier than trying to figure out all the other crazy functions, IDK how to even use vlookup. Thank god I don't use excel more than 2-3 times a year.

[Verbiage]

Originally Posted by fcman

Thank god I don't use excel more than 2-3 times a year.

Seriously? What a rarity.

[fcman]

Originally Posted by Verbiage

Seriously? What a rarity.

I'm a software developer. The only reason I usually touch an excel spreadsheet is because my customers (the finance department) send them to me/want me to send them one. Pretty much everything I do with data is easier to do with a DB query.

[Adamrt]

You should do what I did, employ a young female assistant

[Verbiage]

Originally Posted by fcman

I'm a software developer. The only reason I usually touch an excel spreadsheet is because my customers (the finance department) send them to me/want me to send them one. Pretty much everything I do with data is easier to do with a DB query.

SQL ftw.

[fecurtis]

Originally Posted by M3 Number 86

I think I may have used it before but can't really remember. In entertainment we have contractual terms with licensees that give us license fees based on box office performance (eg. $10M box office gives us LOW, the next tier say $50M gives us MED and $100M gives us HIGH - simplified and would be values but gotta keep it a secret! )

You do a lookup to the table with actual box office, say $45.184637M, and it'll give you the fee for the $10M tier. The lookup table has to be in either ascending (or descending I don't remember) order for this to work though or it'll give you the $50M tier as your actual performance which would be wrong since it hasn't reached it.

So basically it can be used to give values based on a tier system or a range of values associated with the lookup metric.

Holy shit that makes perfect sense.

A question I've had for years but never really cared to find an answer for has finally been answered.

[M3 Number 86]

Originally Posted by fecurtis

Holy shit that makes perfect sense.

A question I've had for years but never really cared to find an answer for has finally been answered.

It's official. I'm the guru of gurus. Jp

It's pretty amazing what new things you can still learn in excel even if you are already a wizard.

I just learned and incorporated +char(code(countif(command)+X)to tell me what column to reference. Combine that with an indirect() and row() then you can reference a cell eg D14. Now you have a dynamic sum or lookup or whatever you want if the data set is moving around - no more reason to keep it in the same format in case someone fucks shit up since it always happens and people say I don't know what happened? I can always lock the stuff but then they complain. lol:

Countif to let me know how many columns have been filled.
+X to give me the correct letter as a code (don't remember the exact number but I think it's in the 80s and will give you "A")

Since it'll return back to A if the last row ended in Z you will need to concatenate and use if statements to start with an A or B and so on so that you get Y Z AA AB...AZ BA...

What a nerd.

Anyone else find it annoying when somebody copy pastes using control+V?!?!

Alt esf man lol

[Verbiage]

Originally Posted by M3 Number 86

Anyone else find it annoying when somebody copy pastes using control+V?!?!

Yep lol. Many a shared workbook have been mysteriously ruined by this feature, since everyone claims to know what they are doing.

[grimlock]

Originally Posted by fecurtis

Holy shit that makes perfect sense.

A question I've had for years but never really cared to find an answer for has finally been answered.

Somehow this is extremely funny.

[M3 Number 86]

Originally Posted by Verbiage

Yep lol. Many a shared workbook have been mysteriously ruined by this feature, since everyone claims to know what they are doing.

Copy control v the click on cell click on format painter click on cell.

Annoying as shit! Lol

I cringe when I see that.

[Verbiage]

Originally Posted by M3 Number 86

Copy control v the click on cell click on format painter click on cell.

Annoying as shit! Lol

I cringe when I see that.

Lol. There's always more than meets the eye with formatting. But lots of the lower level coordinators need to log their respective jobs from their work in the field in sheets that I (the BA) need to prepare for them, and no matter how often I tell them not to do it, I find it all the time and reconfigurations have to take place. Ah well.

Guess that's why I get paid more lol.

[M3 Number 86]

Originally Posted by Verbiage

Lol. There's always more than meets the eye with formatting. But lots of the lower level coordinators need to log their respective jobs from their work in the field in sheets that I (the BA) need to prepare for them, and no matter how often I tell them not to do it, I find it all the time and reconfigurations have to take place. Ah well.

Guess that's why I get paid more lol.

But I'm dealing with finance people doing this. That's just one fraction of the issue I love had to deal with so I'm saying bye bye. Friday is my last day.

[M3 Number 86]

Originally Posted by Verbiage

Lol. There's always more than meets the eye with formatting. But lots of the lower level coordinators need to log their respective jobs from their work in the field in sheets that I (the BA) need to prepare for them, and no matter how often I tell them not to do it, I find it all the time and reconfigurations have to take place. Ah well.

Guess that's why I get paid more lol.

Funny thing is it annoys Sr mgmt when you print them something and one format is off. Lol

[Verbiage]

Originally Posted by M3 Number 86

But I'm dealing with finance people doing this. That's just one fraction of the issue I love had to deal with so I'm saying bye bye. Friday is my last day.

Congrats and best of luck to you man!

Originally Posted by M3 Number 86

Funny thing is it annoys Sr mgmt when you print them something and one format is off. Lol

Lol, the same senior management who couldn't tell you the first thing about fixing the shit in the first place.

[M3 Number 86]

Originally Posted by Verbiage

Congrats and best of luck to you man!

Lol, the same senior management who couldn't tell you the first thing about fixing the shit in the first place.

Lol they say just do it!

[fecurtis]

I just saw all the ctrl + v stuff, lol yeah that shit ruins my universe since I can be OCD sometimes about how things look.

One of the worst for me though, is having to incorporate heavy formatting into a macro. Some days I'd rather have someone slap me in the face with a large fish rather than have to code formatting into a macro.

Also, if someone sends me something and I open it only to be greeted with that damn message that there's links to other workbooks or anything like that, it irks me more than anything reasonably should.

[Verbiage]

Originally Posted by fecurtis

I just saw all the ctrl + v stuff, lol yeah that shit ruins my universe since I can be OCD sometimes about how things look.

One of the worst for me though, is having to incorporate heavy formatting into a macro. Some days I'd rather have someone slap me in the face with a large fish rather than have to code formatting into a macro.

Also, if someone sends me something and I open it only to be greeted with that damn message that there's links to other workbooks or anything like that, it irks me more than anything reasonably should.

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.