| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function across multiple worksheet
Hi, I would like to have data returned to sheet one from a cell in subsequent
worksheets by comparing a cell in sheet one to the name of the worksheets (they match exactly). This will validate that the data has come from the correct worksheet to the corresponding cell in sheet one. The list in sheet one is extensive and hence extensive amount of worksheets, so being able to copy the formula down the column in sheet one will save me time. EG listing in sheet one is Black, Brown, Green and there are sheets called Black, Brown and Green (listing and worksheet names are in the same order). I want to compare the list in sheet one to the name of the sheets and returna value from a cell in those sheets to a cell in sheet one. Can anyone help? -- Thanks Phil |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function across multiple worksheet
Assume that you're looking to return the *same* cell from all your sheets,
say D1. Assume your sheet names are in ColumnA, from A1 down. Try this in B1, and copy down as needed: =INDIRECT(A1&"!D1") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Phil" wrote in message ... Hi, I would like to have data returned to sheet one from a cell in subsequent worksheets by comparing a cell in sheet one to the name of the worksheets (they match exactly). This will validate that the data has come from the correct worksheet to the corresponding cell in sheet one. The list in sheet one is extensive and hence extensive amount of worksheets, so being able to copy the formula down the column in sheet one will save me time. EG listing in sheet one is Black, Brown, Green and there are sheets called Black, Brown and Green (listing and worksheet names are in the same order). I want to compare the list in sheet one to the name of the sheets and returna value from a cell in those sheets to a cell in sheet one. Can anyone help? -- Thanks Phil |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function across multiple worksheet
You could try
=INDIRECT(A11&"!A1") which would return cell A1 from whatever sheet name appears in cell A11. The down side is if your target cell (A1 on the "Green" worksheet, for example) moves (cut/paste or additional lines are inserted/deleted on the "Green" worksheet), the indirect formula does not change to reference the new cell location - it will always refer to cell A1. Also, I believe it is a volatile function - which can adversely affect recalculation speed if you have a lot of them. Or, you could use a macro to build the links. Say Sheet1 A11:A50 contains the worksheet names and you want B11:B50 linked to cell A1 of the respective worksheets identified in A11:A50, Sub Test() Dim rngCell As Range For Each rngCell In Sheets("Sheet1").Range("B11:B50") rngCell.Formula = "=" & rngCell(1, 0).Value & "!$A$1" Next rngCell End Sub "Phil" wrote: Hi, I would like to have data returned to sheet one from a cell in subsequent worksheets by comparing a cell in sheet one to the name of the worksheets (they match exactly). This will validate that the data has come from the correct worksheet to the corresponding cell in sheet one. The list in sheet one is extensive and hence extensive amount of worksheets, so being able to copy the formula down the column in sheet one will save me time. EG listing in sheet one is Black, Brown, Green and there are sheets called Black, Brown and Green (listing and worksheet names are in the same order). I want to compare the list in sheet one to the name of the sheets and returna value from a cell in those sheets to a cell in sheet one. Can anyone help? -- Thanks Phil |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function across multiple worksheet
JMB have tried your suggestion, however the sheet names (and the list in
sheet one) have spaces and commas EG 'Green, Fred Peter' and it appears not to like either. When I changed the sheet names and list to simple names like 'fred' you suggestion works great. Do you know if you macro suggestion will overcome this issue? -- Thanks Phil "JMB" wrote: You could try =INDIRECT(A11&"!A1") which would return cell A1 from whatever sheet name appears in cell A11. The down side is if your target cell (A1 on the "Green" worksheet, for example) moves (cut/paste or additional lines are inserted/deleted on the "Green" worksheet), the indirect formula does not change to reference the new cell location - it will always refer to cell A1. Also, I believe it is a volatile function - which can adversely affect recalculation speed if you have a lot of them. Or, you could use a macro to build the links. Say Sheet1 A11:A50 contains the worksheet names and you want B11:B50 linked to cell A1 of the respective worksheets identified in A11:A50, Sub Test() Dim rngCell As Range For Each rngCell In Sheets("Sheet1").Range("B11:B50") rngCell.Formula = "=" & rngCell(1, 0).Value & "!$A$1" Next rngCell End Sub "Phil" wrote: Hi, I would like to have data returned to sheet one from a cell in subsequent worksheets by comparing a cell in sheet one to the name of the worksheets (they match exactly). This will validate that the data has come from the correct worksheet to the corresponding cell in sheet one. The list in sheet one is extensive and hence extensive amount of worksheets, so being able to copy the formula down the column in sheet one will save me time. EG listing in sheet one is Black, Brown, Green and there are sheets called Black, Brown and Green (listing and worksheet names are in the same order). I want to compare the list in sheet one to the name of the sheets and returna value from a cell in those sheets to a cell in sheet one. Can anyone help? -- Thanks Phil |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function across multiple worksheet
Ragdyer
Thanks, your assumptions and suggestions worked fine, however my sheets (and the list in sheet one) have names with spaces and commas eg 'Green, Fred Peter'. These spaces and commas seem to cause a problem and then the INDIRECT function does not work. Any thoughts? -- Thanks Phil "Ragdyer" wrote: Assume that you're looking to return the *same* cell from all your sheets, say D1. Assume your sheet names are in ColumnA, from A1 down. Try this in B1, and copy down as needed: =INDIRECT(A1&"!D1") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Phil" wrote in message ... Hi, I would like to have data returned to sheet one from a cell in subsequent worksheets by comparing a cell in sheet one to the name of the worksheets (they match exactly). This will validate that the data has come from the correct worksheet to the corresponding cell in sheet one. The list in sheet one is extensive and hence extensive amount of worksheets, so being able to copy the formula down the column in sheet one will save me time. EG listing in sheet one is Black, Brown, Green and there are sheets called Black, Brown and Green (listing and worksheet names are in the same order). I want to compare the list in sheet one to the name of the sheets and returna value from a cell in those sheets to a cell in sheet one. Can anyone help? -- Thanks Phil |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function across multiple worksheet
Green, Fred Peter
That's the actual sheet name? Try it like this: =INDIRECT("'"&A1&"'!D1") Biff "Phil" wrote in message ... Ragdyer Thanks, your assumptions and suggestions worked fine, however my sheets (and the list in sheet one) have names with spaces and commas eg 'Green, Fred Peter'. These spaces and commas seem to cause a problem and then the INDIRECT function does not work. Any thoughts? -- Thanks Phil "Ragdyer" wrote: Assume that you're looking to return the *same* cell from all your sheets, say D1. Assume your sheet names are in ColumnA, from A1 down. Try this in B1, and copy down as needed: =INDIRECT(A1&"!D1") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Phil" wrote in message ... Hi, I would like to have data returned to sheet one from a cell in subsequent worksheets by comparing a cell in sheet one to the name of the worksheets (they match exactly). This will validate that the data has come from the correct worksheet to the corresponding cell in sheet one. The list in sheet one is extensive and hence extensive amount of worksheets, so being able to copy the formula down the column in sheet one will save me time. EG listing in sheet one is Black, Brown, Green and there are sheets called Black, Brown and Green (listing and worksheet names are in the same order). I want to compare the list in sheet one to the name of the sheets and returna value from a cell in those sheets to a cell in sheet one. Can anyone help? -- Thanks Phil |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function across multiple worksheet
Biff...you legend...worked a treat.
Thanks -- Thanks Phil "Biff" wrote: Green, Fred Peter That's the actual sheet name? Try it like this: =INDIRECT("'"&A1&"'!D1") Biff "Phil" wrote in message ... Ragdyer Thanks, your assumptions and suggestions worked fine, however my sheets (and the list in sheet one) have names with spaces and commas eg 'Green, Fred Peter'. These spaces and commas seem to cause a problem and then the INDIRECT function does not work. Any thoughts? -- Thanks Phil "Ragdyer" wrote: Assume that you're looking to return the *same* cell from all your sheets, say D1. Assume your sheet names are in ColumnA, from A1 down. Try this in B1, and copy down as needed: =INDIRECT(A1&"!D1") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Phil" wrote in message ... Hi, I would like to have data returned to sheet one from a cell in subsequent worksheets by comparing a cell in sheet one to the name of the worksheets (they match exactly). This will validate that the data has come from the correct worksheet to the corresponding cell in sheet one. The list in sheet one is extensive and hence extensive amount of worksheets, so being able to copy the formula down the column in sheet one will save me time. EG listing in sheet one is Black, Brown, Green and there are sheets called Black, Brown and Green (listing and worksheet names are in the same order). I want to compare the list in sheet one to the name of the sheets and returna value from a cell in those sheets to a cell in sheet one. Can anyone help? -- Thanks Phil |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function across multiple worksheet
You're welcome. Thanks for the feedback!
Biff "Phil" wrote in message ... Biff...you legend...worked a treat. Thanks -- Thanks Phil "Biff" wrote: Green, Fred Peter That's the actual sheet name? Try it like this: =INDIRECT("'"&A1&"'!D1") Biff "Phil" wrote in message ... Ragdyer Thanks, your assumptions and suggestions worked fine, however my sheets (and the list in sheet one) have names with spaces and commas eg 'Green, Fred Peter'. These spaces and commas seem to cause a problem and then the INDIRECT function does not work. Any thoughts? -- Thanks Phil "Ragdyer" wrote: Assume that you're looking to return the *same* cell from all your sheets, say D1. Assume your sheet names are in ColumnA, from A1 down. Try this in B1, and copy down as needed: =INDIRECT(A1&"!D1") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Phil" wrote in message ... Hi, I would like to have data returned to sheet one from a cell in subsequent worksheets by comparing a cell in sheet one to the name of the worksheets (they match exactly). This will validate that the data has come from the correct worksheet to the corresponding cell in sheet one. The list in sheet one is extensive and hence extensive amount of worksheets, so being able to copy the formula down the column in sheet one will save me time. EG listing in sheet one is Black, Brown, Green and there are sheets called Black, Brown and Green (listing and worksheet names are in the same order). I want to compare the list in sheet one to the name of the sheets and returna value from a cell in those sheets to a cell in sheet one. Can anyone help? -- Thanks Phil |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
| macro or worksheet function? | Excel Worksheet Functions | |||
| Worksheet VBA function - not recognized | Excel Worksheet Functions | |||
| how do i maintain one worksheet from data from multiple worksheet | Excel Worksheet Functions | |||
| Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Linear Mode
