Using Vlookup to bring amounts from other workbook

[JesseMS]

Hi,

I'm trying to create a template that works on bringing in information from various accounting programs, specifically a trial balance. The template has a link that changes the input file (using macros), and I'm using Vlookups to bring across the relevant accounts. The trial balance is set up like this:

Account Code	Account	Account Type	Debit - Year to date	Credit - Year to date	30 Jun 2021
200	Milk Income Current Year	Sales		834,954.79	(722,441.85)
210	Livestock Sales - Calf	Sales		8,867.00	0.00
211	Livestock Sales - Cull	Sales		32,110.60	(27,994.99)
212	Livestock Sales - Other Livestock	Sales		0.00	(91,750.00)
260	Incentives	Other Income		2,536.98	(63,347.16)
274	Fuel Tax Credits Received	Other Income		734.00	(1,621.00)

So for instance, using Vlookup in the template to pull through the Milk Income Current Year: Credit - Year to Date Amount:

Milk Income Current Year

=IFERROR(ROUND(VLOOKUP(B31,'[File.xlsx]Trial Balance'!$B$6:$E$80,4,FALSE),0),0)​

If I wanted to pull through multiple values, eg. All 3 livestock sales and combine them into one cell in the template, how would I do that? I want to leave the Trial balance as is, as many users will be using the template, and I want to reduce any extra steps if possible.

The Account Codes change per file, and some of the Account Names differ on each file as well. My solution to that so far has been to change the account name in the template Vlookup to match the Trial Balance.

Currently using Excel 2013, trying to get management to upgrade us to 2019.

First time posting, let me know if I should be asking this differently/adding anything!

 

[Peter_SSs]

Welcome to the MrExcel board!

let me know if I should be asking this differently/adding anything!

Only small thing is that sample data is better for helpers if you can use XL2BB

If I wanted to pull through multiple values, eg. All 3 livestock sales and combine them into one cell in the template, how would I do that?

Would something like this suit?

JesseMS.xlsm
	A	B	C	D	E	F
1	Account Code	Account	Account Type	Debit - Year to date	Credit - Year to date	30-Jun-21
2		Livestock Sales			40,978.00
Sheet1

Cell Formulas
Range	Formula
E2	E2	=ROUND(SUMIF('[File.xlsx]Trial Balance'!B$6:B$80,B2&"*",'[File.xlsx]Trial Balance'!E$6:E$80),0)

 

[JesseMS]

Welcome to the MrExcel board!


Only small thing is that sample data is better for helpers if you can use XL2BB


Would something like this suit?

JesseMS.xlsm
	A	B	C	D	E	F
1	Account Code	Account	Account Type	Debit - Year to date	Credit - Year to date	30-Jun-21
2		Livestock Sales			40,978.00
Sheet1

Cell Formulas
Range	Formula
E2	E2	=ROUND(SUMIF('[File.xlsx]Trial Balance'!B$6:B$80,B2&"*",'[File.xlsx]Trial Balance'!E$6:E$80),0)

Thanks! I was stuck on vlookup, but that works great!
My IT guy is pretty cautious about letting users install their own software, but I'll see if I can convince him for XL2BB!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

.. and good luck with IT!