Replies

Dear LindaR, and all,

Thank you for your very kind help. LindaR’s solution will, I think, meet our needs.

From a learner’s point of view, though, I’m wondering if you could help me understand what’s going on in the formula?
[indent]

---

=INDEX(Master!$B$2:$B$1000,SUM($A$2:A2))

---

[/indent]
Particularly, your use of SUM as the second INDEX argument.

Again, thanks for your help,
Rich

So-o-o close, yet so far away!

Thanks so much

Well, let’s see where we go with your ideas, before we say no!

Thanks,
Rich

Dear Rory,

They’ll be for display purposes.

Regards,
Rich

Thank you two for your suggestions about using AutoFilter.

I am seeking a way to do this without using AutoFilter follwed by copy and paste. I envision that I could edit or add to my “master” list and the various dependent lists would dynamically reflect the data in the “master” list without further action.

That’s my challenge!

Thanks,
Rich

Dear Steve,

Thanks so very much. This seems to meet my client’s needs perfectly.

Once the formulas in column D have been entered, you could hide the column to preclude some visual confusion.

G’Day,
Rich

Hello y’all,

Thank you for all of your kind suggestions.

I have tried all of your suggestions to no successful result!

Eventually, this Word document is going to be converted to a PDF, so an icon doesn’t meet our needs.

Turns out, that in the previous edition, someone made an image of the Excel worksheet and put that in the Word document.

I still am interested to know if there exists a limitation someone has uncovered?

Thanks so much,
Rich

Dear Sam,

Your solution worked perfectly. I modified the macros to use a ComboBox, because my client was really ooking for a drop-down menu navigation tool on the worksheet. I didn’t address going to a different worksheet in the active workbook. That shouldn’t be too difficult to achieve, though? I defined all the Names before I wrote the macros.

Here’s the code:

Sub initComboBoxes(dummy As Variant)
‘ Called by Sheet1::Activate & Workbook open to initializes the Combobox named ComboBoxNav

‘ Add each macro to the combobox, ComboBoxNav
With Worksheets(“ComboNavTool”).ComboBoxNav
.Clear
.AddItem “GoToPlace1”
.AddItem “GoToPlace2”
.AddItem “GoToPlace3”
.AddItem “GoToPlace4″
End With
End Sub

Sub GoToPlace1()
‘ Create all macros before all other VBA
‘
‘ GoToPlace1 Macro
Application.Goto Reference:=”Place1″
End Sub

Sub GoToPlace2()
‘
‘ GoToPlace2 Macro
Application.Goto Reference:=”Place2″
End Sub

Sub GoToPlace3()
‘
‘ GoToPlace3 Macro
Application.Goto Reference:=”Place3″
End Sub
Sub GoToPlace4()
‘
‘ GoToPlace3 Macro
Application.Goto Reference:=”Place4”
End Sub

This next macro was assigned to Form Button to reset the Print Area easily on the Workbook
Sub SetPrintPlace4()
‘
‘ SetPrintPlace4 Macro
‘
ActiveSheet.PageSetup.PrintArea = “”
‘ Application.Goto Reference:=”Place4″
ActiveSheet.PageSetup.PrintArea = “$G$19:$M$36”
End Sub

I’ve attach the sample workbook, as well.

Thanks for all your help.
Rich

Thanks Hans for that lead.

Still it begs the issue of assigning a macro to an item list in a ListBox.

Thanks,
Rich

Hans,

Your FullName suggestion worked perfectly! It is just what we needed. You’re a genius, and thanks so much!

So, it looked like this:

Function WORKBOOKPATH() As String
‘Returns the workbook name of the cell
‘that contains the function
WORKBOOKPATH = Application.Caller.Parent.Parent.FullName
End Function

I distributed both of these, WORKBOOKNAME and WORKBOOKPATH, via an Excel Add-in.

Tested successfully in MS Excel 2000 in WIN XP Pro, MS Excel 2004 in MAC OS 10.3.9, and MS Excel v.10 in MAC OS 10.3.9.

Thanks so very much, all of you,
Rich

Hi Steve,

Wow, we’re really close, most probably because I wasn’t as clear as I should have been. Your suggestion certainly returned H:Excel ResourcesRich Created Add-Ins

I was looking for something that included the Workbook name as well. It would return, for example: H:Excel ResourcesRich Created Add-Insmyworkbook.xls

Thanks,
rich

Hans,

Yes, thanks for the =CELL tip.

And, I was trying to find if there is a way that I could get the path (not oaths ) in my user defined function? Any ideas?

Thanks,
Rich

Hi Trudi,

Thank you so much for your quick reply!

I tried your suggestion, but I must not be doing something correct. Maybe I’m not as familiar with inner joins. I still can get it to work the way we envisioned! I get no results. Do I run the new query to see all the info we’re after?

Any suggestions?

Thanks a bunch,
G’Day,
Rich

We’re trying to design a Query that returns the Highest Costing Item, and the Lowest Costing Item. Our table includes a field name of Description, and another field name of Cost.
We ran the Query successfully using totals to find at the same time the Max and the Min for the “Cost” field. But we want to know the Description of the Highest and the Lowest! When we include the field “Description”, the Query returns all the records, not simpply max and min.

Does anybody know how we could show the “Description” and the Max and Min?

Thanks,
Rich
see http://www.mrwteaches.net/d221%5B/url%5D

(URL corrected and made clickable by HansV – see Help 19)

We’re trying to design a Query that returns the Highest Costing Item, and the Lowest Costing Item. Our table includes a field name of Description, and another field name of Cost.
We ran the Query successfully using totals to find at the same time the Max and the Min for the “Cost” field. But we want to know the Description of the Highest and the Lowest! When we include the field “Description”, the Query returns all the records, not simpply max and min.

Does anybody know how we could show the “Description” and the Max and Min?

Thanks,
Rich
see http://www.mrwteaches.net/d221%5B/url%5D

(URL corrected and made clickable by HansV – see Help 19)