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

[DeweySanchez]

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

 

[cornflakegirl]

Why can't you refer to them in a formula on another sheet?

 

[RoryA]

For 3, you need to enclose the names in single quotes:
='N%'!A1
for example.

 

[drWatson]

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:

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

This will effectively remove the "%" sign.

Hope this helps,
drW

 

[thomasbeard]

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]

I think we may be overcomplicating here guys - I suspect it's just a syntax error. As Rory says, ='N%'!A1 works fine.

 

[DeweySanchez]

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