need help looking up sheet names

[joiemel]

Hi there,

Newbie here need help to lookup sheet names. i have a workbook with lots of sheets with person's names. what I am trying to do is a summary sheet that if I type the persons name on a cell it will lookup that name from the sheets and return cell values that i want from that persons sheet.

or is there a way that I can store sheet names and use it later as a address to get certain values on those sheets. hope I explained it clearly


If anyone could help me it'd be much appreciated.

 

[CHSBK]

Hi

If you need to reference a cell on another sheet, you can refer to that cell by prefixing the cell label with the sheet name. For example, Excel, by default names its worksheets as Sheet1, sheet2, sheet3, etc. If you are working on sheet2 and need to reference a cell on sheet 1, lets say cell B3, you would use 'sheet1!B3'

I'm not up to speed on Excel's VBA, but I'd be surprised if it isn't possible to use sheet names here to allow you to search for a specified sheet and return specified values.

I've never had to use lookup tables either, but it sounds like there may be a solution in there too??

I am looking forward to reading further posts on this topic as it would open up a whole new world for how excel can be used for me!!

 

[joiemel]

Hi,


I know what you mean, I've tried referencing the cells like you said, thats how I do it but its to much work. there are always new people being added. I've attached a sample workbook here.

What I want to happen is I would just input the persons name which is the same as their sheets name name and then the rest will be auto populated by putting fixed formulas/ reference functions. hope this explains it better. Thanx for the input by the way

 

[joiemel]

Is there any way to store the sheet names in cells and retrieve them as an address? like if I put the sheet name in cell A1 and I want B1 to get the the value in A1 and use it as sheet name, so all I have to add in the reference is the cell # ex. ('sheet'!B3) to ('A1'!B3)
need help on this really bad

 

[chergh]

This:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)


Will return the sheetname

 

[DCrake]

OTTOMH. When you open a workbook there is an event something like OnActivate. What you could do is to write a bit of vba that does something along the lines of

Have a combo box in sheet1

For x = 1 to sheets.count
   Me.Combo.AddItem Sheets(x).Name
Next

Not a big Excel vba user but I know it can be done. Essentially you are looping through the workbook using the sheets collection count and obtaining the name of the sheet via it ordinal number and adding it to the combo box items list.

May have a play later to confirm this.

David

 

[joiemel]

Thanx for the quick replies! I' try the first one.
I don't know anything about VBA and how to put em, but I'm willing to learn and try. hoping to hear great results! thanx again guys!!!

 

[joiemel]

This:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)


Will return the sheetname

I tried this one, it returns the name of the sheet the code is on. how can I make it return a different sheet name from where its placed!? ex. if the code is in A1 in sheet1 and I want it to display sheet2's name? can you also give just a brief explanation how this code works and whats with the number "1,256"? hope its not too much to ask. sorry for being such a noob