Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Auto Reference Worksheet Tab ID's in Formulas?
In my workbook (2003) used to track disaster costs, I have some 60 different
worksheets. On the Summary page, the cells pull data from the individual worksheets. On the Summary page, there are some 60 rows. Each row pulls data from a different worksheet; from the same relative cells, just on different sheets. On the Summary page, the first column is text that identifies which tab (worksheet) that row's data is from. My difficulty is having to manually change the formulas in each cell, in every row, so that it pulls the data from the appropriate worksheet. There's gotta be a way for the formulas within each row, to know which worksheet to pull the data from, based upon the Id contents already in the first column of each row. If the cells in row 1 pull their respective data from the corresponding cells on worksheet "5," and the cells in row 2 pull their data from worksheet "6," I'm having to manually change each cell's formula to reflect the correct worksheet ID. For example: "='21'!$C$5" pulls the data from a specific cell in worksheet "21." On the next row, I have to manually modify all the formulas as follows so they reference the same cells on worksheet "22: "='22'!$C$5" Since "21" & "22" etc. are already identified in the first column of each row, is there a way the formulas could simply reference the correct worksheets based upon the reference in the first column? Something like: "='worksheet Id in 1st column'!$C$5" Thanks, Scott Fraser City of Key West, Florida |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Auto Reference Worksheet Tab ID's in Formulas?
=indirect("'" & A1 & "'!c5")
will try to retrieve the value from C5 of the sheet name located in A1. Scott - Key West wrote: In my workbook (2003) used to track disaster costs, I have some 60 different worksheets. On the Summary page, the cells pull data from the individual worksheets. On the Summary page, there are some 60 rows. Each row pulls data from a different worksheet; from the same relative cells, just on different sheets. On the Summary page, the first column is text that identifies which tab (worksheet) that row's data is from. My difficulty is having to manually change the formulas in each cell, in every row, so that it pulls the data from the appropriate worksheet. There's gotta be a way for the formulas within each row, to know which worksheet to pull the data from, based upon the Id contents already in the first column of each row. If the cells in row 1 pull their respective data from the corresponding cells on worksheet "5," and the cells in row 2 pull their data from worksheet "6," I'm having to manually change each cell's formula to reflect the correct worksheet ID. For example: "='21'!$C$5" pulls the data from a specific cell in worksheet "21." On the next row, I have to manually modify all the formulas as follows so they reference the same cells on worksheet "22: "='22'!$C$5" Since "21" & "22" etc. are already identified in the first column of each row, is there a way the formulas could simply reference the correct worksheets based upon the reference in the first column? Something like: "='worksheet Id in 1st column'!$C$5" Thanks, Scott Fraser City of Key West, Florida -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Auto Reference Worksheet Tab ID's in Formulas?
In A1 enter =INDIRECT(ROW() & "!$C$5")
Copy down to increment the sheets. Note: you can offset the ROW() to start at sheet 5 if you want. In A1 enter =INDIRECT(ROW(5:5) & "!$C$5") Copy down Gord Dibben MS Excel MVP On Mon, 10 May 2010 13:18:04 -0700, Scott - Key West wrote: In my workbook (2003) used to track disaster costs, I have some 60 different worksheets. On the Summary page, the cells pull data from the individual worksheets. On the Summary page, there are some 60 rows. Each row pulls data from a different worksheet; from the same relative cells, just on different sheets. On the Summary page, the first column is text that identifies which tab (worksheet) that row's data is from. My difficulty is having to manually change the formulas in each cell, in every row, so that it pulls the data from the appropriate worksheet. There's gotta be a way for the formulas within each row, to know which worksheet to pull the data from, based upon the Id contents already in the first column of each row. If the cells in row 1 pull their respective data from the corresponding cells on worksheet "5," and the cells in row 2 pull their data from worksheet "6," I'm having to manually change each cell's formula to reflect the correct worksheet ID. For example: "='21'!$C$5" pulls the data from a specific cell in worksheet "21." On the next row, I have to manually modify all the formulas as follows so they reference the same cells on worksheet "22: "='22'!$C$5" Since "21" & "22" etc. are already identified in the first column of each row, is there a way the formulas could simply reference the correct worksheets based upon the reference in the first column? Something like: "='worksheet Id in 1st column'!$C$5" Thanks, Scott Fraser City of Key West, Florida |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Auto Reference Worksheet Tab ID's in Formulas?
But make sure you put the sheet name in apostrophes:
=indirect("'" & row() & "'!c5") (the $ don't matter, since it's a string) And I like to use the =row() and adjust that: =indirect("'" & row() + 5 & "'!c5") That +5 has to be changed to match the row of the cell getting the formula and the worksheet from which the value should be retrieved. Gord Dibben wrote: In A1 enter =INDIRECT(ROW() & "!$C$5") Copy down to increment the sheets. Note: you can offset the ROW() to start at sheet 5 if you want. In A1 enter =INDIRECT(ROW(5:5) & "!$C$5") Copy down Gord Dibben MS Excel MVP On Mon, 10 May 2010 13:18:04 -0700, Scott - Key West wrote: In my workbook (2003) used to track disaster costs, I have some 60 different worksheets. On the Summary page, the cells pull data from the individual worksheets. On the Summary page, there are some 60 rows. Each row pulls data from a different worksheet; from the same relative cells, just on different sheets. On the Summary page, the first column is text that identifies which tab (worksheet) that row's data is from. My difficulty is having to manually change the formulas in each cell, in every row, so that it pulls the data from the appropriate worksheet. There's gotta be a way for the formulas within each row, to know which worksheet to pull the data from, based upon the Id contents already in the first column of each row. If the cells in row 1 pull their respective data from the corresponding cells on worksheet "5," and the cells in row 2 pull their data from worksheet "6," I'm having to manually change each cell's formula to reflect the correct worksheet ID. For example: "='21'!$C$5" pulls the data from a specific cell in worksheet "21." On the next row, I have to manually modify all the formulas as follows so they reference the same cells on worksheet "22: "='22'!$C$5" Since "21" & "22" etc. are already identified in the first column of each row, is there a way the formulas could simply reference the correct worksheets based upon the reference in the first column? Something like: "='worksheet Id in 1st column'!$C$5" Thanks, Scott Fraser City of Key West, Florida -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Auto Reference Worksheet Tab ID's in Formulas?
I would usually place the apostrophes but why bother when sheets are numbers
1 through 60. I don't see any spaces in those. I like to teach others my lazy habits<g Gord Dibben MS Excel MVP On Mon, 10 May 2010 16:37:16 -0500, Dave Peterson wrote: But make sure you put the sheet name in apostrophes: =indirect("'" & row() & "'!c5") (the $ don't matter, since it's a string) And I like to use the =row() and adjust that: =indirect("'" & row() + 5 & "'!c5") That +5 has to be changed to match the row of the cell getting the formula and the worksheet from which the value should be retrieved. Gord Dibben wrote: In A1 enter =INDIRECT(ROW() & "!$C$5") Copy down to increment the sheets. Note: you can offset the ROW() to start at sheet 5 if you want. In A1 enter =INDIRECT(ROW(5:5) & "!$C$5") Copy down Gord Dibben MS Excel MVP On Mon, 10 May 2010 13:18:04 -0700, Scott - Key West wrote: In my workbook (2003) used to track disaster costs, I have some 60 different worksheets. On the Summary page, the cells pull data from the individual worksheets. On the Summary page, there are some 60 rows. Each row pulls data from a different worksheet; from the same relative cells, just on different sheets. On the Summary page, the first column is text that identifies which tab (worksheet) that row's data is from. My difficulty is having to manually change the formulas in each cell, in every row, so that it pulls the data from the appropriate worksheet. There's gotta be a way for the formulas within each row, to know which worksheet to pull the data from, based upon the Id contents already in the first column of each row. If the cells in row 1 pull their respective data from the corresponding cells on worksheet "5," and the cells in row 2 pull their data from worksheet "6," I'm having to manually change each cell's formula to reflect the correct worksheet ID. For example: "='21'!$C$5" pulls the data from a specific cell in worksheet "21." On the next row, I have to manually modify all the formulas as follows so they reference the same cells on worksheet "22: "='22'!$C$5" Since "21" & "22" etc. are already identified in the first column of each row, is there a way the formulas could simply reference the correct worksheets based upon the reference in the first column? Something like: "='worksheet Id in 1st column'!$C$5" Thanks, Scott Fraser City of Key West, Florida |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Auto Reference Worksheet Tab ID's in Formulas?
I need those apostrophes when the sheets are named like numbers. (Someone
didn't test <hehehe.) (And if the name looks like an address, too.) Gord Dibben wrote: I would usually place the apostrophes but why bother when sheets are numbers 1 through 60. I don't see any spaces in those. I like to teach others my lazy habits<g Gord Dibben MS Excel MVP On Mon, 10 May 2010 16:37:16 -0500, Dave Peterson wrote: But make sure you put the sheet name in apostrophes: =indirect("'" & row() & "'!c5") (the $ don't matter, since it's a string) And I like to use the =row() and adjust that: =indirect("'" & row() + 5 & "'!c5") That +5 has to be changed to match the row of the cell getting the formula and the worksheet from which the value should be retrieved. Gord Dibben wrote: In A1 enter =INDIRECT(ROW() & "!$C$5") Copy down to increment the sheets. Note: you can offset the ROW() to start at sheet 5 if you want. In A1 enter =INDIRECT(ROW(5:5) & "!$C$5") Copy down Gord Dibben MS Excel MVP On Mon, 10 May 2010 13:18:04 -0700, Scott - Key West wrote: In my workbook (2003) used to track disaster costs, I have some 60 different worksheets. On the Summary page, the cells pull data from the individual worksheets. On the Summary page, there are some 60 rows. Each row pulls data from a different worksheet; from the same relative cells, just on different sheets. On the Summary page, the first column is text that identifies which tab (worksheet) that row's data is from. My difficulty is having to manually change the formulas in each cell, in every row, so that it pulls the data from the appropriate worksheet. There's gotta be a way for the formulas within each row, to know which worksheet to pull the data from, based upon the Id contents already in the first column of each row. If the cells in row 1 pull their respective data from the corresponding cells on worksheet "5," and the cells in row 2 pull their data from worksheet "6," I'm having to manually change each cell's formula to reflect the correct worksheet ID. For example: "='21'!$C$5" pulls the data from a specific cell in worksheet "21." On the next row, I have to manually modify all the formulas as follows so they reference the same cells on worksheet "22: "='22'!$C$5" Since "21" & "22" etc. are already identified in the first column of each row, is there a way the formulas could simply reference the correct worksheets based upon the reference in the first column? Something like: "='worksheet Id in 1st column'!$C$5" Thanks, Scott Fraser City of Key West, Florida -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Auto Reference Worksheet Tab ID's in Formulas?
(Someone didn't test <hehehe.)
I tested my formula =INDIRECT(ROW() & "!$C$5") In a workbook with 30 sheets numbered 1 to 30 Different value in C5 of each sheet. All 30 values were returned. No apostrophes were required. Gord On Mon, 10 May 2010 17:02:07 -0500, Dave Peterson wrote: I need those apostrophes when the sheets are named like numbers. (Someone didn't test <hehehe.) (And if the name looks like an address, too.) Gord Dibben wrote: I would usually place the apostrophes but why bother when sheets are numbers 1 through 60. I don't see any spaces in those. I like to teach others my lazy habits<g Gord Dibben MS Excel MVP On Mon, 10 May 2010 16:37:16 -0500, Dave Peterson wrote: But make sure you put the sheet name in apostrophes: =indirect("'" & row() & "'!c5") (the $ don't matter, since it's a string) And I like to use the =row() and adjust that: =indirect("'" & row() + 5 & "'!c5") That +5 has to be changed to match the row of the cell getting the formula and the worksheet from which the value should be retrieved. Gord Dibben wrote: In A1 enter =INDIRECT(ROW() & "!$C$5") Copy down to increment the sheets. Note: you can offset the ROW() to start at sheet 5 if you want. In A1 enter =INDIRECT(ROW(5:5) & "!$C$5") Copy down Gord Dibben MS Excel MVP On Mon, 10 May 2010 13:18:04 -0700, Scott - Key West wrote: In my workbook (2003) used to track disaster costs, I have some 60 different worksheets. On the Summary page, the cells pull data from the individual worksheets. On the Summary page, there are some 60 rows. Each row pulls data from a different worksheet; from the same relative cells, just on different sheets. On the Summary page, the first column is text that identifies which tab (worksheet) that row's data is from. My difficulty is having to manually change the formulas in each cell, in every row, so that it pulls the data from the appropriate worksheet. There's gotta be a way for the formulas within each row, to know which worksheet to pull the data from, based upon the Id contents already in the first column of each row. If the cells in row 1 pull their respective data from the corresponding cells on worksheet "5," and the cells in row 2 pull their data from worksheet "6," I'm having to manually change each cell's formula to reflect the correct worksheet ID. For example: "='21'!$C$5" pulls the data from a specific cell in worksheet "21." On the next row, I have to manually modify all the formulas as follows so they reference the same cells on worksheet "22: "='22'!$C$5" Since "21" & "22" etc. are already identified in the first column of each row, is there a way the formulas could simply reference the correct worksheets based upon the reference in the first column? Something like: "='worksheet Id in 1st column'!$C$5" Thanks, Scott Fraser City of Key West, Florida |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Auto Reference Worksheet Tab ID's in Formulas?
Hmmm.
When I said someone didn't test, I meant me (as far as you know!). It worked ok for me, too. But if I built the formula Select A1 on the 1 sheet edit|copy select A2 on any other sheet edit|paste special|paste link Then excel will build the formula with the apostrophes. I'll say it again... Hmmmm. Gord Dibben wrote: (Someone didn't test <hehehe.) I tested my formula =INDIRECT(ROW() & "!$C$5") In a workbook with 30 sheets numbered 1 to 30 Different value in C5 of each sheet. All 30 values were returned. No apostrophes were required. Gord On Mon, 10 May 2010 17:02:07 -0500, Dave Peterson wrote: I need those apostrophes when the sheets are named like numbers. (Someone didn't test <hehehe.) (And if the name looks like an address, too.) Gord Dibben wrote: I would usually place the apostrophes but why bother when sheets are numbers 1 through 60. I don't see any spaces in those. I like to teach others my lazy habits<g Gord Dibben MS Excel MVP On Mon, 10 May 2010 16:37:16 -0500, Dave Peterson wrote: But make sure you put the sheet name in apostrophes: =indirect("'" & row() & "'!c5") (the $ don't matter, since it's a string) And I like to use the =row() and adjust that: =indirect("'" & row() + 5 & "'!c5") That +5 has to be changed to match the row of the cell getting the formula and the worksheet from which the value should be retrieved. Gord Dibben wrote: In A1 enter =INDIRECT(ROW() & "!$C$5") Copy down to increment the sheets. Note: you can offset the ROW() to start at sheet 5 if you want. In A1 enter =INDIRECT(ROW(5:5) & "!$C$5") Copy down Gord Dibben MS Excel MVP On Mon, 10 May 2010 13:18:04 -0700, Scott - Key West wrote: In my workbook (2003) used to track disaster costs, I have some 60 different worksheets. On the Summary page, the cells pull data from the individual worksheets. On the Summary page, there are some 60 rows. Each row pulls data from a different worksheet; from the same relative cells, just on different sheets. On the Summary page, the first column is text that identifies which tab (worksheet) that row's data is from. My difficulty is having to manually change the formulas in each cell, in every row, so that it pulls the data from the appropriate worksheet. There's gotta be a way for the formulas within each row, to know which worksheet to pull the data from, based upon the Id contents already in the first column of each row. If the cells in row 1 pull their respective data from the corresponding cells on worksheet "5," and the cells in row 2 pull their data from worksheet "6," I'm having to manually change each cell's formula to reflect the correct worksheet ID. For example: "='21'!$C$5" pulls the data from a specific cell in worksheet "21." On the next row, I have to manually modify all the formulas as follows so they reference the same cells on worksheet "22: "='22'!$C$5" Since "21" & "22" etc. are already identified in the first column of each row, is there a way the formulas could simply reference the correct worksheets based upon the reference in the first column? Something like: "='worksheet Id in 1st column'!$C$5" Thanks, Scott Fraser City of Key West, Florida -- Dave Peterson |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Auto Reference Worksheet Tab ID's in Formulas?
ps.
Sorry for casting aspersions in your direction! Gord Dibben wrote: (Someone didn't test <hehehe.) I tested my formula =INDIRECT(ROW() & "!$C$5") In a workbook with 30 sheets numbered 1 to 30 Different value in C5 of each sheet. All 30 values were returned. No apostrophes were required. Gord On Mon, 10 May 2010 17:02:07 -0500, Dave Peterson wrote: I need those apostrophes when the sheets are named like numbers. (Someone didn't test <hehehe.) (And if the name looks like an address, too.) Gord Dibben wrote: I would usually place the apostrophes but why bother when sheets are numbers 1 through 60. I don't see any spaces in those. I like to teach others my lazy habits<g Gord Dibben MS Excel MVP On Mon, 10 May 2010 16:37:16 -0500, Dave Peterson wrote: But make sure you put the sheet name in apostrophes: =indirect("'" & row() & "'!c5") (the $ don't matter, since it's a string) And I like to use the =row() and adjust that: =indirect("'" & row() + 5 & "'!c5") That +5 has to be changed to match the row of the cell getting the formula and the worksheet from which the value should be retrieved. Gord Dibben wrote: In A1 enter =INDIRECT(ROW() & "!$C$5") Copy down to increment the sheets. Note: you can offset the ROW() to start at sheet 5 if you want. In A1 enter =INDIRECT(ROW(5:5) & "!$C$5") Copy down Gord Dibben MS Excel MVP On Mon, 10 May 2010 13:18:04 -0700, Scott - Key West wrote: In my workbook (2003) used to track disaster costs, I have some 60 different worksheets. On the Summary page, the cells pull data from the individual worksheets. On the Summary page, there are some 60 rows. Each row pulls data from a different worksheet; from the same relative cells, just on different sheets. On the Summary page, the first column is text that identifies which tab (worksheet) that row's data is from. My difficulty is having to manually change the formulas in each cell, in every row, so that it pulls the data from the appropriate worksheet. There's gotta be a way for the formulas within each row, to know which worksheet to pull the data from, based upon the Id contents already in the first column of each row. If the cells in row 1 pull their respective data from the corresponding cells on worksheet "5," and the cells in row 2 pull their data from worksheet "6," I'm having to manually change each cell's formula to reflect the correct worksheet ID. For example: "='21'!$C$5" pulls the data from a specific cell in worksheet "21." On the next row, I have to manually modify all the formulas as follows so they reference the same cells on worksheet "22: "='22'!$C$5" Since "21" & "22" etc. are already identified in the first column of each row, is there a way the formulas could simply reference the correct worksheets based upon the reference in the first column? Something like: "='worksheet Id in 1st column'!$C$5" Thanks, Scott Fraser City of Key West, Florida -- Dave Peterson |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Auto Reference Worksheet Tab ID's in Formulas?
Not sure why Excel adds the apsotrophes when you paste link or just use =
sign and click when sheet names are numbers only. Does not add the apostrophes to a sheetname without spaces. Hmmmmmm. I'll treat it as just one of those things Excel does. Gord On Mon, 10 May 2010 18:15:19 -0500, Dave Peterson wrote: Hmmm. When I said someone didn't test, I meant me (as far as you know!). It worked ok for me, too. But if I built the formula Select A1 on the 1 sheet edit|copy select A2 on any other sheet edit|paste special|paste link Then excel will build the formula with the apostrophes. I'll say it again... Hmmmm. Gord Dibben wrote: (Someone didn't test <hehehe.) I tested my formula =INDIRECT(ROW() & "!$C$5") In a workbook with 30 sheets numbered 1 to 30 Different value in C5 of each sheet. All 30 values were returned. No apostrophes were required. Gord On Mon, 10 May 2010 17:02:07 -0500, Dave Peterson wrote: I need those apostrophes when the sheets are named like numbers. (Someone didn't test <hehehe.) (And if the name looks like an address, too.) Gord Dibben wrote: I would usually place the apostrophes but why bother when sheets are numbers 1 through 60. I don't see any spaces in those. I like to teach others my lazy habits<g Gord Dibben MS Excel MVP On Mon, 10 May 2010 16:37:16 -0500, Dave Peterson wrote: But make sure you put the sheet name in apostrophes: =indirect("'" & row() & "'!c5") (the $ don't matter, since it's a string) And I like to use the =row() and adjust that: =indirect("'" & row() + 5 & "'!c5") That +5 has to be changed to match the row of the cell getting the formula and the worksheet from which the value should be retrieved. Gord Dibben wrote: In A1 enter =INDIRECT(ROW() & "!$C$5") Copy down to increment the sheets. Note: you can offset the ROW() to start at sheet 5 if you want. In A1 enter =INDIRECT(ROW(5:5) & "!$C$5") Copy down Gord Dibben MS Excel MVP On Mon, 10 May 2010 13:18:04 -0700, Scott - Key West wrote: In my workbook (2003) used to track disaster costs, I have some 60 different worksheets. On the Summary page, the cells pull data from the individual worksheets. On the Summary page, there are some 60 rows. Each row pulls data from a different worksheet; from the same relative cells, just on different sheets. On the Summary page, the first column is text that identifies which tab (worksheet) that row's data is from. My difficulty is having to manually change the formulas in each cell, in every row, so that it pulls the data from the appropriate worksheet. There's gotta be a way for the formulas within each row, to know which worksheet to pull the data from, based upon the Id contents already in the first column of each row. If the cells in row 1 pull their respective data from the corresponding cells on worksheet "5," and the cells in row 2 pull their data from worksheet "6," I'm having to manually change each cell's formula to reflect the correct worksheet ID. For example: "='21'!$C$5" pulls the data from a specific cell in worksheet "21." On the next row, I have to manually modify all the formulas as follows so they reference the same cells on worksheet "22: "='22'!$C$5" Since "21" & "22" etc. are already identified in the first column of each row, is there a way the formulas could simply reference the correct worksheets based upon the reference in the first column? Something like: "='worksheet Id in 1st column'!$C$5" Thanks, Scott Fraser City of Key West, Florida |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 formulas reference same worksheet, one an array | Excel Worksheet Functions | |||
List of Id's in one Colum list of names in another. More Id's on s | Excel Discussion (Misc queries) | |||
Formulas that reference cells that reference another cell | Excel Discussion (Misc queries) | |||
Relative worksheet reference in 3-D formulas? | Excel Worksheet Functions | |||
Face ID's | Excel Discussion (Misc queries) |