Automated Lookup

Hi,

I require a formula that will help me populate T account headings.

I have a data sheet which has a number of GL codes in column G. What I would like is a formula on the “GL Accounts” sheet to self populate unique codes (similar to an advanced lookup with unique fields only).

Then – the main part if the above can not be done (I call always do the filter) is once the list is established on the “GL Accounts” tab I would require the T account headings to self populate.

e.g. The first 2 account looks for the first GL account, then the 2nd T account looks for the 2nd and so on and so on

The fomrula would need to go in the red highlighted cells

Can anyone help please?

Hi, that unique code worked thanks.

Can a formula for the red cells be achieved or should I accept its manual?

The reason being if I add new T accounts I want to be able to copy and past one already there and only manual reference as few cells as possible

Hi,

I didnt have the full picture before but now understand that there will be a lot (hundreds) of transactions I need to account for. (I only knew this after I built my model). Previously I had a handful of popular accounts which took 50 entries in each (50 rows) and then a few other accounts with say only 5 rows.

What I was thinking is can the fomrula to be adapted to include an indirect formula where I have a seperate tab for each GL code (like in the attached example)?

I have approx 30 accounts so this would mean the file size is large meaning the spreadsheet be slow but I can see no other way of achieving this.

Ok - I will go, here goes

Hi,

This seems to do it yes! I will test this fully though and get back to you before I mark as solved

Hi,

Your formula worked just how I wanted to thank you!

I also need to include a 2nd (and final) data set if this is possible.

The data2 set that I need is in a completley different format to the first set.

The data2 sheet has individual rows with each row having to posting entries e.g. in the example the first row is £494.60, this needs to be debited to 213000 and credited to 500000

I have highlighted in red what ideally I would like the code to perform.

Can this be achieved?

Hi, you were spot on about having them in two different tables - its thr right thing to do!

My model is evolving nicely

I do have a slight problem with the lookup on the 2nd data set.

On the T Account sheets:
213000
500000
662200

Within the Cell Range columns (AC:BB) it should pull data from the “Data 2” sheet. Currently it has these array formulas:
Column AG

=IFERROR(INDEX('Data 2'!G$19:G$99,SMALL(IF(('Data 2'!$M$19:$M$99=$B$2),ROW('Data 2'!$M$19:$M$99)-MIN(ROW('Data 2'!$M19:$M$99))+1),ROWS($1:1))),"")

Column AT

=IFERROR(INDEX('Data 2'!G$19:G$99,SMALL(IF(('Data 2'!$M$19:$M$99=$B$2),ROW('Data 2'!$M$19:$M$99)-MIN(ROW('Data 2'!$M19:$M$99))+1),ROWS($1:1))),"")

However the problem is that its returning a barcode incorrectly and I am unsure why.

e.g. on the sheet 500000, cell AT7 this should be test1 barcode
e.g. 2 – on the sheet 552200, Cell AT7, this should be test2 barcode

Any ideas?

I have uploaded the latest model - its the yellow cells on the account sheets

I have included new data on the data sheet2 tab and I still get problems.

On the data2 sheet i have 2 acocunts (251200 and 241100) but the entries are duplicating.

Very grateful for this! The formula worked as expected!! Version 5 already, it will be V100 before long!

Hi,

I am worried about how big it is becoming! I think the design will be ok for a couple of reasons... When it goes live it will have a limited amount of accounts, only approx 15 which should help the file size. In the sample data I have there are 15 but once I have actual data it will be reduced.

Then there is a requirement to see the data in the T accounts mainly because I need to see the accounting and be able to track it through differenent accounts.

It should only be completed on a monthly basis anyway to ensure everything is reconicled so if its built and then processes for 10 mins then that will be ok.

Thats what I am telling myself anyway! Ive spent too long to go back now lol

Thanks for the advice on this, in the past ive got myself in lots of boxes and wasted alot of time!

What I am planning to do next is get a formula that will create a unique list of accounts based off three datasets. Then when I have this I will have each of the tabs linked to that sheet (where you put the account code on the T account sheets cell B2).

Then finally run a macro to run a code which will rename the sheet based on the value in B2.

Thats the plan, unsure if it will work or not