VBA or Formula for Link/Refernce Lookup

[excelsishya]

Hi all,

I am looking for VBA or formula method for kind of Link lookup.

I am using excel 2007.

Please see the sample below

Tab1 contains BS summary each of item in column b is linked to TAB 2 Trail Balance.

BS Summary

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Calibri,Arial; font-size: 11pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 94px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td>
</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td>Balance shhet</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td>
</td> <td style="text-align: right;">2008</td> <td style="text-align: right;">2009</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td>Cash</td> <td>='Trail Balance'!C6+'Trail Balance'!C7</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td>Inventory</td> <td>='Trail Balance'!C3+'Trail Balance'!C4+'Trail Balance'!C5</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td>Intangibles</td> <td>='Trail Balance'!C8</td> <td>
</td> <td>
</td></tr></tbody></table>
I am displaying in Column C link

Here is TAB 2

Trail Balance

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Calibri,Arial; font-size: 11pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 121px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td>
</td> <td>B</td> <td>C</td> <td>D</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td>Trail Balance</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td>
</td> <td style="text-align: right;">2008</td> <td style="text-align: right;">2009</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td>Goods in Tansit</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td>R/M</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td>FG</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td>Depoists</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td>Cash in bank</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td>Customer relation</td> <td>
</td> <td>
</td></tr></tbody></table>
Accounting knowledge is used to link no excel skill.

The desired output is

Desired output

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Calibri,Arial; font-size: 11pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 121px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 61px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td>
</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td>Trail Balance</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td>
</td> <td style="text-align: right;">2008</td> <td style="text-align: right;">2009</td> <td style="font-weight: bold;">Mapping</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td>Goods in Tansit</td> <td>
</td> <td>
</td> <td style="font-weight: bold;">Inventory</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td>R/M</td> <td>
</td> <td>
</td> <td style="font-weight: bold;">Inventory</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td>FG</td> <td>
</td> <td>
</td> <td style="font-weight: bold;">Inventory</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td>Depoists</td> <td>
</td> <td>
</td> <td style="font-weight: bold;">Cash</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td>Cash in bank</td> <td>
</td> <td>
</td> <td style="font-weight: bold;">Cash</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td>Customer relation</td> <td>
</td> <td>
</td> <td style="font-weight: bold;">Intangibles</td></tr></tbody></table>
When VBA or Formula is used it should use Links as (lookup value) give look up result .


Eg Inventory in Tab1 has link from Tab2 cell c3 c4 c5, so these c3 c4 c5 cells in Tab 2 should display Inventory as shown above in any blank column right to it.

Thanks

 

[excelsishya]

Refresh

Guys any thoughts.

 

[excelsishya]

Guys,

Is this possible in excel.

often came across mapped Trial balances, instead of manually checking correctness if link lookup type is possible it makes job very easy.

Thanks