ExcelBanter - Fill down with references to worksheets

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Fill down with references to worksheets (https://www.excelbanter.com/excel-worksheet-functions/194382-fill-down-references-worksheets.html)

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

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

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