Name of Sheet in Formula 1

[binaryfingers]

Hi
Is there a way of pulling the name of a sheet into a formula?

For Example 4 sheets

Sales April 2004
Sales May 2004
Sales June 2004
Output Sheet

What I have on the output sheet is a row of months, and Ideally it will then pull the data corresponding to the item number on a vlookup from the sheet with the corresponding month in it...

Look forward to the help..

Thanks,

 

[dyarwood]

Try

Sheets("Name of sheet")

eg

Sheets("Name of sheet").Range("A1") = "Hello

 

[binaryfingers]

HI dyarwood

Thanks for the quick response.

is there a way that the 'name of sheet' can be made up of the month being pulled from the month column

eg.
January February March etc being in adjacent cells, and this cell is referenced in the name of sheet to determine the month reference in the sheet name?

Thanks

 

[xlbo]

See my FAQ for getting workbook / sheet names - might be in the Office forum though !

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[dyarwood]

Sheets("Sheet3").Select
Sheets.Add
Sheets("Sheet4").Select
Sheets("Sheet4").Name = month_selected

Just need to get the name of the month and pass it to the variable month_selected.

Might have to replace Sheets("Sheet4") with ActiveSheet

 

[binaryfingers]

dyarwood

Thanks again for the help, Im getting closer although I am still missing that vital part.

the middle part of the sheet name is the part that needs to be dynamic (month)

I would like to be able to type the month into a cell and then the cell below it will reference the sales sheet with the correct month in the worksheet and pull a number from the appropiate cell

eg: ="Sales "&Cell&" 2004"!C5

Hope you can help further?

 

[xlbo]

Does this need to be in a formula or VBA ???
If it is a formula, see my FAQ in the MSOffice forum - btw - that should be where you post formula questions


You would need to use the formula in there to return the sheet name, along with the INDIRECT function to convert that string into a proper Sheet / range reference for the formula


Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[binaryfingers]

Geoff

Thanks for the response. sorry for posting in the wrong forum.

I looked at your FAQ and this where I am trying to modify what I need to get it to work, but still not much fun. I am still learning here all the time so sorry if I am missing something visible.

Do I convert all with Indirect?

 

[xlbo]

ok - don't think you need my FAQ - lets say you have the text "May"
in cell A5

your sheet is called:
Sales May 2004

In B5, you can enter
=INDIRECT("Sales " & A5 & " 2004!$A$1")

this will return the value in A1 on the Sales May 2004 sheet
.......that what you want ??

In general terms though, this is bad design - it is much better to keep all data together on one sheet and just use a TAG ie an extra column that has the Month name in it which can be used in formulae. Obviously, if the number of rows is too much or the data layout is funny, this may not be possible but it's worth bearing in mind for future reporting - it makes doing aggregation / summarisation MUCH easier

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[binaryfingers]

Geoff

Still getting a #REF error, can I send you a sample sheet so that you can see a sample of what I am trying to achieve?

 

[alexanderd]

try SHEET4! it's the end bit which tells the formulae where to go !!!!!!!!!!!!!!!!!!!!!!!!!!!

 

[xlbo]

What is the sales sheet called ??
what is in the relevant cell on the output sheet that you are trying to reference ??

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[xlbo]

sorry - just figured out what was wrong...left the ' ' out:
=INDIRECT("'Sales " & A5 & " 2004'!$A$1")



Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[binaryfingers]

Perfect!

Thanks for the continued support!