Can this formula be simplified?

[Ray1971]

Hi. I have various sheets in my workbook for clients. Columns AB:AJ is a sort of extended trial balance. Columns W:Y in one row would need specific amounts from columns AE:AF.

Each financial year end would have its own extended trial balance and I would put the year end date somewhere down column AB.

My formula in a cell in column W needs to find the date in the same row in column A in column AB and then offset to the figure in AF minus the figure on the same row in AE.

My current formula works, but I am just wondering if it can be simplified. In this formula below it is looking for the date I entered in cell A24:

=OFFSET(INDIRECT(MID(CELL("address",INDEX($AB:$AB,MATCH($A24,$AB:$AB,0))),FIND("$",CELL("address",INDEX($AB:$AB,MATCH($A24,$AB:$AB,0))))+0,255)),18,4)-OFFSET(INDIRECT(MID(CELL("address",INDEX($AB:$AB,MATCH($A24,$AB:$AB,0))),FIND("$",CELL("address",INDEX($AB:$AB,MATCH($A24,$AB:$AB,0))))+0,255)),18,3)

Thanks,
Ray

[63falcondude]

It will be difficult to say without having a sample sheet to test on.

I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

[Ray1971]

Thanks - Sample sheet attached

[63falcondude]

Try this in X24:

=INDEX(AF:AF-AE:AE,MATCH(A24,AB:AB,0)+20)

[63falcondude]

Use +18 for column W and +21 for column Y.

You can hide #N/A errors with IFERROR if you need to.

[Ray1971]

Excellent! Thank you 63falcondude.

[63falcondude]

Happy to help. Thanks for the rep!