I am trying to fill a listbox with data from a second worksheet in the workbook based on the value of a cell
on the first worksheet. The data in the second worksheet contains variable number of subitems corresponding to the item on
the first sheet. For example, on the first sheet, I have a cell filled with the name of an item. On the other worksheet is
a list of different items with varying number of sizes for each item. I am trying to develop a UserForm that contains a
listbox that is filled based on the item in the cell on the first sheet. I have tried writing Do While loops, If Then
statements but can't get any of them to work. Any suggestions would be greatly appreciated.

Search criteria: fill listbox with items from a pivottable
field, get
pivot table field values into a listbox, populate listbox with items
from pivottable, listbox values from pivottable, get listbox values
from pivot table.

The three procedures below demonstrate how to fill a listbox with
items from a pivot table on the active worksheet, modify the pivot
table with a selection in
the listbox and then "reset" the modified field in the pivot table
with all values being shown. The target field in the pivottable is a
field call DEPT that will populate a listbox named ListBox1 on the
active sheet.

1. The first procedure, SetupListBox1, populates ListBox1
2. The second procedure, ListBoxSelectionChangesPT, modifies
(shows/hides) DEPT items in the pivottable based on the selection
highlighted in ListBox1 (note with this code, it seems that you can
only select one item at a time in the listbox, I tried adjusting
ListBox1> Properties>Behavior> MultiSelect: 0 - frmMultiSelectSingle,
but this modification conflicted with the code in the procedure. You
may have better luck or insight.
3. The third procedure, PivotShowItemAllVisible, shows all the
previously hidden items in the first field of the pivot table (in this
case, DEPT).

Please note that I gathered these procedures from previous postings in
the newsgroup and modified the existing code just slightly for clarity
and run-time accuracy.

Dim PF As PivotField
Dim I As Integer
Set PF = ActiveSheet.PivotTables(1).PivotFields("DEPT")
With ActiveSheet.ListBox1
.Clear
For I = 1 To PF.PivotItems.Count
.AddItem PF.PivotItems(I)
Next
End With
End Sub

Sub ListBoxSelectionChangesPT()
'Note for this procedure to work it seems that your
'ListBox1 Properties>Behavior>MultiSelect must be set
'to 0 - frmMultiSelectSingle. So it seems you can only
'select one item in the listbox to update to the pivot table.

Dim PF As PivotField
Dim I As Integer
Dim iVis As Integer
Set PF = ActiveSheet.PivotTables(1).PivotFields("DEPT")
With ActiveSheet.ListBox1
For I = 1 To PF.PivotItems.Count
If .Selected(I) Then
PF.PivotItems(I).Visible = True
iVis = iVis + 1
End If
Next
If iVis = 0 Then
MsgBox "Must have at least one DEPT visible"
Exit Sub
End If
For I = 1 To PF.PivotItems.Count
If Not .Selected(I) Then PF.PivotItems(I).Visible = False
Next
End With
End Sub

Sub PivotShowItemAllVisible()
'Shows all items in the FIRST FIELD in all pivot tables
'on the active sheet.
'For version 2000 -- show all items in field
'sort is set to Manual to prevent errors, e.g.
'unable to set Visible Property of PivotItem class

Dim pt As PivotTable
Dim PF As PivotField
Dim pi As PivotItem
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each PF In pt.VisibleFields
For Each pi In PF.PivotItems
If pi.Visible True Then
pi.Visible = True
End If
Next pi
Next PF
Next pt
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

I am trying to write a worksheet that creates a letter from an other worksheet data information. However I dont want it to
look like a form full of blank boxes. Can I automatically hide a row that deos not contain any information or blank spaces.
Cheers in anticipation.

I have one array of cells (5x5). There is a number in each cell, and several of them are not unique. I have another array of
cells that needs to fill up with values from the first array, but must only take a value if it isn't already in the second
array.

For example, in this source array:

1 2 8 5 3
4 9 2 3 1
9 4 3 7 5
6 5 6 0 9

The second array would look like this:

1 2 8 5 3
4 9 7 6 0

Each number would be in a separate cell, and would need to look at the preceding cells to determine
which values it can receive, if any.

I have a question regarding excel. I am a relative newcomer to Excel Macros and such, and am having fun,
but am frustrated with the following problem:

I have a sheet titled "To Do List" with 10 columns - the relevant
columns for this problem are B and C. Column B is titled Client Name and column C is titled Client No. I have a separate
worksheet (same book) titled "Sheet1." My data begins in Row 6 of To Do List (i.e. first cell I use for client name is B6).
Further, I have an autofilter drop down list in row 5 of each column so I can sort ascending/descending by client name,
number, or values in other columns. I am trying to get a function or a macro which will automatically fill in the client
number (column C) when I type in the client name (B).

So far, I have the following function in column C which
works for autofilling column C values based on values from Sheet1:

=IF(ISNA(VLOOKUP($B6,Sheet1!A:B,2,FALSE)),"",VLOOKUP('To Do List'!B6,Sheet1!A:B,2,FALSE))

This should be easy,
however, here are the issues I encounter:

1. When I use autofilter drop down in Column B and sort ascending or
descending, column C does NOT sort along with B (or the other columns).

2. I would LOVE to have this function or
macro use approximate values for column B, or, in the alternative, to autofill based on values in Sheet1. In other words, if
I have client name Adams, John D. in Sheet1, I would like for Autofill to pull that up the first time I type it into B in To
Do List, as opposed to me having to type it in once and then autofill recognizes it, if this is possible (there are over 1300
client names and I may be adding new ones to just the client list (sheet1) so it'd be helpful for To Do List to automatically
recognize them and autofill in their appropriate numbers.)) For example, 15A is GTH Owners Corporation. I'd like to enter
GTH in To Do list and have the macro or function recognize this immediately. NB - when I use TRUE in the above function, it
does not work in any meaningful way.

Problem 1 is my main concern. I can live with the "inconvenience" of
having to type in a full client name one time, if filling all the above wants is a pain!

Thanks in advance for the
support and help.

I am attaching my To Do List, with the appropriate sheets, for a clear understanding of what I
am talking about (albeit with most client removed and with some name changes).

P.S. I already have a Macro on the
sheet which automatically moves anything in Column A marked Done to the Done sheet (see file)

suppose I have 4 Columns A,B,C and D in a spreadsheet. and 4 columns 1, 2, 3, and 4 in a an access db query
Is it possible to autofill Column D with the values from column 4 in my access query with the condition that it will only
auto fill D if the value of column B is the same as the value as column 2 in my db query?

Final invoice comparison (this is to be my result spreadsheet and includes all my clients) - a list of numeric
codes in column A; a list alphanumeric codes in column B; clients name on column C; columns D, E, and F are empty at the
moment and need to be populated (D - NW net invoice value; E - NW gross invoice value; F - ABS invoice value).

I
have prepared a small algorithm of what I need: (please forgive the loose use of my own algorithm language)

Open
"ABS invoices"
Open "NW invoices"
Open "final invoice comparison"

I will end up with a
file containing all clients and what amounts they have been invoiced from each system

Notes: because not all
clients are invoices every month, "ABS invoices" and "NW invoices" will have slightly less clients than
"final invoice comparison". That wont be a problem, because "final invoice comparison" has all the
clients anyway. Thus all clients in ABS invoices and NW invoices are findable in final invoice comparison

I have spent a couple of hours searching for a solution to this problem and either I am not asking the question correctly or
it cannot be done.

This is my problem. On a user form I have a combo box which is populate with vehicle marques.
Each of these marques relates to a namd range on a spreadsheet. Selecting a marque from the combo box populates a listbox
with records from that range.

What I want to be able to do is click a record (which has a unique ref number) and
search the master list then populate a series of text boxes with the selected record.

Each record has around 40
columns of data, therefore the named ranges only contain an abridged version of the data.

Is what I am asking
possible?

Any pointers would be greatly appreciated as I don't have a huge amount of hair left to pull out!!

I have an excel file which contains several worksheets.
Each worksheet contain a list of values. For example, under column name output, there is a range of random values. The other
worksheets are the same, they contain a range of random values under the column name output.

My question is: can i
create a worksheet which can extract the maximum values from the different worksheets? For example, max. value in worksheet 1
is 100, Max. value in worksheet 2 is 200, max value in worksheet 3 is 500. So can i create a new worksheet which can extract
these maximum values? This will be useful as i will have many many worksheets and i can't go through all the worksheets one
by one to find and compare the values.

Can excel do the above? or i need to use Visual basic to help me? I'm
confused..

Hello i am trying to edit a macro that copies the values from an identical worksheet from several different workbooks. right
now i have a code that does a good job of copying the sheets needed and names them based on the file name from which they
were copied, which is ideal. the problem is it opens all the sheets in a new workbook. i would prefer to have it
automatically fill the values instead of creating new worksheets, that way i can set the master workbook to total the hours
for each person in a separate sheet. also some of the workbooks data is being pulled from will be password protected, but not
all of them. I am not very familiar with any sort of coding, so any assistance would be greatly appreciated.
Here is the current code i am running
VB:

Option Explicit
'The example below will copy the first worksheet from each file in a new workbook
'It copy as values because the PasteAsValues argument = True
'First we call the Function "Get_File_Names" to fill a array with all file names
'There are three arguments in this Function that we can change
'1) MyPath = the folder where the files are
'2) Subfolders = True if you want to include subfolders
'3) ExtStr = file extension of the files you want to merge
' ExtStr examples are: "*.xls" , "*.csv" , "*.xlsx"
' "*.xlsm" ,"*.xlsb" , for all Excel file formats use "*.xl*"
' Do not change myReturnedFiles:=myFiles
'Then if there are files in the folder we call the macro "Get_Sheet"
'There are three arguments in this macro that we can change
'1) PasteAsValues = True to paste as values (recommend)
'2) SourceShName = sheet name, if "" it will use the SourceShIndex
'3) SourceShIndex = to avoid problems with different sheet names use the index (1 is the first worksheet)
' Do not change myReturnedFiles:=myFiles
Sub RDB_Copy_Sheet()
Dim myFiles As Variant
Dim myCountOfFiles As Long
myCountOfFiles = Get_File_Names( _
MyPath:="C:Documents and SettingsGerowJJDesktopGroup Time Sheets", _
Subfolders:=False, _
ExtStr:="*.xls", _
myReturnedFiles:=myFiles)
If myCountOfFiles = 0 Then
MsgBox "No files that match the ExtStr in this folder"
Exit Sub
End If
Get_Sheet _
PasteAsValues:=True, _
SourceShName:="Summery", _
SourceShIndex:=1, _
myReturnedFiles:=myFiles
End Sub
' Note: You not have to change the macro below, you only
' edit and run the RDB_Copy_Sheet above.
Sub Get_Sheet(PasteAsValues As Boolean, SourceShName As String, _
SourceShIndex As Integer, myReturnedFiles As Variant)
Dim mybook As Workbook, BaseWks As Worksheet
Dim CalcMode As Long
Dim SourceSh As Variant
Dim Sh As Worksheet
Dim I As Long
'Change ScreenUpdating, Calculation and EnableEvents
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
On Error Goto ExitTheSub
'Add a new workbook with one sheet
Set BaseWks = Workbook.Name("PullTime.xls").Worksheets(1)
'Check if we use a named sheet or the index
If SourceShName = "" Then
SourceSh = SourceShIndex
Else
SourceSh = SourceShName
End If
'Loop through all files in the array(myFiles)
For I = LBound(myReturnedFiles) To UBound(myReturnedFiles)
Set mybook = Nothing
On Error Resume Next
Set mybook = Workbooks.Open(myReturnedFiles(I))
On Error Goto 0
If Not mybook Is Nothing Then
'Set sh and check if it is a valid
On Error Resume Next
Set Sh = mybook.Sheets(SourceSh)
If Err.Number > 0 Then
Err.Clear
Set Sh = Nothing
End If
On Error Goto 0
If Not Sh Is Nothing Then
Sh.Copy after:=BaseWks.Parent.Sheets(BaseWks.Parent.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = mybook.Name
On Error Goto 0
If PasteAsValues = True Then
With ActiveSheet.UsedRange
.Value = .Value
End With
End If
End If
'Close the workbook without saving
mybook.Close savechanges:=False
End If
'Open the next workbook
Next I
' delete the first sheet in the workbook
Application.DisplayAlerts = False
On Error Resume Next
BaseWks.Delete
On Error Goto 0
Application.DisplayAlerts = True
ExitTheSub:
'Restore ScreenUpdating, Calculation and EnableEvents
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
End Sub

I am working on an If statement that shows a "yes" or "no" depending if it is <= or >= to one
percent. This is what I have and it works fine.

=IF('Funding'!$G3<=1%,"yes",IF('Funding'!$G3>=1%,"no!"))

The problem is that column G
is just the range of numbers, and it must reference another column that has a list. For example, "45-019" would be
in the current worksheet as one instance(d3) but this number can be repeated again say in cell D8. Another worksheet has all
of the list of numbers in column A that will be used.
Is it even possible to include the list from the other worksheet for each instance on the worksheet with the formulas and
still keep the above statement? If so, can someone please help?

Has been a while since I have come unstuck and I hope my problem can be resolved...

I have used
a formula in one worksheet uses two calulated values. One formula returns a value from another worksheet. The other formula
is in the same worksheet in which a final value is being calculated. The final formula wont use the returned value from the
second worksheet.

Am I missing something or is there some switch that needs activating or does excel not handle
this sort of calculation?

Here are the formulas:

This formula (below) sits in Col AS in worksheet 1 and
returns a value from Assumptions worksheet (worksheet 2).

I'm new to the forum and not very knowledgeable in Excel - so apologies if I have broken any rules on my first communication
and for my lack of experience. I have spent days trying to get my head round this problem and cannot find a solution.I do
hope you can help. I have workbook which is used by our salesmen to price up jobs and send out estimates to our clients. Each
job can have up to 9 different 'elements' or parts to the job and each element is priced separately to include labour,
materials,plant, which are on separate worksheets in the workbook. We use VLOOKUP to transfer data to another worksheet
called 'estimate'. I need to create a jobsheet on a separate worksheet to distribute to the workforce containing only the
labour, materials, plant etc required for the job and NOT the whole product lists. So basically the jobsheets needs to pull
information from several other worksheets, but only if required. I have attached the workbook to explain, I have had to
remove several worksheets because of size restrictions, hopefully you will get an idea of what I need with what I've sent

Im kinda new to VBA and i have a small problem
I have a sheet with 2 rows (A2:A45) and (B2:B45). The A column contains objects, the B column contains their location in a
building. So B2 contains the location of A2 , B3 for A3 etc. I wish to populate 2 listboxes with these values in the
following way:

First probelm is populating the first listbox (Containing the locations), I can populate it with
the range B2:B45 but every location is repeated eventhough there are only 6 different locations. I want each location to be
listed once and then when clicked on producing the Objects contained within the location.

I need to create list-entries in a list in column B depending on a list-selection in column A. All values
are available in the same workbook in a relational format. In SQL I would write something like "Select * from TableB
where Column A = Value X" and Value X is = the selected value from Excel-Column A from the first list.

As
example:

ColumnG ColumnH
AAA 111
AAA 222
AAA 333
BBB 444
BBB 555
BBB 666

So in my first Worksheet are two columns A and B. If I select in my first List-ColumnA the
unique values from lookup-columnG I want that in the correponding cell in column B are all the values listed from the
lookum-columnH.

So I select AAA in columnA I want the list 111,222,333 in columnB corresponding cell.

I am encountering a limit of < 25 array elements when using the following
assignment method:

ActiveChart.SeriesCollection(1).Values = thisDataSet

where thisDataSet is a declared
array variable of type variant. When the
array variable has more than 24 elements the above assignment crashes with
the message "unable to set the values property of the series class". This
array variable always contains numeric data in every element with no empty
elements. The assignment works perfectly for thisDataSet of 24 or fewer
elements. Is there a better way to assign series values from an array
variable? Or, do I have to live with the < 25 array element limit?

I have a large array of integer values and blanks (13000R X 67C), and I need a list of unique values to define property zones
for a model. I used advanced filter, which weeded it down to about 725 unique rows, but it is still a formidable task for my
staff to manually compile a list, with great potential for errors. I don't care where in the array the values are, or how
many of them there are (I may want to know that later, but I more or less know how to do that). Is there a handy way to
generate a list, which may contain over a thousand values, from an array without individually searching each column? I
searched the forum for "unique values" topics, but didn't find exactly what I needed.