Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an Array Formula
I would like to create a Macro that would at least help me on th 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 conten criteria,e.g. look for cell value where Sales is 1000, Saleperson = John & Sales % i 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 rang of cell where data are marked in * are required to be copied to a row belo cell where value="Backlog" under worksheet name "SalesSummary" under the sam excel file 3) Using example above, to create a formula that will sum up all valu under Sales Column where Sales % is =75%, Saleperson = Carmen & Sales is 4000 fdtoo, Thanks! -- fdto ----------------------------------------------------------------------- fdtoo's Profile: http://www.excelforum.com/member.php...fo&userid=2579 View this thread: http://www.excelforum.com/showthread.php?threadid=39201 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an Array Formula
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an Array Formula
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
creating an array | New Users to Excel | |||
creating an array | Excel Worksheet Functions | |||
Tricky array formula issue - Using array formula on one cell, then autofilling down a range | Excel Programming | |||
Creating an array | Excel Programming | |||
Returning an Array as part of creating a cell formula | Excel Programming |