Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 30
Default NUMBER OF ROWS IN A DYNAMIC RANGE

HOWDY ALL, i REALLY APPRECIATE ALL THE HELP YOU PROVIDE.
I NEED TO LEARN HOW TO CONTROL THE NUMBER OF CELLS THAT WILL DISPLAY THE
DROPDOWN LIST IN A DYNAMIC RANGE. CURRENTLY:

I HAVE A NICE WORKBOOK SET UP BY THE YEAR OF AN AUTO CONTAINING TWO
WORKSHEETS, THAT REPRESENT EACH AUTO BY A SPECIAL NUMBER. EACH AUTOS SHEET
CONTAINS DESCREPENCIES FOUND WITH THE VEHICLE; EACH DESCREPENCY GETS IT'S OWN
SPECIAL NUMBER.
CURRENTLY I AM SET UP WITH A DYNAMIC RANGE DROP DOWN FOR DESCREPENCIES, AN
AUTO DATE INSERT, A NAMED RANGE DROP DOWN FOR INTIALS AND A SEQUENTIAL NUMBER
WORKSHEET IN ANOTHER WORKBOOK THAT IS HYPERLINKED TO THE COLUMN WHERE THE NEW
DESCREPENCY NUMBER GOES. HERE'S THE CODE:

IN MODULE 1 I HAVE:

Option Explicit

Sub Workbook_Open()
Columns("B:B").Select
Range("B3").Activate
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="WAIVER%20NO.xls", _
TextToDisplay:=""
End If
Else
End If
End Sub

NO SHEET 1OR2

IN SHEET 3 (LISTS)I HAVE:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Columns(1).Sort Key1:=Range("A1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub

IN SHEET 4 (86x36236; an auto number)
I HAVE:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer
Set ws = Worksheets("Lists")
If Target.Column = 3 And Target.Row 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("Na meList"),
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("A" & i).Value = Target.Value
ws.Range("NameList").Sort Key1:=ws.Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If
End Sub

I DID INSTALL A COMBO BOX AND SET THE ROWS BUT IT HAD NO EFFECT??

SO, AGAIN, i NEED TO LEARN HOW TO CONTROL THE NUMBER OF ROWS THAT WILL
DISPLAY THE DYNAMIC LIST; CURRENTLY IT WILL ONLY SHOW THE DROP DOWN UP TO ROW
17, AFTER THAT IT WILL NOT DISPLAY THE DROPDOWN. i CANNOT FIND WHERE THIS IS
DELINEATED, i SUSPECT MAYBE IT IS HELD IN MEMORY? SURE WOULD APPRECIATE SOME
HELP. i YOU CAN'T REPLY BY 4:30 CST, MY EMAIL IS .
HAVE A GOOD WEEKEND FOLKS.



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
Lookup Data in two seperate Spreadsheets Padraig Excel Worksheet Functions 6 June 28th 06 03:05 PM
A Challenge: Detection of first and Last +ve number in a range Chris Gorham Excel Worksheet Functions 1 May 17th 06 03:44 PM
Return SEARCHED Column Number of Numeric Label and Value Sam via OfficeKB.com Excel Worksheet Functions 23 January 30th 06 07:16 PM
formula to use when number of rows changes dynamically confused Excel Worksheet Functions 3 August 17th 05 03:55 PM
Insert a number of rows based on a value in a cell on active row iRocco Excel Discussion (Misc queries) 1 August 11th 05 06:18 AM


All times are GMT +1. The time now is 10:11 AM.

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"