WorkSheet reference
 

I need to create a formula that change the reference of a sheet when i copy
the formula.

I have the formula: sumif(sheet2!$F:$F;"Total Rota:";sheet2!$H$H)

When i copy the formula for the next rows or columns, i want the reference
of the sheet change (sheet3;sheet4;...)

(Sorry my english) :)

Thank you
--
HC

WorkSheet reference
 

First, you have a few errors in your formula.
You've used semi-colons (;) instead of commas (,). And you're missing a
colon (:) in the last range reference.
To correct your formula:

=SUMIF(Sheet2!$F:$F,"Total Rota:",Sheet2!$H:$H)

Now for the sheets. The sheet reference will not change automatically. It
is not a relative position, so to speak. "Sheet2" is simply a sheet name
and has no relation to the order position of the sheet. You may try
entering the sheet names into a column, say AA1 through AA5 (use more rows
if needed). Assuming that your sheets are all in "Sheet1", "Sheet2",
"Sheet3", etc. format, you can use a formula like this.

=SUMIF(INDIRECT($AA1&"!$F:$F"),"Total Rota:",INDIRECT($AA1&"!$H:$H"))

When copied down, the AA1 row reference will increment as necessary
therefore changing the sheet reference in your formula.

HTH,
Paul


"lena_form" wrote in message
...
I need to create a formula that change the reference of a sheet when i copy
the formula.

I have the formula: sumif(sheet2!$F:$F;"Total Rota:";sheet2!$H$H)

When i copy the formula for the next rows or columns, i want the reference
of the sheet change (sheet3;sheet4;...)

(Sorry my english) :)

Thank you
--
HC

WorkSheet reference
 

assumed as...
When i copy the formula for the next rows or columns, i want the reference
of the sheet change (sheet3;sheet4;...)
maybe like this
on one sheet location along row(2) say e.g. sheet2!Z2
=SUMIF(INDIRECT("Sheet"&ROW()&"!$f:$f"),"Total
Rota:",INDIRECT("Sheet"&ROW()&"!$h:$h"))
---copy down you will have the sumif for sheet3, sheet4,
consecutively...Ref# will appear denoting that there is no sheet"n" in the
workbook.

regards,
driller


--
*****
birds of the same feather flock together..



"lena_form" wrote:

I need to create a formula that change the reference of a sheet when i copy
the formula.

I have the formula: sumif(sheet2!$F:$F;"Total Rota:";sheet2!$H$H)

When i copy the formula for the next rows or columns, i want the reference
of the sheet change (sheet3;sheet4;...)

(Sorry my english) :)

Thank you
--
HC

WorkSheet reference
 

Hello Paul,

In the portuguese version of Excel, we use semi-colons to separate the
arguments in a formula.
Thank you a lot for the help
--
HC


"PCLIVE" escreveu:

First, you have a few errors in your formula.
You've used semi-colons (;) instead of commas (,). And you're missing a
colon (:) in the last range reference.
To correct your formula:

=SUMIF(Sheet2!$F:$F,"Total Rota:",Sheet2!$H:$H)

Now for the sheets. The sheet reference will not change automatically. It
is not a relative position, so to speak. "Sheet2" is simply a sheet name
and has no relation to the order position of the sheet. You may try
entering the sheet names into a column, say AA1 through AA5 (use more rows
if needed). Assuming that your sheets are all in "Sheet1", "Sheet2",
"Sheet3", etc. format, you can use a formula like this.

=SUMIF(INDIRECT($AA1&"!$F:$F"),"Total Rota:",INDIRECT($AA1&"!$H:$H"))

When copied down, the AA1 row reference will increment as necessary
therefore changing the sheet reference in your formula.

HTH,
Paul


"lena_form" wrote in message
...
I need to create a formula that change the reference of a sheet when i copy
the formula.

I have the formula: sumif(sheet2!$F:$F;"Total Rota:";sheet2!$H$H)

When i copy the formula for the next rows or columns, i want the reference
of the sheet change (sheet3;sheet4;...)

(Sorry my english) :)

Thank you
--
HC

WorkSheet reference
 

Hello,

It worked perfectly.

Thank you a lot
--
HC


"driller" escreveu:

assumed as...
When i copy the formula for the next rows or columns, i want the reference
of the sheet change (sheet3;sheet4;...)
maybe like this
on one sheet location along row(2) say e.g. sheet2!Z2
=SUMIF(INDIRECT("Sheet"&ROW()&"!$f:$f"),"Total
Rota:",INDIRECT("Sheet"&ROW()&"!$h:$h"))
---copy down you will have the sumif for sheet3, sheet4,
consecutively...Ref# will appear denoting that there is no sheet"n" in the
workbook.

regards,
driller


--
*****
birds of the same feather flock together..



"lena_form" wrote:

I need to create a formula that change the reference of a sheet when i copy
the formula.

I have the formula: sumif(sheet2!$F:$F;"Total Rota:";sheet2!$H$H)

When i copy the formula for the next rows or columns, i want the reference
of the sheet change (sheet3;sheet4;...)

(Sorry my english) :)

Thank you
--
HC

WorkSheet reference
 

Is sheet name a must in the formula ? If not remove it and then copy the
fomula to other sheets ( I hope I have understood your requirements
properly).



"lena_form" wrote:

Hello,

It worked perfectly.

Thank you a lot
--
HC


"driller" escreveu:

assumed as...
When i copy the formula for the next rows or columns, i want the reference
of the sheet change (sheet3;sheet4;...)
maybe like this
on one sheet location along row(2) say e.g. sheet2!Z2
=SUMIF(INDIRECT("Sheet"&ROW()&"!$f:$f"),"Total
Rota:",INDIRECT("Sheet"&ROW()&"!$h:$h"))
---copy down you will have the sumif for sheet3, sheet4,
consecutively...Ref# will appear denoting that there is no sheet"n" in the
workbook.

regards,
driller


--
*****
birds of the same feather flock together..



"lena_form" wrote:

I need to create a formula that change the reference of a sheet when i copy
the formula.

I have the formula: sumif(sheet2!$F:$F;"Total Rota:";sheet2!$H$H)

When i copy the formula for the next rows or columns, i want the reference
of the sheet change (sheet3;sheet4;...)

(Sorry my english) :)

Thank you
--
HC