Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fill down with references to worksheets
There are a number of posts on this subject, but I couldn't find
exactly what I was looking for! I have a workbook with a number of invoices, all the same format, one on each worksheet. The worksheets have the same number as the invoice and are numbered sequentially (35, 36, 37 etc). I have a summary sheet that has one line per invoice and shows invoice number, date, total etc. Can I use Fill Down to copy formulae such as ='23'!H$2 so they increment from row to row to ='24'!H$2, ='25'!H$2 etc? If so, how do I go about it? Thanks in advance! Chris |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fill down with references to worksheets
You can use this copied down
=INDIRECT("'"&ROWS($A$1:A23)&"'!H2") no need for absolute references to H2 in this case since INDIRECT makes it static. You can use any row reference $B$1:B23 etc to get the 23 here it is important to use absolute reference on the first cell -- Regards, Peo Sjoblom wrote in message ... There are a number of posts on this subject, but I couldn't find exactly what I was looking for! I have a workbook with a number of invoices, all the same format, one on each worksheet. The worksheets have the same number as the invoice and are numbered sequentially (35, 36, 37 etc). I have a summary sheet that has one line per invoice and shows invoice number, date, total etc. Can I use Fill Down to copy formulae such as ='23'!H$2 so they increment from row to row to ='24'!H$2, ='25'!H$2 etc? If so, how do I go about it? Thanks in advance! Chris |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fill down with references to worksheets
=INDIRECT(ROW()&"!H2")
Gord Dibben MS Excel MVP On Thu, 10 Jul 2008 07:04:27 -0700 (PDT), wrote: There are a number of posts on this subject, but I couldn't find exactly what I was looking for! I have a workbook with a number of invoices, all the same format, one on each worksheet. The worksheets have the same number as the invoice and are numbered sequentially (35, 36, 37 etc). I have a summary sheet that has one line per invoice and shows invoice number, date, total etc. Can I use Fill Down to copy formulae such as ='23'!H$2 so they increment from row to row to ='24'!H$2, ='25'!H$2 etc? If so, how do I go about it? Thanks in advance! Chris |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fill down with references to worksheets
I don't know how you would do that but I suppose it's possible.
If you are doing that because you really *do need to make a list of all the invoices* then you will have to wait till someone smarter that I arrives and gives you the answer .... BUT if you are making the list just to be able to insert totals at the bottom of the list why don't you do a "transversal total" ? The Amazingly Simple Transversal Total -------------------------------------------- Say you have an amount to sum in cell B3 of all your invoices. Grab an empty invoice sheet and rename it Summary sheet. Place it on the extreme left of all your sheets (like it was sheet 1). If you have a number in B3 in the invoices from Invoice 30 to Invoice 50 that you need to sum, in cell B3 of the Summary type this: =SUM(Invoice30!B3;Invoice50!B3) (Use a comma instead of a semicolon depending in which country you're in) That will give you the total if whatever is in B3 in every invoice sheet BETWEEN 30 and 50. You can now copy and paste that formula to wherever you need a "transversal total" in your summary sheet. Another cool thing is that you can then keep a Blank Invoice sheet on the extreme left of the Summary sheet. You can then copy this Blank new invoice (Ctrl + Mouse slide Copy ) in between Invoice 30 and 50 When you key in the data to this new sheet the Summary sheet will automatically sum the new data. Why ???? Just because the new sheet is "physically" in between Invoice 30 and Invoice 50 . I found that really cool. This way your Summary sheet will have the same aspect as the invoice and automatically sum any new sheets that you place between the 30 and the 50. I hope this makes sense because it's a neat little technique and you don't have to keep modifying your formula in the Summary. But if you really do need a list ... BCNU Zumble a écrit dans le message de news: ... There are a number of posts on this subject, but I couldn't find exactly what I was looking for! I have a workbook with a number of invoices, all the same format, one on each worksheet. The worksheets have the same number as the invoice and are numbered sequentially (35, 36, 37 etc). I have a summary sheet that has one line per invoice and shows invoice number, date, total etc. Can I use Fill Down to copy formulae such as ='23'!H$2 so they increment from row to row to ='24'!H$2, ='25'!H$2 etc? If so, how do I go about it? Thanks in advance! Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fill Data with formulas with Absolute References | Excel Discussion (Misc queries) | |||
References between worksheets using IF | Excel Worksheet Functions | |||
References to worksheets in formulae | Excel Discussion (Misc queries) | |||
Auto Fill with some references locked | Excel Discussion (Misc queries) | |||
Lock or Unlock cell references in a formula for auto fill purposes | Excel Discussion (Misc queries) |