Creating worksheets named by cell values and using the worksheet name in formulas

DeweySanchez

New Member
Joined
Jul 11, 2008
Messages
4
I am new to VB and excel macros. I am currently writing what is turning out to be quite a complicated macro (though probably mainly because of the way I am doing it:) ). Part of the macro creates new worksheets based on the value of cells in a list, so i get a one worksheet for each different cell value and the worksheets name=that cells value. However, I would like to be able to refer to those worksheets in formula on other worksheets i.e ="NAME!"B2. The problem is that some of the cells contain values such as N%, so although it will make a worksheet called that I cannot refer to it in a formula. As such I need to know how to do one of three ways (unless anyone can think of another way).

1. Change awkward cell values before I make the worksheets
2. Rename the awkwardly named worksheets after they are made
3. Be able to use the awkward worksheet names in a formula

Please help, there is already smoke coming out of my ears and I fear and mushroom cloud will blossom from the top of my head soon.

Dewey
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,079
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
For 3, you need to enclose the names in single quotes:
='N%'!A1
for example.
 

drWatson

Board Regular
Joined
Feb 9, 2007
Messages
55
hello,
if you want to get rid of the '%' sign in the eg. in sheet name (and refer to it using that name afterwards), you can do this function:

Code:
strSheetName = replace(strSheetName, "%","")

This will effectively remove the "%" sign.

Hope this helps,
drW
 

thomasbeard

Board Regular
Joined
Oct 19, 2005
Messages
139

ADVERTISEMENT

Use an Indirect function. For example, if the name of the sheet (e.g. N%) is in cell A1 and you want to bring back the value from B1 within N%, use:

=INDIRECT("'"&A1&"'!B1")
 

cornflakegirl

Well-known Member
Joined
Nov 4, 2004
Messages
2,023
I think we may be overcomplicating here guys - I suspect it's just a syntax error. As Rory says, ='N%'!A1 works fine.
 

DeweySanchez

New Member
Joined
Jul 11, 2008
Messages
4
WOW! you guys are fast! and very useful.

I had this piece of code to do the work for me:
Do While Cells(f, 1) <> "Sheet2"
MySheet = Cells(f, 1).Value
Cells(f, 3).Value = "=" & MySheet & "!G6"
Cells(f, 4).Value = "=" & MySheet & "!G7"
f = f + 1
Loop

and it would work until it hit a cell (and therefore worksheet) called N%

changing the ref to Cells(f, 3).Value = "='" & MySheet & "'!G6" makes it all work fine.

Thanks to all of you for your help, the other suggestions were great for other things I want to do.

Dewey
 

Watch MrExcel Video

Forum statistics

Threads
1,122,670
Messages
5,597,480
Members
414,143
Latest member
lonnie451

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top