Find last filled row and generate cell reference for use in other formulae

Morning all,

I have a spreadsheet with data that will be constantly updated.
I am hoping it is possible to generate a cell reference based on the value of the last cell.
So far what I have is :
In Cell C1 :

Formula:

Please Login or Register  to view this content.

- Will find the number of games in the data set, from A10 to the end of the data (column is filled with dates).
In Cell C2 :

Formula:

Please Login or Register  to view this content.

- Will show the number of the last row used.
Now, in E5 I have :

Formula:

Please Login or Register  to view this content.

- Where 'STREAK' is a macro function searching for a run of zeros in the data set. The 'H400' is a nominal number that will change according to the updated data set.
What I am trying to do is have :

Formula:

Please Login or Register  to view this content.

, or,

Formula:

Please Login or Register  to view this content.

so the value of the range is updated according to what is in C2.
There is one more formula that would require the same 'tickle' :

Formula:

Please Login or Register  to view this content.

whereby the reference to 'H23' would be updated according to C2
Currently all of the above attempts produce a 'VALUE' error.
Many searches of the mighty google have proved fruitless trying to work this one out.

Cheers for your time
Martin

You are over complicating this.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi, Thanks for the reply.
Attached is an example sheet outlining the details of what I am (currently) trying to achieve.

The 'GAMES TODAY' value in C1 will be used in other sheets in the workbook.
The 'LAST ROW' value in C2 will be used to generate the cell reference to be used in H4 & H5
The 'CURRENT STREAK' & 'MAX LOSING STREAK' values in H4 & H5 will need to calculate based on the 'LASTROW' value generated in C2.

The formulae in H10:S400 will remain 'as is' and the calcs in H4 & H5 will ignore the excess data (0's) according to the 'LASTROW' cell reference generated by C2.

Hope this helps.

Cheers
Martin

I entered that in c2 and the result was 30 as requested.

Formula:

Please Login or Register  to view this content.

Not sure that I understand the rational behind either of the formulas however here is my attempt at a solution.
For the formula in I5:

Formula:

Please Login or Register  to view this content.

It appears to me that the formula in I4 is attempting the following: If there is a 1 in the desired portion of the column then count the number of zeros and if there isn't a 1 count the number of zeros anyway. At any rate to count the zeros only in the range between row 10 and the row indicated in cell C2 of any particular column use the formula:

Formula:

Please Login or Register  to view this content.

I would suggest that you explain to us what you are trying to accomplish with each formula and manually input the expected values for the cells in rows 4 and 5.
Let us know if you have any questions.

Try this formula as applied in cells I2 and across:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.