find end of row in xl97

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

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,458
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,201
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,545
Members
414,316
Latest member
ExcelLee

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