Running total of Occurence

[*shudder*]

I would like to be able to apply a unique number (running total) to a row if a column contains the letter A, something like this:

A 1
B 0 (or blank)
C 0 ""
A 2
C 0
A 3
B 0
C 0
A 4

This information is then pulled across to another sheet which I have already worked out the formula for, however I would like it to leave an empty display rather than an #N/A answer. The formula I am using is this:

=INDEX(Sheet1!C$2:C$119,MATCH($A2,Sheet1!$Z$2:$Z$119,0))

Any help or pointers in the right direction would be appreciated.

Many Thanks

 

[*shudder*]

I now have the formula Water Gypsy recommended in column Z on sheet1, this gives me consecutive numbers for the records.

On sheet2 I have numbers down column A, and want to pull accross the corresponding data from sheet1 in column C.

So on sheet2 it should show something like this

1 - Blue Lorry
2 - Red Lorry
3 - Yellow Lorry

If it makes any difference the data on sheet1 is mixed and could be a number, date or string. I also want to pull across data from other columns into seperate columns on sheet2.

HTH

 

[jasonb75]

The earlier suggestions should have worked,

=IF(MAX(Sheet1!$Z$2:$Z$119),INDEX(Sheet1!C$2:C$119,MATCH($A2,Sheet1!$Z$2:$Z$119,0)),"")

And the suggestion from texasalynn.

Have you tried formula evaluation to see which point it fails at?

 

[*shudder*]

My apologies Jason, late in the day and pasted formula into the wrong column.

Yours still provide and #N/A answer however texasalynn suggestion worked.

Thanks for everyones help.

Problems Solved!

 

[jasonb75]

Strange, it should work, I've not been able to make it return an error, not critical now though as you have a solution that works.

 

[jasonb75]

Realised my error

=IF(MAX(Sheet1!$Z$2:$Z$119)<$A2,"",INDEX(Sheet1!C$2:C$119,MATCH($A2,Sheet1!$Z$2:$Z$119,0))

or

=IF(COUNT(Sheet1!$Z$2:$Z$119)<$A2,"",INDEX(Sheet1!C$2:C$119,MATCH($A2,Sheet1!$Z$2:$Z$119,0))

Both give the same result, but the formula using MAX will still fail if the running total column (or the data it counts) contains #N/A.