Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Gerrym
 
Posts: n/a
Default Dynamic number of ROWS

I need to search a sheet with 79 ROWS but this can vary.
I use do the search from another sheet. I am looking for
an exact match of a value in a particular Column C so I
use MATCH(D24,C2:C79,0)
This works ok and I take care of errors with ISNA. My
question is how can I allow for the fact that the number
of rows may be different without having to manually edit
the formula.


  #2   Report Post  
Jason Morin
 
Posts: n/a
Default

Use can create a dynamic range using OFFSET:

=MATCH(D24,OFFSET(C2,,,COUNTA(C2:C65536)),0)

HTH
Jason
Atlanta, GA

-----Original Message-----
I need to search a sheet with 79 ROWS but this can

vary.
I use do the search from another sheet. I am looking

for
an exact match of a value in a particular Column C so I
use MATCH(D24,C2:C79,0)
This works ok and I take care of errors with ISNA. My
question is how can I allow for the fact that the number
of rows may be different without having to manually edit
the formula.


.

  #3   Report Post  
 
Posts: n/a
Default

This works fine, Jason,

Thank You

-----Original Message-----
Use can create a dynamic range using OFFSET:

=MATCH(D24,OFFSET(C2,,,COUNTA(C2:C65536)),0)

HTH
Jason
Atlanta, GA

-----Original Message-----
I need to search a sheet with 79 ROWS but this can

vary.
I use do the search from another sheet. I am looking

for
an exact match of a value in a particular Column C so I
use MATCH(D24,C2:C79,0)
This works ok and I take care of errors with ISNA. My
question is how can I allow for the fact that the number
of rows may be different without having to manually edit
the formula.


.

.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Selecting a large number of rows, but not all Katherine New Users to Excel 4 January 28th 05 12:54 AM
sorting rows wich can vary in number Bart Excel Discussion (Misc queries) 4 December 14th 04 07:59 AM
increasing the number of rows of a worksheet beyond 65536? ibu Excel Discussion (Misc queries) 2 November 26th 04 09:56 AM
Dynamic Column VlookUps Based on Week Number TLK40us Excel Worksheet Functions 3 November 14th 04 04:33 PM
How do I limit the number of rows in an Excel worksheet. laforge27 Excel Worksheet Functions 1 November 4th 04 02:42 AM


All times are GMT +1. The time now is 09:42 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"