find end of row in xl97

[farnuckl]

how can i find the end of a row in excel97?

 

[Yogi Anand]

Hi farnuckl:

What do you want to find at the end of the row ...

1. the column number of the last entry

2. the actual value of the entry at the end of the row

and please tell whether the entry is

3. numeric, or text, or could be either numeric or text

 

[farnuckl]

2004MIS.xls
	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
1
2
3	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Total
4	21	22	23	24	25	26	27	28	29	30	31
5
6
7	6	0	1			0						19
8	4	0	1			0						17
9
10	1550	1180	851			785						14997
11	1066	883	613			562						10870
12
13	1556	1180	852			785	0	0	0	0		15016
14	1070	883	614			562	0	0	0	0		10887
15
jan

I want my coversheet to find the end of the row to roll-up the month end data. I want to automate the coversheet so it adjusts for the varying days in the months. That said the answers to your questions are:

1&2. i suppose the column will be all i need to get the reference for the coversheet
3. either numeric or text depending on the row.

 

[Aladin Akyurek]

...
1&2. i suppose the column will be all i need to get the reference for the coversheet...

What is the result, given your exhibit?

 

[farnuckl]

all i need it to know that the totals for this month are in column AH.
Depending on the month it could be AG or AF.

 

[Brian from Maui]

Define BigNum as 9.99999999999999E+307

For text and/or numeric

=INDEX(AH:AH,MAX(MATCH(bignum,AH:AH),MATCH(REPT("z",60),AH:AH)))

Although I'd wait for Aladin......

 

[Yogi Anand]

Hi farnuckl:

Let us look at ...

Book1
	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
2														$AH$3
3	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Total
4	21	22	23	24	25	26	27	28	29	30	31
5
6
7	6	0	1	0								19
8	4	0	1	0								17
Sheet10

Would this do?

 

[Aladin Akyurek]

all i need it to know that the totals for this month are in column AH.
Depending on the month it could be AG or AF.

How about:

=SUBSTITUTE(ADDRESS(1,COLUMN(INDEX(jan!3:3,MATCH(REPT("z",255),jan!3:3))),4),"1","")

 

[farnuckl]

Yogi, thanks. That gives me what I need. (It'll just take me a week to figure out what it does exactly)

Aladin, yours works for the january page but it needs to be generic so it can work for all the months.

 

[Yogi Anand]

Hi farnuckl:

In response to your PM about my formulation ...

Hi farnuckl:

In response to your PM regarding my formulation ...

The REPT("z",255) is to create (for lack of better words) the largest possible text entry that any cell in row 3 could have. The MATCH function in trying to match this entry against all the entries in row 3 after failing to match it reports the last text entry in row 3 -- thus giving me a number to correspond with the column that houses the entry that resulted from the use of the MATCH function.

Then to convert the number to its letter counterpart for the column I used the ADDRESS function.