new to excel 2003,don't know best way to create storage list

[kristenrice]

ok...I am new to Excel 2003..I know basics but not even a 1/100th of what the program can do....oh..i'm using windows XP...I am workind on creating a storage list for this company I just started working for. I have box numbers, a short description and then a long description. The long descriptions are actuall names and numbers of contracts averaging about 50-70 a box... I want to be able to have a master workbook with a mastersheet for each department with the box numbers and short description (that I can do!)...Next I want to creat a seperate workbook for each department that has several worksheets in it coorosponding to each box for that department...what I don't know how to do is make it so I can open the first workbook, say the water and sewer worksheet and click on the cell for box 124 that will automatically open appropriate worksheet from the corrosponding departments' workbook...

Again I know this is basic for you all I'm sure you've already answered this in some way somewhere on here, and I would gladly search but I don't know what to search for Please forgive my ignorance! Can anyone help??

[lozzablake]

This is possible, but you will need to have the exact file name, location and sheetname set up so that the macro can read in the data and then open the appropriate workbook and select the correct sheet. Can you post a sample of the master spreadsheet so we can see the data structure (use HTML maker)?

[Barry Katcher]

Hi, Kristen. Welcome to the Bored.

Select a cell in your master workbook. From the menu, choose Insert>Hyperlink. In the dialogue box, on the left, click on Existing File or Web Page. On the right, click on Browse for: File and in the new window, navigate to the file you want to link to and double-click. Back in the dialogue box, at top, you can specify the text for the link in the Text to display box. Click O.K., and you should have a text link that, when clicked, will open the workbook you want to go to.

[lozzablake]

The hyperlink will open the workbook, but it will not select a specific sheet. Does that work for you?

[Barry Katcher]

To go to a particular sheet/cell, place this code in the "This Workbook" module in each of the subsidiary workbooks:

Private Sub Workbook_Open()
    Sheets("Sheet3").Select 'change sheet name to suit
    Range("A1").Select  'change cell address to suit
End Sub

[kristenrice]

ok...First of all thanks for the help...now...I understand the hyperlink part. where you lost me what the

o go to a particular sheet/cell, place this code in the "This Workbook" module in each of the subsidiary workbooks:

Code:
Private Sub Workbook_Open()
Sheets("Sheet3").Select 'change sheet name to suit
Range("A1").Select 'change cell address to suit
End Sub

What is the "this workbook" module....I'm assuming the reference to "this workbook" is the name of the workbook I want to open.. is the Module a macro...and yes if you haven't guessed already welcome to I've never done a macro...sorry for the stupid questions...

Thanks again..

[Barry Katcher]

Kristen, modules are sort of like folders within Visual Basic that hold macros/code. The code above is a macro that is performed automatically whenever the workbook that it is in is opened. It has to go in the "ThisWorkbook" module,which applies to the entire workbook, as opposed to one of the Sheet modules, which are sheet-specific. Anyway, here's a step-by-step of how to get it into the workbook.

1. Copy the code above to the clipboard.
2. Open one of your "department" workbooks.
3. From the menu, choose Tools>Macros>Visual Basic Editor.
4. On the left, in "Project-VBA Project" window, double-click on ThisWorkbook module.
5. Click on the large screen on the right, then right-click and Paste the code.
6. Change the sheet name and the cell address in the code to where you want to open the workbook at. Shown in red below.
----->Sheets("Sheet3").Select
----->Range("A1").Select
7. Close the Visual Basic window and save the workbook.

The next time you open the workbook, either manually or via the link in the master workbook, it will automatically go to the sheet and range that you specify.

[kristenrice]

Ok...I think I understand how that works...

but take for instance that the master workbook under my water and sewer department sheet has a box 1 and box 2 and so forth...if I put the code you suggest in the Department workbook if I click on the box 1 or the box 2 in the master workbook wouldn't it take me to the exact same place in the department workbook? I actually want to have seperate sheets per box so when I click on the box 1 in the master workbook on the department sheet water and sewer it would open the department's workbook and go to the appropriate sheet for that box...doesn't that mean I'd have to use the sheet modules??

Ok...and then again I may not understand this after I get this project out of the way I think I'm signing up for a class!!! But in the meantime thanks again for all your help!

[Barry Katcher]

You're right, it would take you to the same place, regardless of which sheet/cell you want to go to.

Off the top of my head, I don't immediately have a fix for this, although I'm almost certain there's a way (probably several ways) to do what you want. Perhaps the Open macro taking you to a page with hyperlinks to the various sheets that can be clicked on. Perhaps code in the master workbook that take you to the specific workbook/sheet/cell.

I've just been handed a big project at work and can't research this right now, but keep the topic going; I'm certain someone else on the board can help you. I'll check back this evening and see if any progress has been made; if not, I'll jump back in.

[Barry Katcher]

Here's code that you can place in any module in your master workbook, using the technique in my previous post. Change the path/sheet/cell to the appropriate subsidiary workbook path/sheet/cell.

Sub Macro1()
    Workbooks.Open Filename:="G:\Finance\FShare\Barry\Excel\Barry\Book1.xls"
    Sheets("Sheet2").Select
    Range("C14").Select
End Sub

Now, you'll go to the area in your master workbook that you want to place the link, and you'll draw a button, to which you'll assign the macro above.

1. From the menu, choose Tools>Customize.
2. In the "Customize" dialogue box, click on the "Toolbars" tab.
3. Tick the box for "Forms" and close the dialogue box.
4. You should now have the Forms toolbar on your screen.
5. Click the icon for "Button" (a little grey rectangle).
6. On your sheet, in the appropriate place, click-and-drag to draw a button.
7. In the "Assign Macro" dialogue box that appears, click on the appropriate macro, and click "O.K.".
8. With the button still selected (handles and a ::::: border), highlight the default text and overwrite it with your own text.
9. Click anywhere outside the button to deselect it.
10. Click on the button, and it should open the subsid workbook and go to the page and cell you specified in the macro.