Free Microsoft Excel 2013 Quick Reference

Reference tab name in formula

I would like to create a formula that lets me reference a tab name without going to each tab and selecting the relevant cell. for example, i have tabs numbered 101, 102 etc.
The list will be (which is the same as a tab name)
101
102
I want to reference the list as the tab name and then the cell reference. ie the formula will look like +'101'!A1, but 101 will be referenced.
Hope this makes sense.


Post your answer or comment

comments powered by Disqus

	VB:
	
RowsBegin = 848 
RowsEnd = 863 
sName = Sheets(ActiveSheet.Index + 6).Name 
 
If Range("W" & RowsBegin) = "CMAI" Then 
     
    Range("BI" & RowsBegin).Select 
    ActiveCell.FormulaR1C1 = _ 
    "=IF(CMAI!R3C3=0," '"& sName& "'!R[-427]C[-24]"*1,IF(RC[-35]=""1˘ for
1˘"",sName!R[-427]C[-24]+CMAI!R3C3,IF(AND(RC[-35]=""1% for
1˘"",CMAI!R3C3>0),(1+CMAI!R3C3)*sName!R[-427]C[-24],IF(AND(RC[-35]=""1% for 1˘"",CMAI!R3C3

My users are able to change the tab names of the worksheets. I know that in VBA I can still refer to the worksheets as "sheet1" "sheet2" irrespective of their tab names. But I need to use some simple formulas in one worksheet that refer to another worksheet cell and Excel is trying to make me use the worksheet tab name as a prefix. But this is variable.

eg, on sheet 2 cell C5 put result of cells A1 + A2 off of Sheet1.

How can I do this using formulas but ignoring the users tab names?
Thanks
Bill

Hello..thanks for your help in this..

I have a workbook with many tabs (worksheets). The last is a spreadsheet
(let's call it Tab#10) with each row drawing data from different places in
each of the worksheets...so that row 1 will draw data from tab#1, row two
from tab #2...etc.

I would like to manually link the cells in row one (in Tab#10) to tab#1, and
then drag down to fill the rest of the rows (in Tab#10). I was then going to
use the "search & replace" feature to replace the reference to tab#1 by the
correct tab name for row 2 onwards.

QUESTION: There must be a smarter/quicker/more efficient way of doing this,
and I know one of you Excel whizzes knows it!!

p.s. I am not a macro expert..but I just copied a macro code from this site
for automatically naming tabs and it WORKED - I am SO happy.. so I'm
encouraged.

Hello all,

I have two separate workbooks with an equal number of worksheets. In workbook 1, each sheet has a person's name (John Doe, Susan Smith, etc.) in cell c6. In workbook 2, each sheet is named after one of those people. That is, workbook 2 has worksheet tabs John Doe, Susan Smith, etc. The worksheets in workbook 1 do not have cooresponding names, but they could have if it made the solution simpler.

I want to perform the same set of calculations on each sheet in workbook 1, by including in the formulas the related data from workbook 2, based on the match between the name in cell c6 of each sheet in workbook 1 and the tab name in workbook 2.

I would prefer a link formula, but would be willing to use a macro. I do not think I need anyone to write the macro for me. Whether link or macro, I only need to know how, if possible, the reference is coded.

I don't know if what follows will help or confuse, but essentially, I want to incorporate a set of calculations into each sheet of workbook 1 of the following nature:

content of wb1 sheet 1 cell m20: = value of wb1 sheet 1 cell m7 * value of wb2 cell a20 on sheetname with value of wb1 sheet 1 cell c6.
content of wb1 sheet 1 cell m21: = value of wb1 sheet 1 cell n7 * value of wb2 cell a21 on sheetname with value of wb1 sheet 1 cell c6.
content of wb1 sheet 1 cell m22: = value of wb1 sheet 1 cell o7 * value of wb2 cell a22 on sheetname with value of wb1 sheet 1 cell c6.

content of wb1 sheet 2 cell m20: = value of wb1 sheet 2 cell m7 * value of wb2 cell a20 on sheetname with value of wb1 sheet 2 cell c6.
content of wb1 sheet 2 cell m21: = value of wb1 sheet 2 cell n7 * value of wb2 cell a21 on sheetname with value of wb1 sheet 2 cell c6.
content of wb1 sheet 2 cell m22: = value of wb1 sheet cell o7 * value of wb2 cell a22 on sheetname with value of wb1 sheet 2 cell c6.

Thanks in advance to anyone who can help.

Conor

Hello..thanks for your help in this..

I have a workbook with many tabs (worksheets). The last is a spreadsheet
(let's call it Tab#10) with each row drawing data from different places in
each of the worksheets...so that row 1 will draw data from tab#1, row two
from tab #2...etc.

I would like to manually link the cells in row one (in Tab#10) to tab#1, and
then drag down to fill the rest of the rows (in Tab#10). I was then going to
use the "search & replace" feature to replace the reference to tab#1 by the
correct tab name for row 2 onwards.

QUESTION: There must be a smarter/quicker/more efficient way of doing this,
and I know one of you Excel whizzes knows it!!

p.s. I am not a macro expert..but I just copied a macro code from this site
for automatically naming tabs and it WORKED - I am SO happy.. so I'm
encouraged.

Is there a way to list the formulas that reference a name in workbook?
I'm working with a workbook that has around 100 names defined and need to
identify if there are forumulas that are using those names?

I can get a list of names and ranges but am looking for a way to list name,
range and formula.

how do I place the sheet tab name in my spreadsheet by formula?

Excel 2003

I use a macro to make multiple copies of a tab (template). The macro will number each tab automatically as it creates them. What I want to develop is formula that will reference the tab name. This formula would take the tab name (a number) and refer to a master sheet and use that number in a VLOOKUP function to pull data and then populate the new sheet with data from the master sheet based upon the name of the new tab.

Any advice would be helpful.

Thanks,
Allen

Hello all -

I am trying to do the following:

Sheet 1 contains:

Item Sheet 2
ABC =vlookup(A2,'Sheet 2'!$A:$B,2,false)

Sheet 2 contains:

Item Data
ABC 2

I'm trying to get the vlookup to return the value "2"

Right now, I'm manually entering the tab name in the vlookup function, even though it's contained in cell B1.

The tabs are contained in the same workbook if that matters. Since this workbook is growing rather quickly, this is a painful process and doesn't feel very scalable. Since I'm using a mac, I need to do this with functions vs. macros. Does anybody know how I can reference a cell for the name of a tab in the vlookup function?

I was thinking I could maybe somehow do this with the INDIRECT function but I'm stumped.

I apologize if this wasn't clear but it seems to be a slightly convoluted question.

Thanks in advance for the help.

I'd like to use the sheet tab name in a formula. I'd actually like to fill
in some fields including the sheet tab name. Is there an easy way to do
this?

Thanks in advance.

Barb Reinhardt

I have a spreadsheet with 50 tabs - each one numbered 1 to 50, so that the
first tab is named '1', the second is named '2' and so on.

On a summary spreadsheet, I have formulas pulling the same cell (A44) from
each tab, so that:
the formula in cell A1 looks like this ='1'!A$44 and
the formula in cell B1 looks like this ='2'!A$44 and so on.

Is there a way to get Excel to "feed" the tab names to the formula without
the help of Visual Basic. For example, I would like to list the tab names in
the spreadsheet (1 to 50) and have a formula in the cells that would
incorporate the tab names.
Tab Formula
1 ='1'!A$44
2 ='2'!A$44
3 ='3'!A$44

Does that make sense? If so, is there any way to do this?

Using a formula, is there a way to display worksheet (tab names) in a cell?
For example, I have 3 worksheets with names One, Two and Three. In cell A1 in
each woksheet, I would want to see the worksheet name for whichever worksheet
I am viewing. I do not want the name to change in the cell each time I click
on one of the other two spreadsheets, similar to what happens with
=CELL("FILENAME"). I also do not want to see the entire path for the
worksheet.

Hi Excelling,

Look on Chip Pearson's formula page under Miscellaneous for an example of
how to do this:

http://www.cpearson.com/excel/excelF.htm

--
Regards,

Jake Marx
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Excelling in MN wrote:
> Version: Microsoft Office Excel 2003
>
> How can I repeat a sheet's tab name in a cell?
>
> I have a summary sheet which has data fed from multiple other
> supporting sheets. The supporting sheets are updated by other users
> and they modify the tab name so it reflects their specific subproject
> effort.
>
> Just as the summary sheet reflects the supporting sheet data, it
> would be very helpful to state the tab name so it is clear what the
> data is representing (i.e.- the subproject sheet name). I wish to
> create a link so if the subproject (tab) name is changed, the summary
> sheet is automatically updated.
>
> I had hoped this would be similar to adding the file name and path in
> a cell: =cell("filename") however, it is not.

I currently use both Excel 97 & Excel XP, is there a way by formula to
include the worksheet tab name in a cell similar to &[tab] in the
header/footer?
--
Mark

Version: Microsoft Office Excel 2003

How can I repeat a sheet's tab name in a cell?

I have a summary sheet which has data fed from multiple other supporting
sheets. The supporting sheets are updated by other users and they modify the
tab name so it reflects their specific subproject effort.

Just as the summary sheet reflects the supporting sheet data, it would be
very helpful to state the tab name so it is clear what the data is
representing (i.e.- the subproject sheet name). I wish to create a link so
if the subproject (tab) name is changed, the summary sheet is automatically
updated.

I had hoped this would be similar to adding the file name and path in a
cell: =cell("filename") however, it is not.

I am creating a register of first aid kits on my site. What I want to do is have a summary as the first worksheet, and then have contents, and individual expiry dates for each kit on seperate kits. Is there a way to autofill, or auto complete, a different sheet name in formulas?

Ie.
='Kit 1'!$B$27
='Kit 2'!$B$27
='Kit 3'!$B$27
...

='Kit 44'!$B$27

I'm trying to find some non vba approach to grabbing the tab names in workbook A and assigning them to a group of cells in workbook B
I have no clue...

A backup plan would be to go with vba

=Table_Query_from_MS_Access_Database[[#This Row],[Rate]]*B12 Still frustrated by absurd assumptions made by MS developers. But, I have unchecked the [ Use table names in formulas], etc. Still getting table names in formulas and [#This Row] that I do not care for either.

How is this bug feature disabled?

TIA

I am trying to find an easy way to insert the tab name in the body of the
worksheet. I know in headers & footers you can use &[TAB] but I can't seem
to make that work in the worksheet. Any help will be greatly appreciated.

Version: Microsoft Office Excel 2003

How can I repeat a sheet's tab name in a cell?

I have a summary sheet which has data fed from multiple other supporting
sheets. The supporting sheets are updated by other users and they modify the
tab name so it reflects their specific subproject effort.

Just as the summary sheet reflects the supporting sheet data, it would be
very helpful to state the tab name so it is clear what the data is
representing (i.e.- the subproject sheet name). I wish to create a link so
if the subproject (tab) name is changed, the summary sheet is automatically
updated.

I had hoped this would be similar to adding the file name and path in a
cell: =cell("filename") however, it is not.

I have a rather large workbook. I would like to have a link to each tab on
the first tab (worksheet) in the workbook. What I envision is a cloumn of
cells with the sheet names in cells. When the cell or text is clicked on, the
user is taken to the corresponding worksheet.

Also, is there a way in VBA to get the tab names in order to populate the
cells?

Thanks

I'd like to refer to a tab name automatically in a cell formula, just like
you can autoinsert it as in the page header. Is this possible?

I have a "CURRENT MONTH" tab in a workbook; in that Workbook, I have a standard report that pulls Total Revenues from cell C6 of the tab I want to reference.

For example: In my "CURRENT MONTH" tab B6 has the label "CURRENT MONTH REVENUES" C6 has the formula "='May 08'!C6"

Every month I have to change that formula to "='Jun 08'!C6" as an example for June. (it's not just one formula, there are dozens referencing May that I need to change to Jun).

I could do an Edit/Replace, but I'd rather just type in the current month tab name in another cell and have the formulas pulling the data referencing that cell.

Can anyone help???
THANKS IN ADVANCE!

So, I was wondering if this was possible in Excel.

I'm setting up a statistics workbook, and I have formulas that retrieve data from a pivot table (GETPIVOTDATA). The name of the tab that this formula is on matches a value in the pivot table. Can I put something in the formula that will check the tab name? I have to create about 30-40 different tabs, and it would be easier if I can just reference a tab name rather than changing the value in the formula for each tab.

Is this possible?

Thanks!
Joe Bouchard


No luck finding an answer? You could always try Google.