Formula To Return Worksheet Names

Sep 19, 2006

Without resorting to macros, I need a formula that returns a worksheet name.

Ideally, I want to have a list that contains each sheet name in the workbook.

View 3 Replies


ADVERTISEMENT

Return 2 Names Of ActiveX Worksheet Command Buttons

Feb 22, 2008

On a worksheet, I have several commandbuttons. For many of the buttons the name in the "name box" is different than the name in the "(name)" field under Properties.

Researching this, I found this on msdn2.microsoft.com:

An ActiveX control on a sheet has two names: the name of the shape that contains the control, which you can see in the Name box when you view the sheet, and the code name for the control, which you can see in the cell to the right of (Name) in the Properties window. When you first add a control to a sheet, the shape name and code name match. However, if you change either the shape name or code name, the other isn't automatically changed to match.

So, in my case, one button has:
In the name box: "CommandButton5"
In the (name) properties field: "RunProcess"

The real problem arises when I'm using code to loop through the objects on the worksheet, and build an array of the object names. I'm trying to match the name of the buttons to their click events. In the code, "btn.name" returns the name found in the "name box". The one that I want is the name in the "(name)" field under properties because this is the one that matches the click event.

How can I determine, in code, what is entered in the "(name)" field for an object?

View 5 Replies View Related

Concatenate Worksheet Names For Formula

Oct 19, 2007

I want to "calculate" a worksheet name by concatenating 2 cells. Then based on this concatenated word perform a " lookup". Basically I have a form for people to fill in and want to extract their answers automatically.

View 4 Replies View Related

Excel 2007 :: Cannot Get Worksheet Name To Return With Formula

Nov 8, 2011

I am working on a sheet that will copy a specified number of sheets, rename them, set a cell within the equal to a cell from another sheet and (the problem) set cells on my input sheet equal to cells in the newly copied and renamed sheets.

I want the cells in sheet "Data Input" to look something like "='newsheet'E26"

I can make it return the actual number in the cell and return the formula in the cell, but not make the cell in "Data Input" reference the cell in the new book.

Here is what I have:

Code:
Sub CopySheetsTest()
Dim i As Integer
Dim wks As Worksheet

[Code]....

View 4 Replies View Related

Excel 2007 :: Automatically Change The Worksheet Tab Names With Cell Value In Each Worksheet?

Feb 14, 2012

I am fairly new to macros and have trouble with VBA. I have a file with multiple worksheets. Each worksheet contains the name of a specific location in cell A8. I want this name in cell A8 to be the name on the worksheet tab for each worksheet in my file but do not know how to accomplish this. Is that even possible?

View 3 Replies View Related

Renaming Worksheet Names From Data In Worksheet

Mar 26, 2007

I use a web query to import into an excel workbook. As data is changed on the web that the query runs against, I would like the name of the sheet that is being imported into to change to one of the cells that is being imported.

View 9 Replies View Related

Formula To Look For A Number Entry On One Worksheet And Return Another Fixed Entry?

Mar 14, 2014

Is there a formula that will allow me to look for the existence of any number value in a row of one worksheet and then return a specific number value in a cell on another worksheet? For example, if the formula finds any number value it will always return the number 15 to a cell on another worksheet.

View 3 Replies View Related

Loop Through VBA Forms And Return Names

Nov 13, 2009

is there a way of looping through all forms that are in a VBA window and return their names and the controls within them?

View 2 Replies View Related

Index Workbook - Return Sheet Names

Dec 19, 2012

I have a workbook with a menu and 122 sheets holding detail for unique item (product model).

On the menu there is an index of the sheet names which was hand typed. However, the sheet names change as models evolve/get replaced.

I've written a small macro to put the sheet name in cell J1 of each sheet.

[Code] ......

On my menu page i was hoping to do something like this:

A B
Index NAME
2 =sheet[A2].$J$1
3 =sheet[A3].$J$1

That way i could just drag this down and my index list would complete itself and would dynamically update as sheet names change....

View 5 Replies View Related

Lookup And Return Names With Non-Consecutive Rows

Nov 6, 2013

Having an issue with an array formula.

{IFERROR(INDEX('sheet2'!$A$2:$A$14,SMALL(IF('sheet2'!$C$2:$C$14="X",
ROW('sheet2'!$A$2:$A$14)-ROW('sheet2'!$A$1)),C$2),1),"")}.

Originally I did not need to skip any rows, but now I have to skip certain rows. I've tried using my current array formula and skipping the rows not needed but can't get it to work.

{IFERROR(INDEX('sheet2'!$A$2:$A$5,$A$9:$A$14:SMALL(IF('sheet2'!$C$2:$C$5,$C$9:$C$14="X",
ROW('sheet2'!$A$2:$A$5,$A$9:$A$14)-ROW('sheet2'!$A$1)),C$2),1),"")}.

Can this still be done with an array formula? I need to skip rows 6, 7, 8. Not sure what I'm doing wrong but no names are appearing. Sheet 1 looks like this with "Job Titles" starting in A2 on sheet1.

Job Titles
Dept.
1
2
3
4

Claims
CSI

Outb Calls
CSI

Notifications
CSI

Evaluator
CSI

I tried a new formula in cell C3

:=IFERROR(INDEX(Sheet2!$A$2:$A$14,SMALL(IF(ROW(Sheet2!$C$2:$C$14)=9,
IF(Sheet2!$C$2:$C$14="x",ROW(Sheet2!$A$2:$A$14)-ROW(Sheet2!$A$1)))),C$2),1),"") entered as an array.

I should see the name "C. Pepper" followed by "L. Smith" when I copy the formula to the right into D3, "J. Rast" would be skipped as these are the rows not being picked up, but nothing is showing.

Sheet 2 looks like this with "Staff Name" starting in cell A1:

Staff Name
Mgr
Claims
Outb Calls
Notifications
Evaluator

A. Hawkins
Adams

[Code] ..........

View 3 Replies View Related

Return Offset Details For Duplicate Names

Feb 21, 2008

I have a list of about 1000 names with 2 columns next to it. The first contains a list of different groups that the names belong to (there are 19 possible groups). The second contains a list of figures ranging between 10,000 and 500,000. I want a formula that will look for a name in the list and, when it finds the name, return the corresponding group and corresponding amount. My problem is that some names are in more than one group and therefore appear in column 1 more than once. I have tried to use the LOOKUP formula [eg looking for the GROUP =LOOKUP(Sheet1!B33, Sheet2!A:A, Sheet2!B:B)] but when a name appears more than once I cant get all the results - for example:

Mr Phil LittleGreen Group35,000
Mr Ed GrantRed Group20,000
Mr Robert SmithOrange Group10,000
Mr Robert SmithRed Group50,000
Mr Robert SmithGreen Group75,000
Mr James MorrisonGreen Group100,000

On my summary page (Sheet1) I would like to type in Mr Robert Smith (into cell Sheet1!B33) and in the rows below (obviously in 9 different cells) have the results appear but I can only ever get Mr Robert SmithGreen Group75,000 as a result. This is my first post so I hope I have obeyed all the rules and this is clear

View 5 Replies View Related

CountIF With 2 Names And Return Amount Of Matching Times

Nov 26, 2012

I'm trying to do a countif that matches 2 names and returns the amount of times the 2 name matches is in the list. Ihave first names in column a and the surnames in column B.

And there are lots like so

John
Greenwood

Bill
Griffiths

Melanie
Griffiths

View 3 Replies View Related

Lookup Names Within Text And Return That Name To Separate Column If Found

Jun 13, 2013

I'm using a formula to lookup names within text and return that name to a separate column if it's found.

The formula is =INDEX($E$1024:$E$1026,MATCH(FALSE,ISERROR(FIND($E$1024:$E$1026,E1010)),0))

I understand index and match functions, but I'm confused regarding the use of FALSE as the lookup value and the iserror(find( usage.

View 2 Replies View Related

Basic Cell Referencing - Return Correct Names And Values On Another Sheet?

May 13, 2013

Here's the data table being referenced

Rank
Week Ending
Name
Value

1
1/1
Apple
100

[Code] ........

Now on another sheet, I want to return the top two 'Name's and their values like below:

Name
Value

There is a fluctuating amount of rows in the first table, too. So what formula can I use to return the correct names and values on another sheet?

I'm thinking it will use some form of concatenate for the first and use a sumifs function for the value column..

View 1 Replies View Related

Worksheet Tab Names

Dec 23, 2008

Is there a way of linking the worksheet tab names to a cell in the spreadsheet

View 9 Replies View Related

Using Names On The Worksheet In Cell A1?

May 7, 2013

there is a named range on the worksheet in cell A1- "GiGS"

I want to type "GIGS" in 1st cell and then in 2nd one to write formula "=A1*5". So excel need to understand that in A1 there is a name and not just a string

View 3 Replies View Related

Changing Worksheet Names Anywhere Between 50-100

Jan 24, 2008

I would like to change the worksheets names (many, anywhere between 50-100) to a cell (A2) value in each of the worksheets.

View 9 Replies View Related

Worksheet Names In ComboBox

Jul 17, 2008

What I want to do is have a ComboBox (I think it should be a ComboBox, I want the user to be able to select multiple options at once) and display the names of the worksheets in the ComboBox. I could not find a way to do this directly with rowsource and thought about writing a loop to check the current worksheets with their names and put the values into cells, to this way have the names displayed in the ComboBox via rowsource?

View 9 Replies View Related

Dynamic Worksheet Tab Names

Jan 25, 2010

I have this logic that clears cells in all WS in WB.

The logic is using each sheet name to reference the logic to clear the contents.
Look:

Private Sub CommandButton1_Click()
ActiveSheet.Unprotect Password:=""
With Application
.ScreenUpdating = False
.EnableEvents = False
Response = MsgBox("This Action Will Prep For A New Week. Do you want to Continue?", vbYesNo)
If Response = vbNo Then
Exit Sub
End If

Range("H7") = Now()

Sheets(Array("Wednesday", "Thursday", "Friday", "Saturday", "Sunday", "Monday", _
"Tuesday")).Select
Sheets("Wednesday").Activate
Range("B9:H25").Select

But I also want to change each sheet tab name to reflect a cell that has the date. But how to do this with the sheet reference in the logic? Couldit be dynamic?

View 9 Replies View Related

List All Worksheet Names

Dec 5, 2006

Is there a way in VBA to get a list of all worksheet names in a workbook?

View 2 Replies View Related

Get List Of Tab Names In Close Worksheet?

Aug 12, 2014

Looking for a way to use VBA to get a list of the tab names from a closed workbook and put them in a column of the worksheet that has the macro. Is there a good way to do that?

View 1 Replies View Related

Email To A List Of Names From Within Worksheet

Nov 9, 2008

I currently have a workbook that copies the first sheet and emails it to an individual on the first day of the week.

New requirements are for it to be sent to a list of people.

I am at an impasse on how to proceed due to lack of knowledge. The current code obtains the recipient from Sheet3 D4 and i would like to be able to list down this column for additional emails without restricting this to a set number of cells.

I have searched the forum and have been unable to find what i am after.

I have included a test file to show what i have so far.
We use Outlook 2k3
We use Excel 2k3

View 12 Replies View Related

Get Names Of All Worksheets In One Worksheet Without VBScript

Jun 18, 2012

Basically I want to be able to get the names of all sheets in a workbook. I know how to get Excel to print the name of the sheet in a cell once the file is saved, and I know how to hide sheets to make sure that all sheets are referenced in a range. If the name of the worksheet is in the same cell across all sheets (except the ones I don't want included on the totals page), is there a way to call that range and have Excel print off all the worksheet names in different rows or columns via a fill function?

Difficulties: the user may add/delete, rename, and have any number of, worksheets in the workbook. Because these variables are unpredictable, I am having difficulty figuring out the way to do this. Also, the formula should be able to ignore the sheets which have no value in the expected cell.

View 5 Replies View Related

Assigning Variables To Worksheet Names VBA

Jun 6, 2014

How to assign a "WS(i)" worksheet name and have the assigned variable (i) in the name equal the consecutive steps in a total page count.

Here's the code that does not work:

Code:
Dim WB as Workbook
Dim WS as Worksheet

Set WB = ThisWorkbook
For i = 1 To WB.Sheets.Count
Set WS(i) = WB.Sheets(i)
Next

I can see why it does not work. I can't figure out what to declare in my Dim or how to word the statement using the (i) variable in order to capture the value to attach to "WS."

Details: The issue is that the number of pages created will vary day to day depending on staff needs. There's an array of staff names to be assigned to page tabs and there will be skips and deletions along the way depending on daily attendance so "WS(i)" worksheet names can't be assigned at creation. What happens if they are is that the succession of worksheet names winds up being (e.g.) "WS1, WS2, WS5, WS12," etc. instead of consecutive.

There's a very complex data-sort-and-assign system that depends upon how many staff-assigned sheets there are, and as such each sheet has to have a "WS" worksheet name for the process to stay smooth. If the names are not consecutive, though, the sort-and-assign process becomes confused. This step takes place at the end of the initial workbook set up process, immediately before the data are addressed, so there really isn't an earlier opportunity. It can't be later since from this point on the system depends upon the "WS(i)" names being there.

It started fine when there were 5 people. Now there are 37.

View 2 Replies View Related

Dynamic List Of Worksheet Names

Apr 22, 2009

Is it possible to have a list in a summary sheet, containing the names of all other worksheets in the workbook, which will automatically update when a worksheet is added or deleted? I use Excel 2003.

View 9 Replies View Related

Define Worksheet Level Names

Oct 26, 2009

I was trying to re-create an example from Chapter 4 the 2nd Edition of Professional Excel Development in which there is a data validation list,
that is conditionally populated according to the selection of another data validation list.

When you choose b/t Fruits or Vegetables (Source:=Categories) in column D, the list under Item (column E) will read as a group of Fruits or Vegetables (Source:=If(IsBlank(D4),"",Indirect(D4)).

When I look at the Define Name dialog box, there is a column indicating the sheet name, which I cannot re-create/build myself. My first question is how do I do this, and secondly, the 2nd formula above will not work when I plug it into the data validation, but it may be on account of these worksheet level names. If you guys can see another reason why, I'm all ears (or eyes as it were).

UPDATE: Of course, I just tried it again this morning and it worked, but i would still lke to know how to use Worksheet/book level names.

View 9 Replies View Related

Add Worksheet With Dates As Names & No Weekends

Oct 2, 2006

Is it possible to make a macro that creates a Pop-up box that asks for date ranges and generates copies of the first worksheet in the workbook labelled as dates? There are ALWAYS 6 half terms to a school year so a 6x2 pop up box / table that is formatted in the following way will allow the appropriate date ranges to be inputted (saturdays and sundays must be excluded from this range):

Title of input box: Input School Half Term Date Ranges

******** Start Date ** End Date **
Autumn 1 03.09.06 18.09.06
Autumn 2
Spring 1
Spring 2
Summer 1
Summer 2

The macro would then create a series of sheets from 03.09.06 to 18.10.06 as well as the other 5 date ranges (excluding weekends) using the first sheet as a template. Would the user have to also input the start day for each half term or would excel 'know that 04.09.06.06 is a Monday and 18.10.06 is a Wednesday?

If the macro could create worksheets for these date ranges and clone the first worksheet in the workbook then that would save me LOADS of time copying and pasting each year.

View 9 Replies View Related

Retrieve All The Macro Names In A Worksheet

Oct 25, 2006

Is there any way to retrieve all the macro names in a worksheet?

View 2 Replies View Related

Fill ComboBox With Worksheet Names

Jul 6, 2007

I have a form created, were the user can select up to three pairs of open worksheets for sheet subtraction, to see where Im going with, aka the big idea see here: Column Subtraction With Criteria Via Box & Produce Results In New Workbook

What I dont know how to do, is get the form in the attached workbook to show all the available worksheets. Specifically, from the dropdown combo box, I would like it to list all open worksheet title in all open workbooks.

View 8 Replies View Related

List All Worksheet Names Of Workbook

May 5, 2008

I am trying to implement this formula in my file: = SUMPRODUCT(SUMIF(INDIRECT("'"&$H$1:$H$20&"'!A1:A100"),"criteria1",INDIRECT("'"&$H$1:$H$20&"'!B1:B100"))). I have over 35 sheets in this file with different names. Is there a way to export all the sheet names in list format so that I don't have to go back and forth in the file and type the names?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved