how can I imbed sheet names contained in cells into formulas?

I have a spreadsheet which contains a column with account numbers:

ACCT #
1 =SUMIF(SheetReference1,Cell,SheetReference1)
2 =SUMIF(SheetReference2,Cell,SheetReference2)
3 =SUMIF(SheetReference3,Cell,SheetReference3)
4 =SUMIF(SheetReference4,Cell,SheetReference4)



These Account numbers are also the name of sheets.
I need to use the SUMIF function to extract information from each sheet.
However, when I copy paste the formula down I have to manually go into each
cell and change the sheet reference. This can be quite annoying because the
SUMIF function contains the sheet reference twice. If I could imbed the
Account Number as a sheet reference I could save myself a lot of typing....

Does anyone have any suggestions or workarounds?

--
Ted

Use the INDIRECT() function. It takes as an argument a string value for a
cell reference. For your example, if your account numbers were in column A,
you could use something like:

=SUMIF(INDIRECT("SheetReference"&A1),Cell)

--
Regards,
Dave


"Ted" wrote:

> I have a spreadsheet which contains a column with account numbers:
>
> ACCT #
> 1 =SUMIF(SheetReference1,Cell,SheetReference1)
> 2 =SUMIF(SheetReference2,Cell,SheetReference2)
> 3 =SUMIF(SheetReference3,Cell,SheetReference3)
> 4 =SUMIF(SheetReference4,Cell,SheetReference4)
>
>
>
> These Account numbers are also the name of sheets.
> I need to use the SUMIF function to extract information from each sheet.
> However, when I copy paste the formula down I have to manually go into each
> cell and change the sheet reference. This can be quite annoying because the
> SUMIF function contains the sheet reference twice. If I could imbed the
> Account Number as a sheet reference I could save myself a lot of typing....
>
> Does anyone have any suggestions or workarounds?
>
> --
> Ted

Not sure I understand, but I put the following both in sheet2 and sheet3 in
the same range to simulate your account numbers that are the same as sheet
names:
sheet2 10
sheet2 20
sheet2 30
sheet2 40
sheet3 50
sheet3 60
sheet3 70

Then in sheet1 in cells a3 and a4 I put:
sheet2
sheet3
I then put the following formula in b3 and copied down to b4:
=SUMIF(INDIRECT("'" & A3 & "'!a1:a7"),A3,INDIRECT("'" & A3 & "'!b1:b7"))
which returned
100
180
So, if this is what you meant in your question, you should be able to adapt
the formula for your needs.


--
Kevin Vaughn


"Ted" wrote:

> I have a spreadsheet which contains a column with account numbers:
>
> ACCT #
> 1 =SUMIF(SheetReference1,Cell,SheetReference1)
> 2 =SUMIF(SheetReference2,Cell,SheetReference2)
> 3 =SUMIF(SheetReference3,Cell,SheetReference3)
> 4 =SUMIF(SheetReference4,Cell,SheetReference4)
>
>
>
> These Account numbers are also the name of sheets.
> I need to use the SUMIF function to extract information from each sheet.
> However, when I copy paste the formula down I have to manually go into each
> cell and change the sheet reference. This can be quite annoying because the
> SUMIF function contains the sheet reference twice. If I could imbed the
> Account Number as a sheet reference I could save myself a lot of typing....
>
> Does anyone have any suggestions or workarounds?
>
> --
> Ted