referencing a sheet in a list box

I have a workbook in which I need to keep records for many people.  
want to have a sheet for each person.  One sheet would serve as 
report form to extract certain data from the selected person's sheet.

It would be ideal if I could use a list box to select the name of th
person, which would then tell the form which sheet to pull the dat
from.  It would also be ideal if there was a way to have the names i
the list box generated from the existing sheet names so it would b
accurate as people are added to the workbook.

I found a way to generate the name of the sheet in a cell, but no wa
for excel to then treat it as a reference to that sheet.  It onl
treats it as text.

Am I asking for the moon or is it possible?  I am guessing I will hav
to explore the world of user created functions or something like that.
Can anyone help?

Dave Russel

--
Dave_Russel
-----------------------------------------------------------------------
Dave_Russell's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1596
View this thread: http://www.excelforum.com/showthread.php?threadid=27442

0
11/2/2004 3:33:33 AM
excel 39879 articles. 2 followers. Follow

2 Replies
469 Views

Similar Articles

[PageSpeed] 30

you say you already have the sheet name in a cell.  have you tried the
indirect function to get excel to treat the sheet name as a reference?
 For example, if I have the word: SHEET1 in cell b5 and I want a cell
in sheet2 to equal b1 from sheet1 then I enter the following in that
cell
INDIRECT(B5&"!b1")
0
11/2/2004 3:25:04 PM
How about a floating toolbar that you can use with any workbook?

http://groups.google.com/groups?threadm=40A584A3.DB381C74%40msn.com



Dave_Russell wrote:
> 
> I have a workbook in which I need to keep records for many people.  I
> want to have a sheet for each person.  One sheet would serve as a
> report form to extract certain data from the selected person's sheet.
> 
> It would be ideal if I could use a list box to select the name of the
> person, which would then tell the form which sheet to pull the data
> from.  It would also be ideal if there was a way to have the names in
> the list box generated from the existing sheet names so it would be
> accurate as people are added to the workbook.
> 
> I found a way to generate the name of the sheet in a cell, but no way
> for excel to then treat it as a reference to that sheet.  It only
> treats it as text.
> 
> Am I asking for the moon or is it possible?  I am guessing I will have
> to explore the world of user created functions or something like that.
> Can anyone help?
> 
> Dave Russell
> 
> --
> Dave_Russell
> ------------------------------------------------------------------------
> Dave_Russell's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15967
> View this thread: http://www.excelforum.com/showthread.php?threadid=274422

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
11/2/2004 11:02:46 PM
Reply:

Similar Artilces:

Pay To List
How do I delete some of the names in my "Pay To" list (the "pay to" list that is used when making entries into your accounts)? I deleted a large amount in the payees list but they still show when selecting "pay to" when creating a new account entry. I tried the FAQ page and online help and couldn’t find anything. Thanks! John Normally deleting them from the explicit list will get them out of the pulldown/autocomplete list. So, your list at Account List More|Categories and Payees|Payees is much shorter than it was and still you see names not in this list...

security group/distribution list administration
please take a look at the thread below. i posted this earlier today to the AD group. someone suggested to check here as well to possibly find a workaround instead of having to create and maintain two separate groups. thanks. ------------------------------------------ So far I know you have to create two groups, but check the exchange group, they may have some smart tricks. -- Regards Christoffer Andersson No email replies please - reply in the newsgroup ------------------------------------------------ http://www.chrisse.se - Active Directory Tips "toto" <toto@discussions.mic...

comparing from one sheet to another
Hi. I am fairly new to Excel, and am trying to make a workbook to analyze home purchase costs, etc. One thing I want it to do is be able to enter a purchase price on the main sheet, and then compare that price to data on another sheet that has a range of home prices and the title/escrow fees at that price range, and them put the corresponding fee back on the main page. Any idea how I do this? Thanks. Hi have a look at VLOOKUP. See: http://www.mvps.org/dmcritchie/excel/vlookup.htm -- Regards Frank Kabel Frankfurt, Germany "Trish" <anonymous@discussions.microsoft.com> schri...

Contacts and address list
I am migrating from my old XP machine to a new one. I have managed to get contacts into Outlook. When writing an email and clicking on To: , I get the attached message: "The address list could not be displayed. The contacts folder associated with this address list could not be opened....." I have checked: "Show this folder as an email address book" for the contact folder. Please, what should I be doing? Peter Try removing all the Contacts folders listed here: Tools menu > Email accounts > View/Change existing directories/address books > Click Next > ...

Search box of the Record Selector
Hi, Is it possible to set the focus on the Search box of the Record Selector when a form is displayed (when some value has been set before)? That way, when the user types something, he will not modify the existing record. He will in search mode by default. Thanks. -- Jac Tremblay Hi, "Tremblay" such as a town in France !!! Here a solution : Private Sub cmdSearch_Click() SendKeys "^+{F}", True End Sub -- Argy Goto : http://argyronet.developpez.com/ Livres : Créez des programmes avec Microsoft Access 2007 (ISBN 2742982442) VBA pour O...

List for AutoFilter and list for Subtotals have different meanings?
I need to type up a list if I want to use (1) Date>Filter>AutoFilter or (2) Data>Subtotals A sample list: Month Units Jan 100 Jan 200 Feb 100 Feb 400 Mar 300 etc. etc. I find the following interesting. To do (1) AutoFilter, I have to actually **create** a list i.e. Data>List>Create List ...... If I want to do (2) Subtotals, I **don't** have to do Data>List>Create List ....... If I do, Subtotals on the menu won't even be highlighted. Although both (1) and (2) require a list, the definition of a list appears...

Macro
Hi All, I currently have a huge macro that I use to import .asc files into excel for validation, and other things. This macro separates project accounts from non project accounts and performs a series of tasks on the project accounts. The project accounts can be differentiated from non project accounts because they all have an "X" in them... So, in my code, I first have to import the delimited data, set up column breaks, and then sort by my account column to get all of the account data at the bottom of the wkst...(code is already written for this, and I have been working o...

How can I retrive the contents of a comment box in microsoft excel
I have inserted comments, and now i want to get the contents of each comment in a cell. Is it possible? Rg The code below will take the comments from the active sheet and put them in column A (Address) and column B (Text) on a new sheet inserted into the book Sub ExtractCommentsToNewSheet() Dim wksActive As Worksheet Dim wksNew As Worksheet Dim lCnt As Long Dim rngComm As Range, myCell As Range Set wksActive = ActiveSheet Set wksNew = Worksheets.Add Set rngComm = wksActive.Cells.SpecialCells(xlCellTypeComments) lCnt = 1 For Each myCell In rngComm With wksNew.Range("A" & ...

Extract names from string based on value list
I have a list of productnames and a list of brandnames. I want to supply the productnames with an attribute "Brand". I am currently manually filtering the list of productnames based on "Contains... Brand" and adding the Brand to the product using copy paste, but this takes me too long and I can only match records one brand at a time. Can I make Excell search through the list of productnames and whenever it comes across a brandname within the string of a productname it will add it to the column next to it? One way Assume productnames running in A2 down A...

How to protect a sheet if ...
Hi, I have an XL file with a data to be entered by a user using one sheet per date. I also have a Results summary sheet where I use this sort of function, =SUM(Monday 1st : Friday 5th!B3). I would like to implement "Conditional protection" where a data sheet can no longer be modified after a certain date. Example, if today is Tuesday 2nd, then the "Monday 1st" sheet can no longer be modified. An ideas ??? TIA N.L. You need VBA. Put thios code in the ThisWorkbook code module Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target A Range) Application.E...

Help Needed with automating Check Boxes in Word 2K
I have a protected document that offers 3 levels of services. Each service has a checkbox next to it. I also have a list of several check points (with checboxes next to each) that need to be done base on the level of service selected. I would like to have the automation where by the check boxes next to the check points get marked automatically only for the level of service requested. Example. Service Types are GOLD SILVER BRONZE GOLD = check points 1, 2, and 3 SILVER = check points 1, 2, and 4 BRONZE = check points 1 and 3 and 4 -- tech1NJ As you probably only wa...

Non-functional Popup Menu Boxes
When I try to change the properties (color, data labels, etc.) the pop-up menu boxes don't properly display and the parts that do display often don't work. To make matters stranger, if I activate another open program in Windows and return to Excel, the popup menus are often there, but only partially functional. Anyone else seen this craziness? ...

Is MS CRM V3.0 a Viable Package Out-of-the-Box or No, Not Quite Ye
I have been working with a 90-day evaluation installation of the MS CRM server and client product for the past few weeks, along with reading a substantial amount of commentary here in the discussion groups. From my own limited experience thus far, and some of the more critical message threads posted here, there seems to be a strong likelihood that pretty much any MS CRM implementation that is attempted is going to require some level of customization(s) within the various areas of the application that lend themselves to being modified, whether it be done on the application's base fo...

Not triggering "BeforeUpdate" in text box on UserForm
OK, so I need an education here (and what better place to find one...) In Excel 97 I have a UserForm with a text box that has VBA code in the '__BeforeUpdate()' event. The form also has a command button. It appears that if the user types stuff in the textbox, then immediately mouse-clicks the command button, the _beforeUpdate() event of the text box is not triggered. It also appears that the ControlSource range of the text box is not updated in this circumstance. The _Change() event is not a particularly good option here, since I still have to know when the user has fully completed the...

Distribution lists: problem with display of names (Outlook 2000)
With creating a distribution list by adding members from the available contacts the names in the list always get "E-Mail" attached to in brackets - sample: "John White (E-Mail)". These names are then listed also in the recipient list of mails sent to the distribution list. I don't like this "ugly" name appendix - is there a way to get it removed? Torsten Villnow You can hack the contact32.dll if you really need to go to the trouble. http://www.slipstick.com/contacts/emaildisplay.htm -- Russ Valentine [MVP-Outlook] "Torsten Villnow" <t.vi...

How I get CheckBox state on a Sheet.
I want put a lot of checkBoxs, SpinButtons... on a Sheet. In VBA project, how I get these CheckBoxs state by its name? Thanks. Cactus Sub findCheckBoxState() Dim chkState As Boolean chkState = ActiveSheet.OLEObjects("Checkbox1").Object.Value MsgBox chkState End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Cactus [������]" <a@b.com> wrote in message news:%23Z5YkDG%23EHA.3988@TK2MSFTNGP11.phx.gbl... >I want put a lot of checkBoxs, SpinButtons... on a Sheet. > > In VBA project, how I get these...

how to unpivot a pivotted excel sheet?
how to unpivot a pivotted excel sheet? Is this based on a range/table in a worksheet? Find the grandtotal cell double click on it. You'll see the data--but it won't be formatted pretty. alamm wrote: > > how to unpivot a pivotted excel sheet? -- Dave Peterson ...

ComboBox dropdown list Colour!
I suppose you can not have the dropdown list Colour different from the actual window showing the data. Reason: So when the drop down list appears below , does not get mixed up with what is already on the screen Thanks For any Help...Bob You have color options in the properties for the object - background color. -- KARL DEWEY Build a little - Test a little "Bob" wrote: > > > I suppose you can not have the dropdown list Colour different from the > actual window showing the data. > Reason: So when the drop down list appears below , does not get mixed up &g...

Macro to cpy data from one wrkbk and append to a sheet in another wrkbk
I know this type of question has been asked many times so far, so apologise for another along the same lines. However, I have copied and hacked and chopped and pasted and change various examples in an attempt to do something that I thought would b rather simple. Each day, we send offsite 200 odd backup tapes, which we have barcode and scan into a spreadsheet. Each day, we receive 200 odd backup tapes, which we scan into anothe spreadsheet in the same workbook. The data in each page is over written daily. The sent data is in a sheet called "Today's movements" and go fro cel...

Find Record Box
I am trying to place a find record box in a form using the combo box wizard in Access 2003; however, the wizard does not give me the “ Find a Record on My Form…” option. I was trying to place it in the header (which I have done many times before), but it won’t give me the third option. I thought it might be a “properties” issue, but if it is I can’t identify it. I’ve done something wrong, any ideas? Thanks in advance. "OldGuy" <OldGuy@discussions.microsoft.com> wrote in message news:C468BC75-B17C-48AE-82D4-DF46B7521125@microsoft.com... >I am trying to place a find...

How do I edit or delete the yellow tag box in Publisher?
When I email my Publisher page as a message the email message contains a little yellow box that shows up when you move the mouse over it. I need to either remove the box or edit the text in it but I don't know where or how to do that. The text in the box should be the title of the page. I appreciate any help given. Thanks. If you wish the recipients to be able to read your Publisher page, convert it to a pdf and attach it. You can also attacht the Publisher file, but the recipients must have a similar release of Publisher to open it. There is no free viewer. "jmo1301" &l...

Is there a way to use background pattern fill for just a text box?
Does anyone know if there is a way to use the background colors/textures in just a text box and not the whole project? I have Publisher 2002. Thanks Kristine kda949, Click on the little down arrow next to the Fill Color icon. Choose Fill Effects. Have fun... "kda949" <kda949@discussions.microsoft.com> wrote in message news:6236C9FF-3EFD-4019-8C9D-0BA36EE0373F@microsoft.com... > Does anyone know if there is a way to use the background colors/textures > in > just a text box and not the whole project? I have Publisher 2002. > > Thanks > Kristine ...

Cell Referencing Problem
Greetings, In excel 2000 I would like to be able to read in different values for AK into the following cell entry: ='Feb01-04'!$AK39 Stated another way, I would like to rewrite the above cell reference so that the value AK is read from another source cell. For example I might type AX into source cell A1 and the above reference would change to ='Feb01-04'!$AX39 Any help would be appreciated. Thanks, Don Rist Don, You can use the INDIRECT function to do this. E.g., =INDIRECT("'Feb01-04'!$"&A1&"39") -- Cordially, Chip Pearson M...

Set sheet viewpoint on workbook_open()
When Workbook_Open() is called, I want to tell Excel to begin the viewpoint in the top left corner, starting at cell "A1". A user will sometimes save their workbook while viewing cells far below, such as "J200". When they re-open the workbook, the workbook with open at cell "J200", but I would rather have the sheet open at cell "A1". How do I do this? Include a line like this: Application.Goto Reference:=Worksheets("Sheet1").Range("A1"), scroll:=True -- Gary''s Student - gsnu200909 "Robert Crand...

How can my Email list view my Real Estate Flyers w/o an Attachment?
I send Real Estate flyers to a growing list of clients... Some say they can't open the PDF file, Others are afraid to Open ( They don't own a Mac ) for fear of getting a virus- We all get these email catalog pages with nice photos and information... but they are not attached- They are somehow embedded into the email source code. How can I do the same? I use InDesign to design the flyers, I can use Entourgae to send them out... Can anyone offer some Help/Advice/Suggestions/Comments/Web sites/ Changed Settings etc. to make this work? Email me (dan@buyahme.com) a test or I can emai...