Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Reference
1st - let me describe my workbook: My first worksheet contains a table that
is a summary of the other worksheets. Let's call it "Summary" worksheet. The other worksheets contains monthly manhours worked during the past 12 months. Every worksheet contains the manhours of an office in a different city. In the Summary table i have the reference to the other worksheets as E.g. =SUM(Belfast!B20) where Belfast is the worksheet name. This works fine, but at the end of the month I have to take out the top line and add one line at the bottom for the new month, thus every record move one level up. I.o.w. March is now at the bottom (in B20) and after monthsend March would be in B19. my problem is after deleting and inserting rows my formula looks like this "=SUM(Belfast!B19)" and i have to go and change it back to B20 by hand and I'm sure there must be a better way of accomplishing this. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Reference
Try to use absolute row reference like this:
=SUM(Belfast!B$19) Regards, Stefi €˛Herman_KLF€¯ ezt Ć*rta: 1st - let me describe my workbook: My first worksheet contains a table that is a summary of the other worksheets. Let's call it "Summary" worksheet. The other worksheets contains monthly manhours worked during the past 12 months. Every worksheet contains the manhours of an office in a different city. In the Summary table i have the reference to the other worksheets as E.g. =SUM(Belfast!B20) where Belfast is the worksheet name. This works fine, but at the end of the month I have to take out the top line and add one line at the bottom for the new month, thus every record move one level up. I.o.w. March is now at the bottom (in B20) and after monthsend March would be in B19. my problem is after deleting and inserting rows my formula looks like this "=SUM(Belfast!B19)" and i have to go and change it back to B20 by hand and I'm sure there must be a better way of accomplishing this. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Reference
Tried that Stefi, didn't work.
Sorry, i should have said that, but that is as far as i got. "Stefi" wrote: Try to use absolute row reference like this: =SUM(Belfast!B$19) Regards, Stefi €˛Herman_KLF€¯ ezt Ć*rta: 1st - let me describe my workbook: My first worksheet contains a table that is a summary of the other worksheets. Let's call it "Summary" worksheet. The other worksheets contains monthly manhours worked during the past 12 months. Every worksheet contains the manhours of an office in a different city. In the Summary table i have the reference to the other worksheets as E.g. =SUM(Belfast!B20) where Belfast is the worksheet name. This works fine, but at the end of the month I have to take out the top line and add one line at the bottom for the new month, thus every record move one level up. I.o.w. March is now at the bottom (in B20) and after monthsend March would be in B19. my problem is after deleting and inserting rows my formula looks like this "=SUM(Belfast!B19)" and i have to go and change it back to B20 by hand and I'm sure there must be a better way of accomplishing this. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Reference
Try
=INDIRECT("Belfast!B"&20) Using Sum for a single cell has no reason! Stefi €˛Herman_KLF€¯ ezt Ć*rta: Tried that Stefi, didn't work. Sorry, i should have said that, but that is as far as i got. "Stefi" wrote: Try to use absolute row reference like this: =SUM(Belfast!B$19) Regards, Stefi €˛Herman_KLF€¯ ezt Ć*rta: 1st - let me describe my workbook: My first worksheet contains a table that is a summary of the other worksheets. Let's call it "Summary" worksheet. The other worksheets contains monthly manhours worked during the past 12 months. Every worksheet contains the manhours of an office in a different city. In the Summary table i have the reference to the other worksheets as E.g. =SUM(Belfast!B20) where Belfast is the worksheet name. This works fine, but at the end of the month I have to take out the top line and add one line at the bottom for the new month, thus every record move one level up. I.o.w. March is now at the bottom (in B20) and after monthsend March would be in B19. my problem is after deleting and inserting rows my formula looks like this "=SUM(Belfast!B19)" and i have to go and change it back to B20 by hand and I'm sure there must be a better way of accomplishing this. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Reference
=INDIRECT("Belfast!B20")
If you want to be able to copy this cell (within the same sheet) and have it adjust row & column, could change to: =INDIRECT("Belfast!"&COLUMN(B1)&ROW(A20),FALSE) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Herman_KLF" wrote: Tried that Stefi, didn't work. Sorry, i should have said that, but that is as far as i got. "Stefi" wrote: Try to use absolute row reference like this: =SUM(Belfast!B$19) Regards, Stefi €˛Herman_KLF€¯ ezt Ć*rta: 1st - let me describe my workbook: My first worksheet contains a table that is a summary of the other worksheets. Let's call it "Summary" worksheet. The other worksheets contains monthly manhours worked during the past 12 months. Every worksheet contains the manhours of an office in a different city. In the Summary table i have the reference to the other worksheets as E.g. =SUM(Belfast!B20) where Belfast is the worksheet name. This works fine, but at the end of the month I have to take out the top line and add one line at the bottom for the new month, thus every record move one level up. I.o.w. March is now at the bottom (in B20) and after monthsend March would be in B19. my problem is after deleting and inserting rows my formula looks like this "=SUM(Belfast!B19)" and i have to go and change it back to B20 by hand and I'm sure there must be a better way of accomplishing this. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting a sheet name reference within a cell reference??? | Excel Discussion (Misc queries) | |||
Formulas that reference cells that reference another cell | Excel Discussion (Misc queries) | |||
absolute cell reference A spreadsheet cell reference that does no | Excel Discussion (Misc queries) | |||
problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. | Excel Worksheet Functions | |||
Problem with =sum(offset(cell reference,w,x,y,z). I want cell reference to be variable | Excel Worksheet Functions |