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

My roommate has been illegally subletting her room in our apartment (no consent from landlord and up charging as well).

If our landlord/leasing office finds out, could I be evicted as well?

like

So.. i tend to get too in the weeds and stumble around a lot on behavioural interview questions. I’m very bad at them. What are some good resources to prepare? When I google, I get crappy ehow article

like
like

Hi
I am working as system engineer and have exposure/skills across multiple technologies ranging from Cloud, DBs, Linux - Ubuntu/Redhat, Windows- 7/8/9/2012/2016/2019, cisco Network switches/Routers, VDIs, Virtualization, L7 VPN technologies & end point security etc. Additionally i have sound knowledge of Cybersecurity,VAPTs etc & hold few good certificates.

I am actively applying for jobs in Delhi NCR location but not getting enough calls for interviews. Need suggestions please. YOE: 3

Thx.

like

I am currently expecting, have informed my team. Since the organisation is in hybrid mode, when discussed earlier i was told that it is flexible in terms of wfh depending on my health condition. But now my department wants me to be in office twice a week irrespective of the prior discussion. Still half of the office is on hybrid mode. Is it a fair ask?

Anyone here from EY ? How’s interviewing there? Help EY

likefunny

Any idea when our revised compensation letter will be available ? It didnt come on 15th

Penn or draftkings?

like

Redeemed my recognition points for a portable air conditioner. Hope it's effective!

like

$530k HHI, $250k cash on hand, $1.2M investments/retirement. Plan to buy $800-900k house as our first home, but thinking of the best approach given rates today. My thinking is that we (i) put $200k+ down and get a 5/1 ARM. (ii) put everything we save moving forward toward paying down the principal (after maximizing retirement accounts). Should be able to do $100k per year. (iii) If rates go down below 4.5ish % for 15 year fixed, refinance and stop aggressive payment. Thoughts on this approach?

like

Hi, Is cab facility available for daily pickup and drop for Chennai DLF location. Or is it provided only for people working in shifts.
Any input is appreciated. Received offer but work location is very far from home.

like

Hi. I’m looking for job opportunities in sales . YOE - 7 . I’ve worked with real estate and edtech Industry. Experience in handling team

like

I just received notification my laptop shipped! 😱 I feel like a kid in a candy store! I start my new position in one week and am equally petrified and excited.

likeuplifting

Oh lawd gimmie the strength

Post Photo
funnylikeuplifting

What is the process of onboarding in ADP? Whether virtual or physical? Do I have to register on any portal?

What would be the designation and salary to be expected, when joining ADP with 12+ YOE as an UI developer, with current CTC of 13L

How long before joining ADP Initiates onboarding process . My HR mentioned they will call one week before joining and share all details.

Can anyone at Point B give some insight into the annual salary review process? I am confused by the answers I've gotten when talking with recruiting. Do you get an annual compensation increase and equity allocation every year or does compensation only go up when you are promoted?

like

How did you make friends when you moved here?

VMLY&R has openings in every department! DM me with your information/ resume to be considered.

like

Additional Posts in Excel Genius

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

Hello. Quick question about formatting. I have an excel sheet with bunch of negative numbers, but some are percentage values and some are dollar values. Is there a way to make all of them have a parenthesis for being negative while keeping them still percentage and dollar value separately or do I have to target them separately?

like

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

Does 2-finger touchpad scroll not work on Excel sheets anymore? I used to be able to swipe quickly on my laptop's touchpad to navigate sheets but now it doesn't work. Based on the handful of posts I've read online about it, the problem seems unresolved. Has anyone dealt with the same issue and fixed it?

Any book recommendations on excel modeling for real world business applications?

like

Anyone have an app or Excel spreadsheet they use to keep track of utilization? We just switched systems and our new system doesn’t give us a real time balance of our utilization rate (cont)

Does anyone know what purple tables mean in Excel?

I know about green and orange, but never saw purple before and trying to figure it out.

likefunny

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)

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

Hey fishies, looking for some help with the below

I have 4 lots of data categories which are assigned a code in the adjacent cell
Eg:
Cell A2= 'xyz' Cell B2 = 'w1'
Cell C2= 'ghi' Cell D2 = 'x1'
Cell E2= 'plm' Cell F2 = 'y1'
Cell G2= 'mhn' Cell H2 = 'z1'

The combination of the 'W','X','Y' and 'Z' codes identity a specific trend
I am extracting data which includes multiple combinations of these codes. What is the best way to show frequency of the codes against the category it is assigned to

like

Recently made a job switch and have been using a lot more excel. Any free resources to help build a solid foundation / fee based courses to help improve my skills?

like

Hi excel geniuses, how do I quickly copy info from one cell to blanks for same if. Semi below I have products and names, for the blank fields w same product how do I quickly copy for the rest of the blank cells?

Post Photo

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

likehelpful

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

like

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

Post Photo
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

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

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

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

I’m verrrrrry new to excel and am going crazy because is there not a way to “clip” text in cells like you can in google sheets? I read about a “fill” option, but then noticed that cells that aren’t filled just repeat text. 😂🤯

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