Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
gabriel_e
 
Posts: n/a
Default Worksheet name / reference as a formula?

Hello.

I have a Workbook where there are many worksheets; the name of each is a
date, i.e. "1-21", "1-22", "1-23", etc. I have a summary sheet that
centralizes data based on the individual worksheets into a usable form. Every
day I add another worksheet with a new date. This system does not need to be
scalable as I will only be doing this for 6 months. On the summary sheet, I
have VLOOKUP equations that reference to worksheets pulling data for each
date so that the same piece of data may be compared over time in a column.
The problem is, every time I add another worksheet, I have to manually change
the VLOOKUP formula to reference the newly minted sheet.

For instance, here is my formula:

=VLOOKUP(B$3,'1-21'!$E$2:$I$31,5,FALSE)

'1-21' is the worksheet. When I add a sheet for '1-22' I will have to copy
this formula and then manually change the worksheet name.

What I would like to do is change the worksheet name to a formula, so that I
dont have to manually change it each time.

However, I cant get this to work at even the most basic level, which would
be to have a cell with €œtext€ formatting with the text €œ1-22€ and then
reference to it from within my VLOOKUP formula.

If that would work, I would have the relative worksheet formula access the
date column which labels the date each row represents so that instead of
something like this:

'1-21'

I would have something like this:

'MONTH(A15)&"-"&DAY(A15)

That equation produces the text €œ1-21€ and adjusts to the relevant date.

Unfortunately, I cant get the worksheet name to be relative in any way.

Is there any way to turn the worksheet name into a formula, so that it may
be altered?

Is anything like this possible?

Thank you.

Using:
Excel 2003
  #2   Report Post  
Posted to microsoft.public.excel.misc
Biff
 
Posts: n/a
Default Worksheet name / reference as a formula?

Hi!

Enter the sheet name in a cell:

A1 = 1-22 (or whatever)

=VLOOKUP(B$3,INDIRECT("'"&A1&"'!E2:I31"),5,0)

Biff

"gabriel_e" wrote in message
...
Hello.

I have a Workbook where there are many worksheets; the name of each is a
date, i.e. "1-21", "1-22", "1-23", etc. I have a summary sheet that
centralizes data based on the individual worksheets into a usable form.
Every
day I add another worksheet with a new date. This system does not need to
be
scalable as I will only be doing this for 6 months. On the summary sheet,
I
have VLOOKUP equations that reference to worksheets pulling data for each
date so that the same piece of data may be compared over time in a column.
The problem is, every time I add another worksheet, I have to manually
change
the VLOOKUP formula to reference the newly minted sheet.

For instance, here is my formula:

=VLOOKUP(B$3,'1-21'!$E$2:$I$31,5,FALSE)

'1-21' is the worksheet. When I add a sheet for '1-22' I will have to copy
this formula and then manually change the worksheet name.

What I would like to do is change the worksheet name to a formula, so that
I
don't have to manually change it each time.

However, I can't get this to work at even the most basic level, which
would
be to have a cell with "text" formatting with the text "1-22" and then
reference to it from within my VLOOKUP formula.

If that would work, I would have the relative worksheet formula access the
date column which labels the date each row represents so that instead of
something like this:

'1-21'

I would have something like this:

'MONTH(A15)&"-"&DAY(A15)'

That equation produces the text "1-21" and adjusts to the relevant date.

Unfortunately, I can't get the worksheet name to be relative in any way.

Is there any way to turn the worksheet name into a formula, so that it may
be altered?

Is anything like this possible?

Thank you.

Using:
Excel 2003



  #3   Report Post  
Posted to microsoft.public.excel.misc
Arvi Laanemets
 
Posts: n/a
Default Worksheet name / reference as a formula?

Hi

Additionally, you can use an UDF to get a list of sheet names to summary
sheet, like
Public Function TabI(TabIndex As Integer, Optional MyTime As Date) As String
' returns the name of n-th sheet in workbook,
' use TODAY() or NOW() as optional parameter
' to make the function to recalculate automatically
TabI = Sheets(TabIndex).Name
End Function

Copy the code into workbook's module. On your master sheet, p.e. into cell
A4 enter the formula:
=IF(ISERROR(TABI(ROW()-2,TODAY())),"",TABI(ROW()))
(I assume the Summary sheet is the first one, i.e. the leftmost tab)
, and copy the formula down for enough rows to get the list of all sheets
Now into cell B4 you can enter the formula
=IF(A4="","",VLOOKUP(B$3,INDIRECT("'"&A4&"'!E2:I31 "),5,0))
, and copy it down too - it's done! When you prepared enough rows, whenever
you add a new sheet to your workbook, and keep summary sheet as 1st one,
your summary sheet is updated automatically.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Biff" wrote in message
...
Hi!

Enter the sheet name in a cell:

A1 = 1-22 (or whatever)

=VLOOKUP(B$3,INDIRECT("'"&A1&"'!E2:I31"),5,0)

Biff

"gabriel_e" wrote in message
...
Hello.

I have a Workbook where there are many worksheets; the name of each is a
date, i.e. "1-21", "1-22", "1-23", etc. I have a summary sheet that
centralizes data based on the individual worksheets into a usable form.
Every
day I add another worksheet with a new date. This system does not need to
be
scalable as I will only be doing this for 6 months. On the summary sheet,
I
have VLOOKUP equations that reference to worksheets pulling data for each
date so that the same piece of data may be compared over time in a
column.
The problem is, every time I add another worksheet, I have to manually
change
the VLOOKUP formula to reference the newly minted sheet.

For instance, here is my formula:

=VLOOKUP(B$3,'1-21'!$E$2:$I$31,5,FALSE)

'1-21' is the worksheet. When I add a sheet for '1-22' I will have to
copy
this formula and then manually change the worksheet name.

What I would like to do is change the worksheet name to a formula, so
that I
don't have to manually change it each time.

However, I can't get this to work at even the most basic level, which
would
be to have a cell with "text" formatting with the text "1-22" and then
reference to it from within my VLOOKUP formula.

If that would work, I would have the relative worksheet formula access
the
date column which labels the date each row represents so that instead of
something like this:

'1-21'

I would have something like this:

'MONTH(A15)&"-"&DAY(A15)'

That equation produces the text "1-21" and adjusts to the relevant date.

Unfortunately, I can't get the worksheet name to be relative in any way.

Is there any way to turn the worksheet name into a formula, so that it
may
be altered?

Is anything like this possible?

Thank you.

Using:
Excel 2003





  #4   Report Post  
Posted to microsoft.public.excel.misc
gabriel_e
 
Posts: n/a
Default Worksheet name / reference as a formula?

Thanks so much for your help!

This worked beautifully!

I ended up using:

VLOOKUP(B$3,INDIRECT("'"&MONTH($A42)&"-"&DAY($A42)&"'!$E$2:$I$32"),5,FALSE)

A42 has the appropriate date for the relevant row.

This will save me a ton of work, thanks again!



"Biff" wrote:

Hi!

Enter the sheet name in a cell:

A1 = 1-22 (or whatever)

=VLOOKUP(B$3,INDIRECT("'"&A1&"'!E2:I31"),5,0)

Biff

"gabriel_e" wrote in message
...
Hello.

I have a Workbook where there are many worksheets; the name of each is a
date, i.e. "1-21", "1-22", "1-23", etc. I have a summary sheet that
centralizes data based on the individual worksheets into a usable form.
Every
day I add another worksheet with a new date. This system does not need to
be
scalable as I will only be doing this for 6 months. On the summary sheet,
I
have VLOOKUP equations that reference to worksheets pulling data for each
date so that the same piece of data may be compared over time in a column.
The problem is, every time I add another worksheet, I have to manually
change
the VLOOKUP formula to reference the newly minted sheet.

For instance, here is my formula:

=VLOOKUP(B$3,'1-21'!$E$2:$I$31,5,FALSE)

'1-21' is the worksheet. When I add a sheet for '1-22' I will have to copy
this formula and then manually change the worksheet name.

What I would like to do is change the worksheet name to a formula, so that
I
don't have to manually change it each time.

However, I can't get this to work at even the most basic level, which
would
be to have a cell with "text" formatting with the text "1-22" and then
reference to it from within my VLOOKUP formula.

If that would work, I would have the relative worksheet formula access the
date column which labels the date each row represents so that instead of
something like this:

'1-21'

I would have something like this:

'MONTH(A15)&"-"&DAY(A15)'

That equation produces the text "1-21" and adjusts to the relevant date.

Unfortunately, I can't get the worksheet name to be relative in any way.

Is there any way to turn the worksheet name into a formula, so that it may
be altered?

Is anything like this possible?

Thank you.

Using:
Excel 2003




  #5   Report Post  
Posted to microsoft.public.excel.misc
gabriel_e
 
Posts: n/a
Default Worksheet name / reference as a formula?

Arvi,

I did not get a chance to try your suggestion as I implemented Bill's
solution first and that solved my issue.

I appreciate your help!

Thanks!

"Arvi Laanemets" wrote:

Hi

Additionally, you can use an UDF to get a list of sheet names to summary
sheet, like
Public Function TabI(TabIndex As Integer, Optional MyTime As Date) As String
' returns the name of n-th sheet in workbook,
' use TODAY() or NOW() as optional parameter
' to make the function to recalculate automatically
TabI = Sheets(TabIndex).Name
End Function

Copy the code into workbook's module. On your master sheet, p.e. into cell
A4 enter the formula:
=IF(ISERROR(TABI(ROW()-2,TODAY())),"",TABI(ROW()))
(I assume the Summary sheet is the first one, i.e. the leftmost tab)
, and copy the formula down for enough rows to get the list of all sheets
Now into cell B4 you can enter the formula
=IF(A4="","",VLOOKUP(B$3,INDIRECT("'"&A4&"'!E2:I31 "),5,0))
, and copy it down too - it's done! When you prepared enough rows, whenever
you add a new sheet to your workbook, and keep summary sheet as 1st one,
your summary sheet is updated automatically.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Biff" wrote in message
...
Hi!

Enter the sheet name in a cell:

A1 = 1-22 (or whatever)

=VLOOKUP(B$3,INDIRECT("'"&A1&"'!E2:I31"),5,0)

Biff

"gabriel_e" wrote in message
...
Hello.

I have a Workbook where there are many worksheets; the name of each is a
date, i.e. "1-21", "1-22", "1-23", etc. I have a summary sheet that
centralizes data based on the individual worksheets into a usable form.
Every
day I add another worksheet with a new date. This system does not need to
be
scalable as I will only be doing this for 6 months. On the summary sheet,
I
have VLOOKUP equations that reference to worksheets pulling data for each
date so that the same piece of data may be compared over time in a
column.
The problem is, every time I add another worksheet, I have to manually
change
the VLOOKUP formula to reference the newly minted sheet.

For instance, here is my formula:

=VLOOKUP(B$3,'1-21'!$E$2:$I$31,5,FALSE)

'1-21' is the worksheet. When I add a sheet for '1-22' I will have to
copy
this formula and then manually change the worksheet name.

What I would like to do is change the worksheet name to a formula, so
that I
don't have to manually change it each time.

However, I can't get this to work at even the most basic level, which
would
be to have a cell with "text" formatting with the text "1-22" and then
reference to it from within my VLOOKUP formula.

If that would work, I would have the relative worksheet formula access
the
date column which labels the date each row represents so that instead of
something like this:

'1-21'

I would have something like this:

'MONTH(A15)&"-"&DAY(A15)'

That equation produces the text "1-21" and adjusts to the relevant date.

Unfortunately, I can't get the worksheet name to be relative in any way.

Is there any way to turn the worksheet name into a formula, so that it
may
be altered?

Is anything like this possible?

Thank you.

Using:
Excel 2003






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
What is the formula to reference a cell in a different worksheet? Jenney Zellner Excel Worksheet Functions 2 October 14th 05 09:26 AM
How to use a cell value to reference a worksheet name S2 Excel Worksheet Functions 2 October 10th 05 03:02 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 07:48 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 03:28 AM
Reference the worksheet from a multiple worksheet range function ( DBickel Excel Worksheet Functions 1 May 28th 05 03:49 AM


All times are GMT +1. The time now is 01:53 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"