find end of row in xl97

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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

Board Regular
Joined
Dec 16, 2003
Messages
127
2004MIS.xls
WXYZAAABACADAEAFAGAHAI
1
2
3WedThuFriSatSunMonTueWedThuFriSatTotal
42122232425262728293031
5
6
7601019
8401017
9
101550118085178514997
11106688361356210870
12
1315561180852785000015016
141070883614562000010887
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.
 

farnuckl

Board Regular
Joined
Dec 16, 2003
Messages
127

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
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...... :LOL:
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

Hi farnuckl:

Let us look at ...
Book1
WXYZAAABACADAEAFAGAHAIAJ
2$AH$3
3WedThuFriSatSunMonTueWedThuFriSatTotal
42122232425262728293031
5
6
7601019
8401017
Sheet10


Would this do?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
farnuckl said:
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

Board Regular
Joined
Dec 16, 2003
Messages
127
Yogi, thanks. That gives me what I need. (It'll just take me a week to figure out what it does exactly) :eek:

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

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,037
Messages
5,856,966
Members
431,841
Latest member
jaybeem

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top