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.

 

[mlandry62]

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...?

You are correct. We are comparing values from each of the separate Internal trial balances against the Consolidated Audited trial balances to ensure that we haven't missed anything.
How does TBLINK() work?

 

[Bosquedeguate]

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...?

You could ... the table version makes it a bit more dynamic.

 

[mlandry62]

You could ... the table version makes it a bit more dynamic.

I don't have a TBLINK function on my spreadsheet

 

[Anonymous1378]

Assuming your software version doesn't pre-date TBLINK, I believe it goes something like

=TBLink("TrialBalanceName","FINAL[7]","1000-GCI","18","3")

 

[mlandry62]

Assuming your software version doesn't pre-date TBLINK, I believe it goes something like

=TBLink("TrialBalanceName","FINAL[7]","1000-GCI","18","3")

Excuse my noviceness.
TBLink is not recognized which I assume was supposed to be used on the Consolidated TB
Trial Balance name I assume is the where the values are coming from
Final 7?
18?
3?

 

[Fluff]

I have never heard of the TBLINK function, are you sure it's an Excel function?

 

[mlandry62]

I have never heard of the TBLINK function, are you sure it's an Excel function?

Any other ideas?

 

[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

Making progress using this suggestion.
Now how do I combine Col A & B together?

The result needs to be 1000-GCI, 1011-GCI, 1016-GCI.
Please keep in mind the characters are not always a set of three.

 

[mlandry62]

Making progress using this suggestion.
Now how do I combine Col A & B together?

View attachment 96291

The result needs to be 1000-GCI, 1011-GCI, 1016-GCI.
Please keep in mind the characters are not always a set of three.

Getting closer however I need a dash between the account and characters. How is that accomplished?

 

[Bosquedeguate]

To combine use a formula like this in a cell

=B1 & “-“ & A 1

Produces
nnnn-AAA result