What’s the best way to summarize multiple amortization schedule. I have each schedule on a separate tab. On a master sheet can I do something where I can input a specific month and show my ST/LT ...

like
Posting as :
works at
You are currently posting as works at

It sounds like VLOOKUPs or INDEX MATCH MATCH (best practice) might serve you well here. You can put a specific date into cell A1 of the master sheet for example. Then VLOOKUP(A1, Range on ST Sheet, # of rows on ST sheet, 0) to pull data for that month from the ST sheet. You can do something similar for the others.

like

...cont. and outstanding loan balances.

To build on this, you can also reference the exact tab names in a row or column. SUMPRODUCT, INDIRECT, and SUMIF can pull the corresponding data by month as stated above from each table at its respective tab.

See below -
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"rng"),criteria,INDIRECT("'"&sheets&"'!"&"sumrng")))

We’ve used this same formula before for the exact same reason this past year for a client with around 100 separate amortization tables. Also, if the data ranges are transposed, you may want to use OFFSET in there as well.

Related Posts

Is anybody hiring copywriters right now? I am very cool and smart!

like

Did anyone finished the final interview for Walmart Software Engineer III position?

like
like

Used to work at JPMC in a compliance role. Left for a few years. Interviewing for a WM KYC Onboarding Officer Associate position but have yet to speak to HR. Have interviewed with hiring manager and interviewing with ED today. The position wants 5 years of experience. Worried about the salary. Anyone know the range? Glassdoor and other places have it all over the place

like

Started with the firm in April. What do you think is appropriate to ask for a raise at EOY? What's the average non promotion raises?

like

Anyone else hear that post-CRT partners will get fired this year in commercial advisory? How the hell does a partner get fired....

like

Any book recommendations for someone looking to get into a Product Owner / Manager role?

like

Anything good I can spend Delta Skymiles besides airfare? (I’m not really planning on flying anywhere soon).

like

Hi, anyone else end up in civil litigation and not really understand how you ended up here or why you actually kind of love it here? Even though you have to fight it back like a deeply established weed so you have some semblance of an individual life?

like

32 M in the Bay Area and bored in quarantine. Any F looking to chat?

Saw a post in the confession bowl so I wanted to check this bowl out. Any hot, smart, rich and single guys in this bowl?😁

funny

ZS hiring for multiple roles... DM in case of any referrals!

like

2 in 1 laptop needed for MBA? Helpful at all?

like

Has anyone been terminated from Novartis?

like

Any cover letter or resume advice from recruiters?

like

Been asked by a client to apply for a GS14 term position but I have a few questions/concerns.
1. Current salary is 140K with ~10K variable comp. If I wanted to at least match, it would require me to be in Step 5/6— is that possible/likely or would I have to start at Step 1?
2. How often have you seen term positions converted to permanent? I’m considering turning this offer down due to this.

like

Anyone able to see the September month salary slip in the ADP portal. Any idea when it will be uploaded?

like

Any good books to read this winter break? On leadership, digital, or any autobiographies?

like

Hey Fishes, Can I rejoin Infosys again. I left Infosys in dec 2020. If yes, please refer me for React Js. YOE - 5.5Infosys

What are some good practices during the sales phase of a potential project to execute before the contract is signed? Seems obvious but would love some ideas on standard operating procedures that help with seamlessly handing over a signed contract to the account team. For example, speaking to the potential client explicitly about their role in staying on schedule, reviewing scope creep consequences, etc.

like

Additional Posts in Excel Genius

How do I compare two sheets to make sure they are identical?

like

Does anyone have any tips/tricks for knowing where to plug +1/-1 in a large table with totals across the y and x axis that contain un-rounded numbers? Every time I fix a row I throw off a column or my check figures to the true data source.

Post Photo
like

Anybody use a personal computer for work? How's your experience been, any horror stories/watch-outs?

like

Hey, I have been tasked with teaching some of my coworkers excel from the very basics to more advanced. Does anyone have recommendations on the skills to teach them about? Thank you all in advance!

helpful

Has anyone had an issue with index match and lookups returning NA when trying to manipulate data from a password protected workbook?

After I set a print area, how do I showcase the page 1 - page x in the excel sheet and have the background be grey?

Will need to migrate to office 365 soon. What’s the difference for excel from the 2013 version?

likehelpful

I’m calculating a bunch of totals and averages by category on one worksheet by filtering data in different categories from another worksheet. However, when I change the filters, the previously..(cont)

Timesaver: If you’re working on a file saved on the cloud (teams, share point, etc.) and need the URL to communicate out, tap FILE -> INFO -> COPY PATH and the url will be on your clipboard. Pre-established sharing privileges of orig folder will apply. Hopefully this saves you time reshuffling open windows! Bonus: Same on standard MS suite files.

likesmart

This is dumb, but. How can I pull and average the numbers in column b associated with the 4 values in column a? So, (sum of all As/total number of As) without filtering?

Post Photo
like

Our Data is only showing when something has changed, is there any way to make excel fill in the values?

Post Photo
like

What’s the symbol that you can type in a cell so you can input a text, but that text would not appear in the spreadsheet? I want to describe the formula without using a text box, which uses more space.

The equivalent of this is in SQL, you can use the apostrophe in the beginning of and end of a text. That text will not affect the function of the query, but that text can be seen if you proofread the query.

like

Best free data sites to tap into to build a city analysis model? Basically I want to "discover" cities with certain dimensions, like: population, elevation, climate, and proximity to other things (ski resorts, particularly)?

like

What’s the keyboard shortcut to get into the “insert options” dialogue when you’ve inserted a new row with Ctrl+Shift+Plus?

like

Hi there,
Which Excel courses you recommend for a beginner?

like

Can dates be grouped just by Month-Year in pivot tables on a Mac without having the year as its own dimension?

Post Photo
like

Vlook up isn't recognizing a couple of cells even though it's feeding off a pivot and when I check with an " if =" it comes up as being the same.
Any advice on what I need to check?
Thanks

like

Hi All,
Looking to automate a manual process for the client and thinking of creating macros for with VBA. Has anyone done anything similar?

In the process of learning but wanted to reach out for any tips, tricks and advice anyone may have.

Thanks I’m advance.

like

How can I extract just the year into a new cell from a “yyyymmdd” cell (labeled PD here) that isn’t recognized as a date? I basically need to sort this data by year. Thanks in advance!

Post Photo
like

Would anyone be willing to teach me vlookup?

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

Scan your QR code to download
Fishbowl app on your mobile

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