Creating an Array Formula

[fdtoo]

I would like to create a Macro that would at least help me on the following:

1) to copy the content of an excel filename"SaleForecast.xls" under
Worksheet name="Sales"
to another excel filename="ForecastSummary.xls" under worksheet
name="SalesSummary"

2) to look for a row of data that fits the following content criteria,e.g.
look for
cell value where Sales is >1000, Saleperson = John & Sales % is >75%:

ColumnA ColumnB ColumnC
Sales Saleperson Sales %
5000 Carmen 75
1000 John 30
8000 Weller 75
2000* John* 80*

*Note:Above data is under worksheet name "Salesman" and the whole range of
cell
where data are marked in * are required to be copied to a row below cell
where
value="Backlog" under worksheet name "SalesSummary" under the same excel
file

3) Using example above, to create a formula that will sum up all value under
Sales Column
where Sales % is >=75%, Saleperson = Carmen & Sales is >4000

fdtoo,
Thanks!


_

[Patrick Molloy]

assuming your table is A2:C5

Option Explicit

Sub SetFormula()
Dim frmla As String

frmla = "=SUM( (R2C2:R4C2=SalesPerson)" & _
"*(R2C1:R4C1>=Sales)" & _
"*(R2C3:R4C3>=Percent)" & _
"*(R2C1:R4C1))"

Range("G2").FormulaArray = frmla

End Sub



"fdtoo" wrote:

>
> I would like to create a Macro that would at least help me on the
> following:
>
> 1) to copy the content of an excel filename"SaleForecast.xls" under
> Worksheet name="Sales"
> to another excel filename="ForecastSummary.xls" under worksheet
> name="SalesSummary"
>
> 2) to look for a row of data that fits the following content
> criteria,e.g.
> look for
> cell value where Sales is >1000, Saleperson = John & Sales % is
> >75%:
>
> ColumnA ColumnB ColumnC
> Sales Saleperson Sales %
> 5000 Carmen 75
> 1000 John 30
> 8000 Weller 75
> 2000* John* 80*
>
> *Note:Above data is under worksheet name "Salesman" and the whole range
> of
> cell
> where data are marked in * are required to be copied to a row below
> cell
> where
> value="Backlog" under worksheet name "SalesSummary" under the same
> excel
> file
>
> 3) Using example above, to create a formula that will sum up all value
> under
> Sales Column
> where Sales % is >=75%, Saleperson = Carmen & Sales is >4000
>
> fdtoo,
> Thanks!
>
>
> _
>
>
> --
> fdtoo
> ------------------------------------------------------------------------
> fdtoo's Profile: http://www.excelforum.com/member.php...o&userid=25797
> View this thread: http://www.excelforum.com/showthread...hreadid=392014
>
>

[Patrick Molloy]

see www.cpearson.com for all your array formula solutions!

"fdtoo" wrote:

>
> I would like to create a Macro that would at least help me on the
> following:
>
> 1) to copy the content of an excel filename"SaleForecast.xls" under
> Worksheet name="Sales"
> to another excel filename="ForecastSummary.xls" under worksheet
> name="SalesSummary"
>
> 2) to look for a row of data that fits the following content
> criteria,e.g.
> look for
> cell value where Sales is >1000, Saleperson = John & Sales % is
> >75%:
>
> ColumnA ColumnB ColumnC
> Sales Saleperson Sales %
> 5000 Carmen 75
> 1000 John 30
> 8000 Weller 75
> 2000* John* 80*
>
> *Note:Above data is under worksheet name "Salesman" and the whole range
> of
> cell
> where data are marked in * are required to be copied to a row below
> cell
> where
> value="Backlog" under worksheet name "SalesSummary" under the same
> excel
> file
>
> 3) Using example above, to create a formula that will sum up all value
> under
> Sales Column
> where Sales % is >=75%, Saleperson = Carmen & Sales is >4000
>
> fdtoo,
> Thanks!
>
>
> _
>
>
> --
> fdtoo
> ------------------------------------------------------------------------
> fdtoo's Profile: http://www.excelforum.com/member.php...o&userid=25797
> View this thread: http://www.excelforum.com/showthread...hreadid=392014
>
>