In Excel 2000, I have a complex calculation (in a worksheet) that takes 4

parameters and uses them in 4 different sub-calculations, involving

relative/conditional sub-sub-calculations. In effect, it's a model.

Having done this, I now want to pour a series of numbers into this model, to

see its effect on various combinations of parameters.

Rather than type each parameter in, copy-paste-value the result into a

summary table, how can I get Excel to run the model on call from a worksheet?

I am illiterate in VisualBasic, so anything that avoids macroes would be cool.

parameters and uses them in 4 different sub-calculations, involving

relative/conditional sub-sub-calculations. In effect, it's a model.

Having done this, I now want to pour a series of numbers into this model, to

see its effect on various combinations of parameters.

Rather than type each parameter in, copy-paste-value the result into a

summary table, how can I get Excel to run the model on call from a worksheet?

I am illiterate in VisualBasic, so anything that avoids macroes would be cool.

- Calling sub w/ each iteration of Data Table
- Aggregating Results of Multiple Calculations
- Call macro after each iteration of solver
- XL: Examples of Interest Calculations Outside the United States
- Pivot to show the text instead of value calculation
- How to do conditional multiple pass calculations
- Failure Of Automatic Calculation When Worksheet Moved To Another File
- Grand total of the calculated field returns wrong results
- Sum of Squares Calculation
- Length of service calculated in calender months.
- For each loop nested; getting stuck on one cell in first iteration of nested loop
- VBA routine gets slower and slower on each iteration of the main loop
- Are there limits on number of variables / calculations
- History of the calculations
- Weeks of Supply Calculation
- Average of Multiple Calculations
- SumIf(values of non calculated cells in a row...)
- Automatically Populating a cell with the result of a calculation
- Aggregating Results of Multiple Calculations
- Generate chart from drop-down list of values
- Is it possible to do this in Excel..
- Bringing Excel SpreadSheet/Application To The Web
- Dynamic XvsY graphs
- Hiding the active cell marker / placeholder

I need to call a sub with each time my Data Tables calculate.

I use a macro to copy and paste a cell's value from one sheet to another so that I can create a few data tables. The way the data tables (under What-if Analysis) are working together is causing some problems as the cell that is being copied and pasted is not updating as the various data tables run. Is there a way to call the basic copy and paste macro with each iteration of the Data Table run?

Thank you.

I hope you can help me with the following task:

I have three workbooks called DATA, PROCESSOR and RESULTS.

DATA contains the input data that will be processed in the PROCESSOR workbook while the RESULTS will aggregate the results from the PROCESSOR workbook. The DATA workbook links to the PROCESSOR workbook.

I need a macro that will cycle through worksheet names (V1 on INPUT tab in the DATA workbook) – for each value in the V column of the INPUT worksheet of the DATA workbook this macro will recalculate the PROCESSOR workbook and copy the A to O columns from its Results worksheet to the worksheet of the RESULTS workbook whose name is currently selected in T1 on the INPUT worksheet of the DATA workbook.

Example cycle:

1) Set “1” to T1 on INPUT tab of DATA

2) Recalculate Processor (should be calculated only at this stage as it takes a long to calculate in the full variant)

3) Copy A to O columns from the RESULTS tab of PROCESSOR worksheet

4) Paste data into the “1” tab of the RESULTS workbook.

5) Continue to the next number in the list of the numbers in the V column on INPUT tab of DATA

I need this prototype macro to aggregate the results of the calculation on a large data set consisting of multiple data subsets.

I have attached all 3 workbooks.

I will be happy to hear what you think!)

Dima

I have read through this forum regarding this topic and have had no luck implementing a fix. What I need is, that at the end of each iteration of a solver instance, a macro is called. Each iteration should call the function "Macro1". As it is now, it appears that the solver is called, but the macro is not. My code is below:

Function Macro1()

Range("aq2").GoalSeek Goal:=0, ChangingCell:=Range("target")

Range("target").Copy

Range("as2").PasteSpecial Paste:=xlPasteValues

End Function

Sub SolveForSituations()

Dim sRangeName As String, sRangeAddress As String

Dim nResult As Long, nCalcMode As Long

sRangeName = "EscenariosSolverByChange"

sRangeAddress = Range(sRangeName).Address

Range("SolverResult").Clear

Range("ag2").Clear

Range("ai2").Clear

Range(sRangeName).Value = 25

solverReset

SolverOk SetCell:=Range("SolverTarget").Address, MaxMinVal:=2, ValueOf:="0", ByChange:= _

Range("EscenariosSolverByChange").Address

SolverAdd CellRef:=sRangeAddress, Relation:=1, FormulaText:="75"

SolverAdd CellRef:=sRangeAddress, Relation:=3, FormulaText:="-75"

SolverOptions StepThru:=True

SolverSolve UserFinish:=True, ShowRef:=Macro1

End Sub

Microsoft financial functions presume customary financial practices in the United States. Other countries have different laws about quoting and computing interest. This article ...

Is this possible to use pivot to present text data instead of value calculation e.g count, sum,%... in data area?

For example:

MOnth Jan Feb Mar

AAA USA AUS POL

BBB POL AUS USA

CCC BRI USA POL

Best regards,

TQV

I am an average good user of excel, till now I have been able to solve all kind of complex calculations some way but I have encountered a situation & wondering if there is any way to do this in excel or using some VBA. I would be grateful to all of you for this.

My excel sheet is some thing like this

Order No Require Item Require Qty Stock Status

10001 XXXXX 100 300 200

10001 YYYYY 100 200 100

10002 XXXXX 200 300 200

10002 YYYYY 200 200 100

10003 XXXXX 100 300 100

10003 YYYYY 100 100 000

There are a lot of different orders for different different products but all have some common ingredients. On any given day If I see my pending orders & Inventory then it looks like above & showing all stock available but I can't process all my orders as stock will be reduced after first order, then I wont be able to process second order but I can process 3rd one . What I want to do is process this data some way that It takes the first order & if all the material is available then reduce the stock so in above example after first order XXXXX will be left 200 & YYYYY 100. Then for next order XXXXX is sufficient but YYYYY is not available so no stock shall be deducted & for third order calculation also XXXXX will be 200 & YYYYY will be 100 & if there is any next order then for that XXXXX = 100 & YYYYY = 0 . Then show it in next column.

I don't think it's possible without VBA but I am not much familiar with that other than copy & pasting the codes from web & modifying here & there. But an expert might do it with excel also.. (I will appriciate if any1 can give me a VBA code ) This is just example in actual my data is 20000 or more row with a lot of product combinations.

Thanking you all in advance for your input & support

I have workbooks in which summary tables are generated by lookup formulas. Tables are on different sheets but all use the same lookup value by referring to a cell on the 'master' sheet. The lookup value appears in cells on all sheets, by reference to the master sheet (e.g. [formula] = mastersheet!$B$2).

The master sheet contains the main summary table and is copied and detached for distribution, using a macro. For practical reasons, this is done in two stages, first copying the sheet within the workbook (to make minor alterations), then moving it to a new book to save and distribute.

This is where the problem arises. After the sheet has been detached, we find that if we now change the lookup value on the master sheet in the original file, the tables on that sheet will update normally, but the cells on other sheets remain frozen at the previous value and the tables on those sheets do not update.

The only way round the problem is by Shift + Ctrl + Alt + F9. (Maybe I should add that all my workbooks are always set to automatic calculation.)

The macro itself is not the cause of the problem; if we follow the same procedure manually, the result is the same. However, if we move the sheet in one step, eliminating the intermediate copying stage, the problem does not arise. But this is evading the problem, not solving it, and I would be reluctant to have to resort to this.

The original problem remains as stated, viz. failure of automatic calculation.

Any help appreciated.

i have a problem regarding calculated field.. i have data of sales of a distributor with all the accounts that he handles.. i want to get the total number of buying accounts for that distributor..

buying account is an account that bought a specific product for at least one time... even the account buy 2 or 3 times more for that specific product the account is considered as 1 buying account..

i want to use a pivot table so i can manipulate the data.. first i tried to add an additional field to my data and put the formula

"if(norbo_val>0,1,0)" where norbo_val is the total sales less the rejects. it means if ever an account have positive sales the account is considered as a buying account for that specific product. my problem is that when that account bought the same product for 2 or more times it sums all the times an account bought for that specific product..

so i decide to remove the field in my data and create a calculated field in the pivot table. i input the same formula to the calculated field and it gives me the correct result where if ever an account bought a specific product for at least one time regardless on how many times the account bought that same product, the account is considered as 1 buying account. the only problem i am encountering now is that the grand total is not summing up the total numbers of buying accounts if i list all the accounts. it seems the formula is affecting the grand total where it should add the total of buying accounts but instead using the formula of the calculated field so the result is only 1.. pls. help me...thanks!

i want to add the sample data in this thread but i dont know how to add the file.

I'm trying to do a sum of squares calculation, between and within groups, across multiple columns.

I llike this to occur automatically when I input the data. Where I've running into difficulty is calculating the mean of each group(without sorting and manually selecting). See sample of data below:

Typestdby_currentrx_currentNormal0.141116170.171218Rework0.140827160.170686Normal0.140360580.172524Rework0.136112870.167756Normal0.141427740.170232

Note that there are more columns of different parameters I've just inserted two for demonstation

Any help appreciated.

Thanks

Ed

I have to calculate redundancy entitlements based on length of service

calculated in completed calender months. DATEDIF(Date1,Date2,"m")

works fine 99.9% of the time except that it ignores the days in the

dates. For this calculation an employee is deemed to have completed a

months service if the days of the month are inclusive.

For example; 22/03/2007 to 22/05/2007 is clearly two months service,

but so is 22/03/2007 to 21/05/2007 because they are deemed to have

completed the month, whereas 22/03/2007 to 20/05/2007 is only one

completed month. ( I didn't make the rule. That's how it is, )

So now I have =IF(((DAY(A10))-1)=(DAY(B10)),DATEDIF(A10,B10,"m")+1,

(DATEDIF(A10,B10,"m"))) but this doesn't work if the number of days in

the month of the second date is greater than the number of days in the

month of the first date.

I'm going crazy trying to work this out and I owe it to these people

that the entitlements are 100% accurate. Any help would be much

appreciated!

Dave

Schedule I

A6 = 8006-001

A7 = 9080-002

A8 = xxxx

etc.

Schedule K - 2008 Final

D12 = 8009-001

B31 = TOTAL COSTS

I31 = $9,000

The above portion in bold on Schedule K repeats but the different values in the first and last cells. The middle cell will always say 'TOTAL COSTS.' This is where I am getting stuck on.

In short, I am starting with cell A6 on Schedule I (this is the 'project' object variable in the first for each loop), taking that cell and going to Schedule K and running it down column D (using 'b' as the object variable for the second for each loop) to compare for a match. If a match is found inside of this second/nested for each loop, then I want to use the FIND method and move over 2 columns to the left, and then down that column (B) to locate the cell that contains the next occurence of the letters 'TOTAL COSTS.' Then from 'TOTAL COSTS' I want to move over 7 cells, get the value in that cell, and drop it in a cell on the first tab, Schedule I.

The problem is that on the first iteration of the nested for each loop, the program is getting stuck on the object variable 'findTotalCost.' Then comes across and extracts $9,000 and pastes it on the Schedule I. This is ok for the first iteration. However the $9,000 value gets pasted again in the next cell below, again and again with each iteration.

So the program is stuck on the first occurence of 'TOTAL COSTS.' I need to somehow clear the value of 'TOTAL COSTS' at the of each iteration. Al least that's what I think. That way each occurence of 'TOTAL COSTS' will be picked up not just the first one.

Please help.

Sub billable2008() Dim b As Range Dim project As Range Dim findTotalCost As Range r = 6 For Each project In Worksheets("Schedule I").Range("A:A") For Each b In Worksheets("Schedule K - 2008 Final").Range("D:D") If b.Value = project.Value Then Set findTotalCost = Columns(2).Find(what:="TOTAL COSTS") Worksheets("Schedule I").Cells(r, 28).Value = findTotalCost.Offset(0, 19).Value Exit For End If Next b r = r + 1 Next project End Sub

I have a VBA routine that on each loop updates data from queries, prepares some formatting and then prints to pdf. This all works fine, the problem is that it starts off and produces each pdf in around 30 seconds but on each iteration this gets larger and larger (and there are 700 schools/iterations we need to produce the report for) in the end it slows Excel right down practically to a halt and I have to close down and reopen Excel - then everything is fast again.

Anyway I've narrowed the problem down to this function that changes a chart series and adjusts the scale depending on certain criteria. This function seems to be the one that causes the loop to take longer and longer. Has anyone any ideas what it could be??

Note that I've declared some worksheet types as module level public vars now to try and speed things up so these vars are declared at top of module (and so get declared only once during cycle instead of getting declared on every iteration of the loop)

Many thanks for any help:

Public Sub AmendCharts(sType As SchoolType) ' Sorts out NOR Series for different phases ' S Barker Jul 2008 ' Updated: Sept 08 - Now includes code to sort SEN Scales depending on School Type - S Barker Sept 2008 Set wks = Sheets("Disp_PupChars01") Dim ColFrom As Byte Dim ColTo As Byte Dim StartCol As Byte StartCol = Range("NORYearGroupStart").Column Select Case sType Case Nursery: ColFrom = StartCol ColTo = StartCol + 1 Case SpecialNursery: ColFrom = StartCol ColTo = StartCol + 1 Case Primary: ColFrom = StartCol + 2 ColTo = StartCol + 8 '27 Case SpecialPrimary: ColFrom = StartCol + 2 ColTo = StartCol + 8 '27 Case Secondary: ColFrom = StartCol + 9 '28 ColTo = StartCol + 13 '32 Case SpecialSecondary: ColFrom = StartCol + 9 '28 ColTo = StartCol + 13 '32 Case Sixth: ColFrom = StartCol + 14 '33 ColTo = StartCol + 15 '34 Case SpecialSixthForm: ' Include yr 14 for special sixth forms ColFrom = StartCol + 14 ColTo = StartCol + 16 End Select 'wks.ChartObjects("Chart 4").Activate 'ActiveChart.PlotArea.Select wks.ChartObjects("Chart 4").Chart.SeriesCollection(1).Values = _ "='Disp_PupChars - Table Sheet'!R14C" & ColFrom & ":R14C" & ColTo wks.ChartObjects("Chart 4").Chart.SeriesCollection(2).Values = _ "='Disp_PupChars - Table Sheet'!R15C" & ColFrom & ":R15C" & ColTo wks.ChartObjects("Chart 4").Chart.SeriesCollection(1).XValues = _ "='Disp_PupChars - Table Sheet'!R12C" & ColFrom & ":R12C" & ColTo wks.ChartObjects("Chart 4").Chart.SeriesCollection(2).XValues = _ "='Disp_PupChars - Table Sheet'!R12C" & ColFrom & ":R12C" & ColTo Set wks = Nothing ' Updated code to now sort the SEN scales out depending on phase ' S Barker Sept 08 Dim ScaleRange As Long, x As Byte Select Case sType Case Nursery: ScaleRange = 70 Case Primary: ScaleRange = 80 Case Secondary: ScaleRange = 60 Case Sixth: ScaleRange = 30 Case Else: ScaleRange = 100 End Select Set SEN_Sht = Sheets(SENSht) ' Do for each of the 4 charts.. For x = 1 To 4 Set SEN_Chart = SEN_Sht.ChartObjects("Chart " & x) SEN_Chart.Chart.Axes(xlValue).MaximumScale = ScaleRange Set SEN_Chart = Nothing Next x Set SEN_Sht = Nothing End SubFor info this is an excerpt of what is declared at the top of the module

Public wks As Worksheet Public SEN_Sht As Worksheet Public SEN_Chart As ChartObject Public Enum SchoolType Nursery = 1 Primary = 2 Secondary = 3 Special = 4 Sixth = 5 SpecialNursery = 6 SpecialPrimary = 7 SpecialSecondary = 8 SpecialSixthForm = 9 End Enum

that has numerous worksheets relating to financial years. Each sheet is

populated by extracting data from our SUN (Systems Union) accounting

application using their 'Vision XL' add-in. VBA macros do a lot of the grunt

work and have numerous variables, including some relating to named ranges

that are referenced in the multitude of formulae within each sheet.

While these financial workbooks have been working fine over the last years,

our client information has grown and obviously financial years are added

annually and the workboks for some of our clients have become rather large.

My management is concerned whether there could be any limitations in

Excel/VBA that might cause them to stop working or give unpredictable results.

Does anyone know of any limits on the number of variables / calculations (or

combination thereof) that we should be wary of?

Many thanks in advance.

Keith Carter

Say, I have choose cell B4 to give the sum of B1 and B2. So, the formula for cell B4 is =B1+B2.

I will change B1 and B2 sometimes, so B4 will change along with them.

I want a list of cells giving me a history of the calculations B4 has performed.

So for example, I'd like C1:C10 to each automatically contain a history of B4's results, with the oldest being C1 and the most recent being C10.

Does anyone know how to do this?

~ Ado

for a production scheduling application? For the data:

Week 1 2 3 4 5

Production 5 3 1 1 1

Sales 0 2 3 2 1

Inventory 5 6 4 3 3

WOS 2 3 99 99 99

The trick is to develop a formula that takes the week 1 ending inventory of

5 and then subtracts sales of 2 in week 2, then subtracts sales of 3 in week

3, and upon realizing that at week 3 the inventory is <=0 you take [week] 3

minus [week] 1 to get 2 weeks of supply. The same calculation repeats for

week 2. For week three 99 is indicated to show the future sales listed won't

deplete the inventory.

Does anyone know of a way to calculate this without averaging the sales or

setting up an akward cumulative calculation for each week and using the match

and index functions?

This weeks of supply calculation has a number of applications. I suprised

there isn't a standard function in Excel with functionality along the lines

of "this value will reach zero in x periods with the irregular series of

withdrawls in range b1..m1"

I have two columns of numbers, and I would like to divide the 1st of each

pair by the 2nd of the pair, and get the average of those calculations. Kind

of addressing "the sum of the averages is different from the average of the

sums" issue. So my data might look like this:

A B

1 10 4

2 11 3

3 15 6

etc

If my brain works right, the average of dividing 10/4, 11/3, & 15/6 would be

2.89. Is this a number I can get with some sort of CSE formula?

Thank you in advance.

the values of non calculated cells...?

I hope you can help me with the following task:

I have three workbooks called DATA, PROCESSOR and RESULTS.

DATA contains the input data that will be processed in the PROCESSOR workbook while the RESULTS will aggregate the results from the PROCESSOR workbook. The DATA workbook links to the PROCESSOR workbook.

I need a macro that will cycle through worksheet names (V1 on INPUT tab in the DATA workbook) – for each value in the V column of the INPUT worksheet of the DATA workbook this macro will recalculate the PROCESSOR workbook and copy the A to O columns from its Results worksheet to the worksheet of the RESULTS workbook whose name is currently selected in T1 on the INPUT worksheet of the DATA workbook.

Example cycle:

1) Set “1” to T1 on INPUT tab of DATA

2) Recalculate Processor (should be calculated only at this stage as it takes a long to calculate in the full variant)

3) Copy A to O columns from the RESULTS tab of PROCESSOR worksheet

4) Paste data into the “1” tab of the RESULTS workbook.

5) Continue to the next number in the list of the numbers in the V column on INPUT tab of DATA

I need this prototype macro to aggregate the results of the calculation on a large data set consisting of multiple data subsets.

I have attached all 3 workbooks.

I will be happy to hear what you think!)

Dima

I am trying to generate a chart which would have:

- On the X axis, the "parameter": all the values included in a drop-down list (created with data validation)

- On the Y axis, the "results": the values taken by another cell (the content of the cell changes when the parameter is changed from the drop-down list).

In the attachment, please make as if the "SupportSheet" worksheet did not exist, and assume you only have the "Chart" worksheet. My issue is that the result is generated from a series of complex calculations from a different file, so it is not trivial to create a table with all the values to be charted.

I am using Excel 2002 (sic), but could get hold of a more recent version if needed.

Thanks in advance for your help!

I am an average good user of excel, till now I have been able to solve all kind of complex calculations some way but I have encountered a situation & wondering if there is any way to do this in excel or using some VBA. I would be grateful to all of you for this.

My excel sheet is some thing like this

Order No Require Item Require Qty Stock Status

10001 XXXXX 100 300 200

10001 YYYYY 100 200 100

10002 XXXXX 200 300 200

10002 YYYYY 200 200 100

10003 XXXXX 100 300 100

10003 YYYYY 100 100 000

There are a lot of different orders for different different products but all have some common ingredients. On any given day If I see my pending orders & Inventory then it looks like above & showing all stock available but I can't process all my orders as stock will be reduced after first order, then I wont be able to process second order but I can process 3rd one . What I want to do is process this data some way that It takes the first order & if all the material is available then reduce the stock so in above example after first order XXXXX will be left 200 & YYYYY 100. Then for next order XXXXX is sufficient but YYYYY is not available so no stock shall be deducted & for third order calculation also XXXXX will be 200 & YYYYY will be 100 & if there is any next order then for that XXXXX = 100 & YYYYY = 0 . Then show it in next column.

I don't think it's possible without VBA but I am not much familiar with that other than copy & pasting the codes from web & modifying here & there. But an expert might do it with excel also.. (I will appriciate if any1 can give me a VBA code ) This is just example in actual my data is 20000 or more row with a lot of product combinations.

Thanking you all in advance for your input & support

Is it possible to do this? I have a spreadsheet application with a large number of complex calculations that I want users to be able to access on their blackberrys/laptops/desktops online. I've considered simply re-creating the spreadsheet as an asp and using javascript as a backend to perform the calculations, but due to time constraints it wouldn't be very viable option for me to recreate this from scratch. The spreadsheet is so large it would likely take me a few days just to re-create the design in ASP!

Any thoughts, recommendations? I tried saving the spreadsheet as an mhtml and considered just replicating functionality from that with javascript, but that produced some unmaintainable markup, and I don't even know where to begin to get what used to be controls working (though I wasn't expecting too much...).

In a nuthsell, I have a spreadsheet, and I want to make it LIVE.

P.S.

Oops on the title typo.

I need to know how to set the x-axis source data for a given series.

thanks for any help in advance

I feel a bit silly asking this in the presence of so many obvious excel gurus: but here it goes.

I've created some user forms, activex controls, etc. that do a series of complex calculations, supported by VBA and it's at the point where I want to roll it out.

How do I hide / get rid of the active cell marker on the sheets? I've looked in the "options - view" section and have lucked out. Can anyone help?

Cheers,

Jason

No luck finding an answer? You could always try Google.