Forum Discussion
Vlookup Working but only till Row 55, at row 56 stops working
I have used Excel for many years, but have exhausted my knowledge on this one.
The worksheet that I am trying to fix is labeled "100.01". On "100.01" the first 55 instances are pulled from "General Ledger" but not the 56th.
The same error occurs on every tab if more than 55 entries using a particular code are entered on the worksheet labeled "General Ledger". (for example, if there were 56 entries using "400.01" on the "General Ledger" tab, then worksheet "400.01" would pull entries up to row 55, and cease (error) at row 56)
The formulas do not appear to change between rows 55 and 56 on "100.01", and neither do the cell formats. I don't see a change in formatting from one row to another on "General Ledger" either.
I am simply out of debug ideas on this one.
The excel file is large and has a great number of formulas. It's calculation time every time a change is made is extensive, and it takes a long time to save, open, and close the file.
I have attached the excel file.
Any help would be greatly appreciated!
- Detlef_LewinSilver Contributor
Tamara,
you have over complicated your model.
Make use of Excel tables, Pivot tables and Power Query.
Input would be "Chart of Accounts" and "General Ledger" (without the formulas). Output would be a Pivot table in "Financial Statement" layout.