VLOOKUP

[mlandry62]

Struggling to get VLOOKUP to work with multiple workbooks.

Second screenshot is the master spreadsheet, which by the way there are 10 of these!
The first sheet looks at the account numbers in Group 4110 to compare against Col B numbers and return the value from Col K to the Visual value.
Hopefully I've explained myself properly.

Issue I have is Multiple Trial Balances (2nd Screenshot) and account numbers in first screenshot have extensions that relate to the multiple trial balances.

Any assistance or advice would be greatly appreciated.

 

[Bosquedeguate]

can you send the formula that is not working?

Stripping off the end of your Sub-Group numbers is easy. Then the VLOOKUP should be easy to do. But, which Sheet are you extracting Balances from?

 

[mlandry62]

can you send the formula that is not working?

Stripping off the end of your Sub-Group numbers is easy. Then the VLOOKUP should be easy to do. But, which Sheet are you extracting Balances from?

That's the issue. I need all 10 trial balances to reference hence the sub group.
Each account number with a 3 character code references one of the trial balances.

 

[Bosquedeguate]

Are your sheets named according to the 3-character code? Or is there a correspondence between 3-character code:Balance sheet name?

 

[mlandry62]

Are your sheets named according to the 3-character code? Or is there a correspondence between 3-character code:Balance sheet name?

No there isn't however I'm sure I could with some type of formula add the 3 character code to each of the 10 trial balances if that would help my situation.

 

[Bosquedeguate]

No there isn't however I'm sure I could with some type of formula add the 3 character code to each of the 10 trial balances if that would help my situation.

can you send the formula that is not working?

Stripping off the end of your Sub-Group numbers is easy. Then the VLOOKUP should be easy to do. But, which Sheet are you extracting Balances from?

Let me suggest that you create a simple lookup table that defines the relationship between 3-char code and balance sheets.
Something like below.
This table can be used to determine which Sheet will be used for the Balance Lookup.

Book2 (Autosaved).xlsm
	A	B
1	Code	Balance Sheet Name
2	GCI	Sheet1
3	ORCA	Sheet2
4	MFG	Sheet3
5	KLK	Sheet4
6	…	…
Sheet3

 

[mlandry62]

Let me suggest that you create a simple lookup table that defines the relationship between 3-char code and balance sheets.
Something like below.
This table can be used to determine which Sheet will be used for the Balance Lookup.

Book2 (Autosaved).xlsm
	A	B
1	Code	Balance Sheet Name
2	GCI	Sheet1
3	ORCA	Sheet2
4	MFG	Sheet3
5	KLK	Sheet4
6	…	…
Sheet3

Should I assume that all the spreadsheets have to be on one workbook including the table you are suggesting?

 

[Bosquedeguate]

Should I assume that all the spreadsheets have to be on one workbook including the table you are suggesting?

Not necessarily. The Balance Sheet column would change slightly if this is the case. These workbooks would need to be opened.

I added the filename (Workbook name), as well as the range for each workbook.

Code	Balance Sheet Name
GCI	[Workbook1.xls]Sheet1:$A$1:$K$10
ORCA	[Workbook2.xls]Sheet2:$A$1:$K$10
MFG	[Workbook3.xls]Sheet3:$A$1:$K$10
KLK	[Workbook3.xls]Sheet4:$A$1:$K$10
…	…

 

[mlandry62]

Not necessarily. The Balance Sheet column would change slightly if this is the case. These workbooks would need to be opened.

I added the filename (Workbook name), as well as the range for each workbook.

Code	Balance Sheet Name
GCI	[Workbook1.xls]Sheet1:$A$1:$K$10
ORCA	[Workbook2.xls]Sheet2:$A$1:$K$10
MFG	[Workbook3.xls]Sheet3:$A$1:$K$10
KLK	[Workbook3.xls]Sheet4:$A$1:$K$10
…	…

Thanks for helping an extreme novice! I'll see if I can put it all together.

 

[Anonymous1378]

What would be the point of this? To ensure your consolidated trial balance matches the total of your trial balances?
Why don't you just use a TBLINK() to each of the TBs...?