Hi all
I have been aske to make a spreadsheet that will build a set of formulas
based on the number of sheets being used. The sheets are name shA, shB etc
and can change. The formaula that I need is on a summary sheet and is as
follows:
fname = "=SUMIF(" & tmpName & "!E8:E107," & "=LO" & ",HT_end!D8:D107)"
where tmpName is the name of each spreadsheet, LO is the criteria for the
search.
The result is :
=SUMIF(HT_end!'E8':'E107',LO,HT_end!'D8':'D107') I was after
=SUMIF(HT_end!E8:E107,"LO",HT_end!D8:D107)
Any ideas or better method appreciated
Thanks

Did you use .formula or .formulaR1C1?

Just curious why you have tmpName in one portion and HT_End in the other. Did
you mean that?

Murray wrote:

Hi all
I have been aske to make a spreadsheet that will build a set of formulas
based on the number of sheets being used. The sheets are name shA, shB etc
and can change. The formaula that I need is on a summary sheet and is as
follows:
fname = "=SUMIF(" & tmpName & "!E8:E107," & "=LO" & ",HT_end!D8:D107)"
where tmpName is the name of each spreadsheet, LO is the criteria for the
search.
The result is :
=SUMIF(HT_end!'E8':'E107',LO,HT_end!'D8':'D107') I was after
=SUMIF(HT_end!E8:E107,"LO",HT_end!D8:D107)
Any ideas or better method appreciated
Thanks

--

Dave Peterson

Dave, was using .formulaR1C1.
The tmpName is the variable sheet name and was HT_End in this example.

Thanks

"Dave Peterson" wrote:

Did you use .formula or .formulaR1C1?

Just curious why you have tmpName in one portion and HT_End in the other. Did
you mean that?

Murray wrote:

Hi all
I have been aske to make a spreadsheet that will build a set of formulas
based on the number of sheets being used. The sheets are name shA, shB etc
and can change. The formaula that I need is on a summary sheet and is as
follows:
fname = "=SUMIF(" & tmpName & "!E8:E107," & "=LO" & ",HT_end!D8:D107)"
where tmpName is the name of each spreadsheet, LO is the criteria for the
search.
The result is :
=SUMIF(HT_end!'E8':'E107',LO,HT_end!'D8':'D107') I was after
=SUMIF(HT_end!E8:E107,"LO",HT_end!D8:D107)
Any ideas or better method appreciated
Thanks

--

Dave Peterson

Your formula string is written in A1 reference style. So use .formula instead
of .FormulaR1C1.

(Sorry for not making that more clear the first time.)

If that doesn't work, copy your code snippet from the VBE and paste into your
response.

Murray wrote:

Dave, was using .formulaR1C1.
The tmpName is the variable sheet name and was HT_End in this example.

Thanks

"Dave Peterson" wrote:

Did you use .formula or .formulaR1C1?

Just curious why you have tmpName in one portion and HT_End in the other. Did
you mean that?

Murray wrote:

Hi all
I have been aske to make a spreadsheet that will build a set of formulas
based on the number of sheets being used. The sheets are name shA, shB etc
and can change. The formaula that I need is on a summary sheet and is as
follows:
fname = "=SUMIF(" & tmpName & "!E8:E107," & "=LO" & ",HT_end!D8:D107)"
where tmpName is the name of each spreadsheet, LO is the criteria for the
search.
The result is :
=SUMIF(HT_end!'E8':'E107',LO,HT_end!'D8':'D107') I was after
=SUMIF(HT_end!E8:E107,"LO",HT_end!D8:D107)
Any ideas or better method appreciated
Thanks

--

Dave Peterson


--

Dave Peterson