Simple Row Count for Table

[Vintage79]

Hello All,

I would like to create a simple row count that can return a 2 digit number. I have a table of data, and in the first column, I would like there to be a reference number:

Excel 2010
	B	C	D
4	Ref No.	Day	Date
5	Ref893 01	Saturday	10-Mar-18
6	Ref383 02	Saturday	10-Mar-18
7	Ref109 03	Saturday	10-Mar-18
Sheet1

<p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Test</p><br /><br />The first part of the reference will be taken from elsewhere on the sheet, so I just need to add in the 01, 02, 03 etc. The simple part is that the numbers will be sequential, so I just need a way of counting how many rows down the row is from B4. So, what's the best way to do this, and return a numerical count?

Thanks in advance for any assistance!

 

[Peter_SSs]

Something like this, copied down?

Excel Workbook
	B	C	D	E
4	Ref No.	Day	Date
5	Ref893 01	Saturday	10-Mar-18	893
6	Ref383 02	Saturday	10-Mar-18	383
7	Ref109 03	Saturday	10-Mar-18	109
Ref No

 

[Vintage79]

Thank you Peter! The formula =TEXT(ROWS(B$5:B5),"00")) works perfectly for me.

I've noticed that there seems to be an issue with adjusting formulas once they are in place on the table. For example when I enter a new date in D8, a new row is entered into the table, and then formulas self-complete, which is fine. But then the font in B7 gets returned to the default setting (Calibri 11pt in my case).

The only solution I have found, is to start a fresh table each time, on a fresh worksheet, and enter everything from scratch. Is this common with tables?

Thanks again for the help!

 

[Vintage79]

Ok, I think I have found the issue!

Let's go with this example:

Excel 2010
	B	C	D
4	Ref No.	Day	Date
5	W2 / 01	Sunday	11/03/2018
Sheet1

<p style="width:3em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Test2</p><br /><br /> Formulas:
B5: =IF($C$2="","",("W"&$C$2&" / "&(TEXT(ROWS(B$5:B5),"00"))))
C5: =IF(D5="","",TEXT(D5,"dddd"))

So, when I enter a new date in D6, the new row is created, but the formula in B5 gets changed to =IF($C$2="","",("W"&$C$2&" / "&(TEXT(ROWS(B$5:B6),"00")))) and the formula in B6 becomes =IF($C$2="","",("W"&$C$2&" / "&(TEXT(ROWS(B$5:B7),"00"))))
It worked fine when I tested it before, but this seems to be the reason why cells are returning to default fonts. How can formulas change? I haven't seen this problem before!

 

[Peter_SSs]

Sorry, I didn't realise that you had a formal table. Try this in B5, adjusting 'Table1' to your table name if required.

=IF($C$2="","",("W"&$C$2&" / "&(TEXT(ROWS(Table1[#Headers]:[@[Ref No.]])-1,"00"))))

 

[Vintage79]

That works perfectly. Thanks again for the help Peter, it's deeply appreciated!

 

[Peter_SSs]

That works perfectly. Thanks again for the help Peter, it's deeply appreciated!

No problem - glad to help.

 

[Vintage79]

As I am new to tables, I have a small query that is slightly related, and didn't think it warranted a new thread:

Is it possible to refer to the column of a table by it's number or position, rather than by name? This would be useful in a case where the header of a column might change.

=TableName[2nd Column] something like this is what I mean. Or maybe even using the cell letter: =TableName[D:D]. The idea is to refer to the data below the header of a certain column.

 

[Peter_SSs]

If you wanted to refer to, say, the third column then INDEX(Table1,0,3)
For example, to return the maximum value from the third column =MAX(INDEX(Table1,0,3))

 

[Vintage79]

Thanks Peter!