Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is the formula to reference a cell in a different worksheet? | Excel Worksheet Functions | |||
How to use a cell value to reference a worksheet name | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Reference the worksheet from a multiple worksheet range function ( | Excel Worksheet Functions |