I need to find last column thet contains data and first column that has data...

[CRIMEDOG]

I have range AllData that has 4 columns of identifying information - then columns with date headers that have input data
I want to find the last date there was data input and the first date. The dates are not incremental and not every column has input - 400-500 rows
for the last date:

Please Login or Register  to view this content.

For the first date:

Please Login or Register  to view this content.

where 2 is the row with the reference sheet info
InvoiceData! is the data sheet.
THIS WOULD WORK IF THE ROWS WERE THE SAME.

So I added a column on the reference sheeet with:

Please Login or Register  to view this content.

which give the row # on the InvoiceData sheet.

NOW : how to use indirect in the first two formulas to replace 2 with data from O2?

[dilipandey]

HI CrimeDog,

Please upload a sample workbook to support your query. Thanks.


Regards,
DILIPandey
<click on below * if this helps>

[CRIMEDOG]

This is what I tried:

Please Login or Register  to view this content.

Keep getting #ref error
I enter the formulas as array ([ctrl][shift][enter]
without the indirect references they work perfectly.
The value in )2 is 2 - so the indirect should result in exactly the same formula...??

[CRIMEDOG]

Here is a sample
As you can see the row number is generally the same until row 103 on Invoicedata!

[dilipandey]

Hi CrimeDog,

May be I am not getting it completely.. can you manually type your expected results and provide a brief logic ?

I just need to see your final result - no supporting (column / row / calculations) required. Thanks.


Regards,
DILIPandey
<click on below * if this helps>

[CRIMEDOG]

In the array formula, I use 2:2 - And it works. I want it to substitute the number from column O.
The main issue is: on the data page - the units aren't always on the same row.
So the array formula should return 12 which it does with 2:2 but I need it to use o2:o2.