Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Relative worksheet reference for multiple worksheets in a workbook
I have a workbook with several worksheets. I am creating a summary worksheet
page within the workbook. Here is what I have: =+'Sheet1 (3)'!$H$52 I need to be able to copy that for several rows on the summary worksheet page AND have it automatically change the sheet reference to the next sheet. Example: =+'Sheet1 (3)'!$H$52 =+'Sheet1 (4)'!$H$52 =+'Sheet1 (5)'!$H$52 When I copy and paste it keeps the sheet reference the exact same and doesn't change the sheet reference to the next # automatically. Please HELP if you know how. I have done about 2 hours of research and can't figure it out :( |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Relative worksheet reference for multiple worksheets in a workbook
I'd try:
=indirect("'Sheet1 (" & row() - ## & ")'!h52") The ## depends on the row that's getting the formula. So if you the first formula goes into row 2 and I want to retrieve the value from "sheet1 (1)", then the ## would be 1 (so it's the row (2) - 1 = 1). Debbie wrote: I have a workbook with several worksheets. I am creating a summary worksheet page within the workbook. Here is what I have: =+'Sheet1 (3)'!$H$52 I need to be able to copy that for several rows on the summary worksheet page AND have it automatically change the sheet reference to the next sheet. Example: =+'Sheet1 (3)'!$H$52 =+'Sheet1 (4)'!$H$52 =+'Sheet1 (5)'!$H$52 When I copy and paste it keeps the sheet reference the exact same and doesn't change the sheet reference to the next # automatically. Please HELP if you know how. I have done about 2 hours of research and can't figure it out :( -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Relative worksheet reference for multiple worksheets in a work
Dave,
OK I think I may have presented it incorrectly....I need it to reference the next SHEET not the next row. See below for my corrected problem. Hopefully this will make sense :) Any help would be greatly appreciated. I am creating a summary worksheet page within a multiple worksheet workbook. Here is what I have: =+'Sheet1'!$H$52 I need to be able to copy that for several rows on the summary worksheet page AND have it automatically change the sheet reference to the next sheet. Example of what I need when I copy and paste: =+'Sheet1'!$H$52 =+'Sheet2'!$H$52 =+'Sheet3'!$H$52 So the sheet reference is relative but the cell reference is absolute. "Dave Peterson" wrote: I'd try: =indirect("'Sheet1 (" & row() - ## & ")'!h52") The ## depends on the row that's getting the formula. So if you the first formula goes into row 2 and I want to retrieve the value from "sheet1 (1)", then the ## would be 1 (so it's the row (2) - 1 = 1). Debbie wrote: I have a workbook with several worksheets. I am creating a summary worksheet page within the workbook. Here is what I have: =+'Sheet1 (3)'!$H$52 I need to be able to copy that for several rows on the summary worksheet page AND have it automatically change the sheet reference to the next sheet. Example: =+'Sheet1 (3)'!$H$52 =+'Sheet1 (4)'!$H$52 =+'Sheet1 (5)'!$H$52 When I copy and paste it keeps the sheet reference the exact same and doesn't change the sheet reference to the next # automatically. Please HELP if you know how. I have done about 2 hours of research and can't figure it out :( -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Relative worksheet reference for multiple worksheets in a work
I posted much the same answer that Dave has here in your other posting of
same question in GQ. Best to only ask in one forum so that all answers can be consolidated. Or maybe the system split this response into two different forums - who knows. I believe that the response that Dave has given you is the correct one for what you want. But I think Dave may have messed up his formula by adding parenthesis in it that aren't needed. Try =INDIRECT("'Sheet" & Row()-## & "'!H52") same explanation of ROW()-## as he gave: it should be a number that when subtracted from the current row gives you a 1 for first formula entry. Then it will increment as you copy the formula down the sheet. So if first formula went: Into row 1 and you want reference to Sheet1 it is simply: =INDIRECT("'Sheet" & Row() & "'!H52") Into row 2 and you want reference to Sheet1 for first formula, it would be: =INDIRECT("'Sheet" & Row()-1 & "'!H52") Into row 5 and you want a reference to Sheet1 for first formula, then it would be: =INDIRECT("'Sheet" & Row()-4 & "'!H52") "Debbie" wrote: Dave, OK I think I may have presented it incorrectly....I need it to reference the next SHEET not the next row. See below for my corrected problem. Hopefully this will make sense :) Any help would be greatly appreciated. I am creating a summary worksheet page within a multiple worksheet workbook. Here is what I have: =+'Sheet1'!$H$52 I need to be able to copy that for several rows on the summary worksheet page AND have it automatically change the sheet reference to the next sheet. Example of what I need when I copy and paste: =+'Sheet1'!$H$52 =+'Sheet2'!$H$52 =+'Sheet3'!$H$52 So the sheet reference is relative but the cell reference is absolute. "Dave Peterson" wrote: I'd try: =indirect("'Sheet1 (" & row() - ## & ")'!h52") The ## depends on the row that's getting the formula. So if you the first formula goes into row 2 and I want to retrieve the value from "sheet1 (1)", then the ## would be 1 (so it's the row (2) - 1 = 1). Debbie wrote: I have a workbook with several worksheets. I am creating a summary worksheet page within the workbook. Here is what I have: =+'Sheet1 (3)'!$H$52 I need to be able to copy that for several rows on the summary worksheet page AND have it automatically change the sheet reference to the next sheet. Example: =+'Sheet1 (3)'!$H$52 =+'Sheet1 (4)'!$H$52 =+'Sheet1 (5)'!$H$52 When I copy and paste it keeps the sheet reference the exact same and doesn't change the sheet reference to the next # automatically. Please HELP if you know how. I have done about 2 hours of research and can't figure it out :( -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Relative worksheet reference for multiple worksheets in a work
My apologies to Dave - actually his formula here is correct for the sample
data you provided, which is different than what I saw in your other post. The parenthesis would be needed to form the proper name of the duplicated sheets. Just a little more confusion from cross-posting in multiple forums. "JLatham" wrote: I posted much the same answer that Dave has here in your other posting of same question in GQ. Best to only ask in one forum so that all answers can be consolidated. Or maybe the system split this response into two different forums - who knows. I believe that the response that Dave has given you is the correct one for what you want. But I think Dave may have messed up his formula by adding parenthesis in it that aren't needed. Try =INDIRECT("'Sheet" & Row()-## & "'!H52") same explanation of ROW()-## as he gave: it should be a number that when subtracted from the current row gives you a 1 for first formula entry. Then it will increment as you copy the formula down the sheet. So if first formula went: Into row 1 and you want reference to Sheet1 it is simply: =INDIRECT("'Sheet" & Row() & "'!H52") Into row 2 and you want reference to Sheet1 for first formula, it would be: =INDIRECT("'Sheet" & Row()-1 & "'!H52") Into row 5 and you want a reference to Sheet1 for first formula, then it would be: =INDIRECT("'Sheet" & Row()-4 & "'!H52") "Debbie" wrote: Dave, OK I think I may have presented it incorrectly....I need it to reference the next SHEET not the next row. See below for my corrected problem. Hopefully this will make sense :) Any help would be greatly appreciated. I am creating a summary worksheet page within a multiple worksheet workbook. Here is what I have: =+'Sheet1'!$H$52 I need to be able to copy that for several rows on the summary worksheet page AND have it automatically change the sheet reference to the next sheet. Example of what I need when I copy and paste: =+'Sheet1'!$H$52 =+'Sheet2'!$H$52 =+'Sheet3'!$H$52 So the sheet reference is relative but the cell reference is absolute. "Dave Peterson" wrote: I'd try: =indirect("'Sheet1 (" & row() - ## & ")'!h52") The ## depends on the row that's getting the formula. So if you the first formula goes into row 2 and I want to retrieve the value from "sheet1 (1)", then the ## would be 1 (so it's the row (2) - 1 = 1). Debbie wrote: I have a workbook with several worksheets. I am creating a summary worksheet page within the workbook. Here is what I have: =+'Sheet1 (3)'!$H$52 I need to be able to copy that for several rows on the summary worksheet page AND have it automatically change the sheet reference to the next sheet. Example: =+'Sheet1 (3)'!$H$52 =+'Sheet1 (4)'!$H$52 =+'Sheet1 (5)'!$H$52 When I copy and paste it keeps the sheet reference the exact same and doesn't change the sheet reference to the next # automatically. Please HELP if you know how. I have done about 2 hours of research and can't figure it out :( -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Relative worksheet reference for multiple worksheets in a work
I was going to respond to the OP, but I'm not sure what Debbie's worksheet names
really are. JLatham wrote: My apologies to Dave - actually his formula here is correct for the sample data you provided, which is different than what I saw in your other post. The parenthesis would be needed to form the proper name of the duplicated sheets. Just a little more confusion from cross-posting in multiple forums. "JLatham" wrote: I posted much the same answer that Dave has here in your other posting of same question in GQ. Best to only ask in one forum so that all answers can be consolidated. Or maybe the system split this response into two different forums - who knows. I believe that the response that Dave has given you is the correct one for what you want. But I think Dave may have messed up his formula by adding parenthesis in it that aren't needed. Try =INDIRECT("'Sheet" & Row()-## & "'!H52") same explanation of ROW()-## as he gave: it should be a number that when subtracted from the current row gives you a 1 for first formula entry. Then it will increment as you copy the formula down the sheet. So if first formula went: Into row 1 and you want reference to Sheet1 it is simply: =INDIRECT("'Sheet" & Row() & "'!H52") Into row 2 and you want reference to Sheet1 for first formula, it would be: =INDIRECT("'Sheet" & Row()-1 & "'!H52") Into row 5 and you want a reference to Sheet1 for first formula, then it would be: =INDIRECT("'Sheet" & Row()-4 & "'!H52") "Debbie" wrote: Dave, OK I think I may have presented it incorrectly....I need it to reference the next SHEET not the next row. See below for my corrected problem. Hopefully this will make sense :) Any help would be greatly appreciated. I am creating a summary worksheet page within a multiple worksheet workbook. Here is what I have: =+'Sheet1'!$H$52 I need to be able to copy that for several rows on the summary worksheet page AND have it automatically change the sheet reference to the next sheet. Example of what I need when I copy and paste: =+'Sheet1'!$H$52 =+'Sheet2'!$H$52 =+'Sheet3'!$H$52 So the sheet reference is relative but the cell reference is absolute. "Dave Peterson" wrote: I'd try: =indirect("'Sheet1 (" & row() - ## & ")'!h52") The ## depends on the row that's getting the formula. So if you the first formula goes into row 2 and I want to retrieve the value from "sheet1 (1)", then the ## would be 1 (so it's the row (2) - 1 = 1). Debbie wrote: I have a workbook with several worksheets. I am creating a summary worksheet page within the workbook. Here is what I have: =+'Sheet1 (3)'!$H$52 I need to be able to copy that for several rows on the summary worksheet page AND have it automatically change the sheet reference to the next sheet. Example: =+'Sheet1 (3)'!$H$52 =+'Sheet1 (4)'!$H$52 =+'Sheet1 (5)'!$H$52 When I copy and paste it keeps the sheet reference the exact same and doesn't change the sheet reference to the next # automatically. Please HELP if you know how. I have done about 2 hours of research and can't figure it out :( -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying relative reference across worksheets | Excel Discussion (Misc queries) | |||
Linked value to cell in different workbook + Relative File Reference | Excel Discussion (Misc queries) | |||
relative reference when creating a link to another workbook | Excel Worksheet Functions | |||
How to use address from relative reference across worksheets? | Excel Worksheet Functions | |||
Reference External Worksheets & Protect Worksheet / Workbook | Excel Worksheet Functions |