Taking excel formula questions. AMA. Bring it on bitches

likesmarthelpful
Posting as :
works at
You are currently posting as works at
Highlighted IconHIGHLIGHTED

@Assurance Senior 1: say you have data from A1 through A469. I would just highlight that data in that column in full (A1:A469), Go To Special—>Blanks, then in A2 do =A1, and hit Ctrl+Enter. It should then fill all of the GL Acct numbers for you using the ones above it until it changes.

likehelpful

@kpmg1 I hate to admit it but I think OP has me by a bit in the knowledge department but for how I got to being the most knowledgeable excel user in most rooms it was entirely self taught through mostly side projects I was doing in college mostly to do with sports statistics or other dumb hobbies. It's probably slower than a course, but you can learn a ton just by googling and becoming familiar with the terminology as you're working and realize you need to do something that seems like it should be possible

like
Recent IconRecent

I can try. If you have a chessboard and can remember how the horse piece can start a move in the game - 2 steps vertically and 1 horizontally and reach a specific point in the board thats what an index match does for you. The match determines how many steps vertically and how many horizontally you can move and the starting point is always top left of your range (chess board). =Index(range,verticalmatch, horizontalmatch).
*Remember the words though- match returns a reference and not a value which is very powerful

likeuplifting

Associate 1- =hyperlink("https://turbotax.com/","click here") enter this in an excel cell and click it

likefunnysmart

PWC1 - Its better done using VBA. For i=1 to inprange. Columns. Count: for j=1 to inprange.rows.count : if inprange.cells(i,j)<>"" then outputrange.offset(ctr)=inprange.cells(i,j):ctr=ctr+1:endif : next j: next i

likehelpful

BDO1 - I beat you to it. 1-1 so far :)

likefunny

Alt N V T but you are asking just to annoy me. Give me a formula thing young kid.

funnylike

@a1 =left(1, text(year(cell)))

like

I think I was pretty clear that I want to use whatever word is in cell A1 (in this case January) to pull from a tab with the same name.

likesmart

@KPMG 1 - you’re on the right track, you just need to add another MATCH function for the column. So you’d have =INDEX(SourceInfo , MATCH([GL Acct #],A:A,0) , MATCH([TB Code],1:1,0)). SourceInfo is the table basically where all of your data is, GL Acct # is the account #you want to find (normally a reference), and TB Code is the code # you want to find (normally a reference). A:A and 1:1 assume your rows and columns are in the first and top rows/columns but you can replace with wherever the GL and TB #s are

likehelpful

I was neck deep in work today but 2-1 it is.

like

Officially my favorite 🐠 thread

like

Is there a formula or function that can align data in the same column vertically if it’s all over the place? Like I have a TB that has account names in various columns from A - E and I want them all to line up in the same column without having to cut and paste a bunch of times if that makes sense

likehelpful

Parse out the first number after the second “/“ for a column (let’s say column A, rows 1-10) of dates formatted in MM/DD/YYYY

like

You can use =indirect("January!A1")

like

Where did you learn all this?! Time or actual course?!

like

Short cut for pivot table?

like

Formula to take the GL numbers above and paste below until another GL Number appears and for it to repeat

like

@a1 or text to columns fixed

like

I think you’re looking for the INDIRECT function @PwC 3

like

Related Posts

like

I have a 3rd grade student who has been extremely disrespectful and defiant. I’m at my wits end... she says “no” or “why should I?” to everything she is asked to do and has a terrible attitude. Help!

like

Looking for an internal transfer to Amazon Advertising. How is the WLB, learning and industry opportunities?

What is the best waxing or threading place for eyebrows in Northern Virginia?

like

Anyone have intel on the culture/LGBTQ presence at BAE Systems (Nashua NH location) and southern NH/north of Boston in general? I’ve worked for very diverse and inclusive defense contractors and for ones still in the stone ages, and realize there is a wide variety.

like

Transferring from audit to FDD. How do I study for the interviews? Is there any resources I can use to pass the case interviews? What are some types of questions for the behavioral interviews?

like

Hi Fishers,

DM me if anybody want to file ITR for last year, I will help you get good refund amount for whatever tax paid or deducted from salary during the financial year.

Johnson & Johnson Mondelēz International I am working as a Territory Manager with Cars24 from around 11 months and in total of around 6 years of working experience as a Team Leader. Data Management # Channel Sales # General & Modern Trade # FMCG # New Product Launch # Marketing & Finance. Looking out for a organisational and cultural change to enhance my productivity and learning.🙏Britannia Industries Limited ITC Limited Johnson & Johnson Nestle Dabur India Colgate-Palmolive Pepsico Marico Hindustan Unilver Limited Mondelēz Internatio

like

Question! I’m a unemployed med student and I want to get my rental arbitrage business started. I formed my LLC last yr, no income coming in or business credit yet. How can I start getting business funding ??

like

Is IRS late payment interest deductible on a partnership return? I know the penalties are not deductible but thought the interest was. Trying to find the support for this in the code/regs and my Google skills are failing me.

like

Please like so that i can dm others thanks

like

Hello Sharks Kindly let me know my in hand salary based on below structure.

Post Photo
like

Please suggest: 21 LPA india or long term Us Onsite

like

Hello fish and sharks! I am an analyst at a pharma company in NYC. I’m trying to pivot to consulting specifically in Risk Management. My boss and I talked about consulting firms and he said how consultants work all the time outside of working hours.. is it really that bad? And is the compensation not great compared to how much you put in?

like

Hi Fishes,

Purely from salary growth perspective which is most lucrative data science domain or AI domain
1. Computer vision
2. NLP
3. Machine Learning engineer

like

Current IBD Analysts/Associates/maybe VPs- when you have a networking chat with an undergrad, do you typically have a hit list of topics to discuss? I have a general list, but want to hear everyone’s

like

What is a good hourly pay rate for a full time rad tech in cardiac cath? I just got offered a job and I’m curious if the offer is fair.

like

Hi all, what is the interview process in cgi? I'm 5.8 yrs experienced dotnet resource, currently working in cognizant. Received a mail related to interview in cgi so, would like to know how many rounds, will they be difficult like that? Also, if any tips to clear

like

What's the salary range for manager in SAMA practice (Strategy, analytics and M&A). I've HR round so wanted to know the minimum and maximum range for manager.

like

Fishes please like my post so that I can DM

like

Additional Posts in Excel Genius

A lot of the work at my new employer is quite redundant and can be automated via macros. What are some of the best resources to learn vba?

like

At my old firms when I would be using excel and modeling using the font schemes (blue, green, black for hardcode, link, and formula) I could use the “control shift ;” to shift through the schemes. My new firm doesn’t do this. Does anyone know why the shortcut would be different and how I can add it back?

like

What are your top Excel ninja tricks? Ones that few people know...

like

I have a field in excel that represents the GSID, that I want to concatenate to create a link. Example field GSID is 123-west and the link needs to be www.website/123_west.html for some reason using concatenate with left & right formulas to switch - to _ results in the field throwing an error. Anyone knows of a way to make this work?

like

Tips on adjusting to Mac? I’ve figured lots of keyboard shortcuts but it’s just not the same 😪

like

Last cell question. Some of my simple models are massive in file size because every tab has a last cell of ~XFD65. But I’m not using anything past ~U100. Continued.

like

Why when I refresh a query, the table where the query is loaded to just gets duplicate rows? Same with the pivot table that feeds off of that query?

like

I’m trying to go back to the basics. How do you guys structure thoughts around identifying the correlation between 2 variables? A basic example: the number of steps I take in a day and the hours of sleep I got that night.

like

[shortcut genius] how can I use a keyboard shortcut to simulate the double-click on the cross in right low corner that extend your formula only to the row you need (ctrl+d will go too far)

likehelpful

Hi! I have knowledge of pivot tables, vlook ups. But I'm rusty on if statements and would like more knowledge on excel formulas. I'm looking for excel classes to take online. Any suggestions?

like

I want to create a heat map of when people are unavailable with day of week as the columns and hour of day as the rows. I have start times and end times, how can I use this data to create the map?

like

What are the most typical models you all build

likehelpful

Is there any easy way to handle FedEx/ups tracking numbers? I’d like to have some formula that would just show the status in another cell.

like

Can you share the most useful formulae and built in automation features you have used with the latest version of excel?

like

I really need some help.
What shortcut inverses my
Ctrl + [ aka Ctrl + {
which jumps through a cell to the source? How do I jump back? Manually isn't practical for a 50+ tab sheet

like

Just created my first macro! Super proud of myself! #humblebrag

likeuplifting

I have three lists. Each is a list of processes. Each list represents one hierarchy level. Is there an easy way to get from three separate lists to one list ordered like shown on the right?
Thanks for your help fish!

Post Photo
like

If you follow him on LinkedIn you probably already saw this! Lol 😆 but I totally resonate with the post... 🤣

Post Photo
funnylike

How do I stop excel from changing the cell reference to a different column each time I refresh the data pull?
The spreadsheet is connected to an API data pull. Each time I refresh the data it changes the cell reference (SCD_0010) to a different cell e.g. (SCD_0127). How can I stop this?

Post Photo
like

New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
That was just a preview…
Sign Up to see all discussions
  • Discover what it’s like to work at companies from real professionals
  • Get candid advice from people in your field in a safe space
  • Chat and network with other professionals in your field
Sign up in seconds to unlock all discussions on Fishbowl.

Already a user?
Login here

Share

Embed this post

Copy and paste embed code on your site

Preview

Download the
Fishbowl app

See what’s happening in your industry
from the palm of your hand.

A phone with Fishbowl app

Send download link to your phone

OR

Scan your QR code to download
Fishbowl app on your mobile

By continuing you agree to Terms of Use and Privacy Policy.

Messaging rates may apply

Download app

Sign up for free to view this conversation on Fishbowl

By continuing you agree to Terms of Use and Privacy Policy

Already have an account? Log in

Sign up for free to continue using Fishbowl

By continuing you agree to Terms of Use(New) and Privacy Policy(New)
Messaging rates may apply

Already have an account? Log in

For account settings, visit Fishbowl on Desktop Browser or

General

Legal