I want to create a button on this spreadsheet - the button will be called z4 in the "Yr 9 ICT" sheet
it will include the following
rows Jane Bloggs 2
rows Jane Bloggs 12
rows Jane Bloggs 22
rows Jane Bloggs 32
rows Jane Bloggs 33
rows Jane Bloggs 42
rows Jane Bloggs 44
rows Jane Bloggs 66
What do I have to do to create this.
I have some Excel models out in the field with some old VBA code that is now causing problems. I regularly develop 'patch' models to make updates or repairs to user models, but need the VBA code syntax to get into a Worksheet module and change one line of VBA code.
The worksheet is named 'Forecast' and I need to change line 5 of my VBA code.
The vast majority of my users are on Excel 2000. I remember the security issues with newer Excel versions and can walk then through that, but I forgot the code syntax I need to get the job done.
I have 3 cpmmandbuttons in a spreadhseet named "Controls" . viz
Trim and Edit Base DataCreateReportArchive
They are always run in the same order.
My Aim is to disable and gray out the Archive button once its assigned macro is run(by clicking it) so that it activates only next time that the workbook is opened.
I was wondering how to create buttons such that when the user clicks on the button they are automatically taken to a different part of the workbook.
I'd like to do this because my spreadsheet is now large enough such that navigating just using the sheet tabs at the bottom is not ideal.
I can create a hyperlink, but I think a button just looks nicer.
Is it possible to put the standard Excel buttons (as in the ribbon in 2007 or the toolbars in 2003) actually on the spreadsheet. I would like, for example, to put the " pivot table refresh button" on my spreadsheet somewhere in the middle, separate from the program. Can I extract or import these Excel program's buttons somehow to insert elsewhere?
View 3 Replies View RelatedI would like to set up a maintenance register where I would have:
worksheet 1 - a page where staff can choose a location and choose a maintenance issue and hit submit
worksheet 2 - after they have hit submit the detail will appear in designated cells in worksheet 2 (a ledger of maintenance issues). so each submit should send the info to a new line in worksheet 2.
I have a basic knowledge of Excel and have used formulas and functions before but not VBA/Macros. Any step by step through setting up the submit button and macro correctly?
I've tried to do Worksheet("Register").Range("A2").Value = Worksheet("Request").Range("A1") after installing a push button but all I get is errors.
I am trying to create a worksheet that has multiple radio buttons. Each radio button is linked to a Macro. I need there to be many buttons running down one side of the worksheet. Each button needs to perform a macro that is relevant to the cells in the same row that it is on.
Essentially what i am trying to do is make a macro that when the button is pressed copys data from H6 and paste it into B6. The button is situated above I6. I need a button for each row from 6 to 110.
While this macro is easy to create (i use the record button and then assign the macro to the button), i would have to do this 104 times and assign a new macro to each button.
Is there a quicker way?
Is it possible to change the color of buttons or command buttons? There does not seem to be any place that allows this under properties for buttons, although there does for command bars. However, I've tried recording a macro as I change the color, but nothing get's recorded so I'm not sure what the syntax would be.
I have a spreadsheet with several buttons and I'd like them to change colors as they are pressed so it's possible to see what you've already done. And then, as soon as any other cell on the sheet is changed, the buttons reset color.
I'm currently developing a model for a payment schedule. For example, lets say a customer purchases an item from a store, but the store receives that payment in the following month. I'm looking to develop a dynamic model so that if I change the assumption from 1 month to another term (2,3,4+ months etc.) the model will adjust accordingly.
I've started with a formula
=IF(MONTH(H3)-MONTH(G3)=B14,G11*B13)
But of course this will not work once the term is set to larger than 1 month. With B14 representing 1 month, and G11*B13 being payment information (price * quantity).
1. In column A, I have models, in column B, I want to place either a "printer" if the model begins with Phaser or Laser, and if it begins with WorkCentre make it MFD. Also, if the Phaser has MFP on the end, make this an "MFD" and If Color 560 or Docucolor, make this "Out of Scope". How do I read the first part of the string in column A to do this, or do I need to make a second column with this Phaser and WorkCentre broken out?
I have this code attached, but only works if I have the word Phaser or WorkCentre.
I have a sheet with a table of peoples hours they are doing during the week each day. I want the hours to be put into a model shading the appropriate times to show when people will be in. I have attached a sample sheet of what i want it to do which i have had to do my self. Sheet 1 has table in and sheet 2 has the model i want to be automatically filled in once ihave entered all my data in the original table.
I am having a very difficult time determining how to get started with this problem in VBA. I am not sure how to generate the failures, or start the array. I have been racking my brain for quite some time. Company B makes a product that has two component parts. The results of reliability testing for each component shows that the components fail according to an exponential distribution with the following mean time between failure (mtbf).
ComponentMTBF(months)
115
224
Below are Company B's sales records for the past 36 months.
MonthSalesMonthSalesMonthSales...............................
I have a deferred revenue model. Revenue from each month earns out at an equal rate over twelve months. Sales for each month are in column B (B21) and C. I created formulas for the first year but when I get to the second year, I'm not sure what to do, because the earned premium will include values from previous year sales and current year sales.
View 4 Replies View RelatedSuppose that you have some data points and you want to fit a straight line to them. You write the line's equation in the cells ajacent to your data points, make the square of each difference, sum up all the squares and you have your objective function. Of course your line equation depends on two parameters, that you enter in two cells. Then you run the Excel Solver, enter your objective function as target cell to minimize, enter the range containing your parameters, set your constraints and off you go.
So far so good.
And now suppose that in order to generate your model data it is not enough to write a straight line equation in some cells, but rather to run a complete reactor model (contained in a macro). How do I couple that model-generating macro with the Excel Solver? Now, I actually found a solution. I wrote a Worksheet_change macro in the sheet containing the model parameters, which calls the model-generating code each time the parameters are chenged by the Excel Solver. This has worked fine for a simple example as the straight line one.
Now one of the two:.....
What would be the best way to put together a cancel curve on an annual membership model?
View 5 Replies View RelatedI have a question around making a summary table from a model. I will try to explain this as plainly as possible.
I have a model where you can toggle 3 different inputs across two variables. The final output number of the model will vary accordingly.
I would like to create a 3X3 table with the final output for each combination of variables. Is there a way of doing this using formulas or would I need a macro?
I am charged with building a predictive model that can find items in a hospital inventory that will expire before they are used. The idea is simple: if I use an item every week, this item has an expiration date of 50 weeks from now, and I have 60 on the shelf, then I will likely expire 10 of the items.
The complexity is that there are multiple expiration dates. So, I want be able to look down a list of 3000 items, find the multiple entries of expiration dates, and isolate them based on criteria (select, for example, the first chronologically expiration date).
I have recently started working on a model built by some one else and I need to add a extra row to model. The Model has lot of macros on every page. So i need to know how adding an extra row is going to affect the macros.
I understand the main problem would be that the references in the macro wont get updated which might lead the macro to malfunction.
Whats the best way to analyze which macros are getting effected.
I have: + three raw data sheets (needed as 3 lots of data with 25K records in each). + I also have a model that I want to take each record from each sheet, populates some cells in a seperate calculation sheet and then gets a result - which it adds to a total (array).Finally I have + a clever macro that shows me what % of the way through I am (i.e. displays the number of record processed out of the 75K (3*25K) total)
While I have got the model to work - there is some untidy code used where I start my clever % macro for x = 1 to 75K then repeat the same instructions for running the model for each section i.e. sheet 1 : 1 to 25000, then the same instructions for sheet 2, 1 to 25000 etc etc. and then end it I want to know if there is any clever coding that will let me go from one sheet to another when there is an empty cell struck on the active sheet?
I've inherited the following code, which uses solver to create a sensitivity table for a given return in a derivatives model:
Private Sub CommandButton1_Click()
'Yield Analysis
'Application. ScreenUpdating = False
x = Range("Correlation")
v = Range("SwapCur")
w = Range("DebtLTV")
Range("ValYLD").Value = 0.005
For i = 1 To 5
For j = 1 To 7
Range("ValYLD").Value = 0.005
Range("DebtLTV") = 0
a = Range("IRRung")
b = Sheets("Cashflow").Cells(35 + j, 4)
c = Range("SwapFX")
d = Sheets("Cashflow").Cells(35, 4 + i).................
It seems that the code does not cope well when negative numbers are used as inputs, as the results it gives are unexpected and incorrect. I have fiddled with various elements but to no avail. All inputs are percentages. i= 1to 5 are always positive, j 1-7 percentages, centred around "SwapFX". It is these inputs, along with "swapcur" which are now negative, and previously have not been.
I have a spreadsheet that tries to model a possible stock price movement in a column. So in column A I have all dates starting from today and in Column B a specific stock's possible stock price movemens onver the next 2 years.
So dates start at A3 and stock prices start at B3 and end at A734 and B734.
This gives me ONE possible stock movement and to get the most accurate movement, I will need to model this 20,000 times.
Therefore, I want to create a loop macro that repeat this motion 20,000 times in the columns next to A and B. So which loop effect would I need to use and how would the code look like ? This is honestly my first time attempting to use a loop macro
I need to optimize an Excel model with 5 variables each of which cam be any value from 1 to 20. The solution space, therefore, contains 3,200,000 combinations. I have a software(Palisade Risk) which can give out a random sampling of this solution space. I need to know how many combinations can be considered as a representative sample of this solution space - e.g. can it be 1%?.
View 5 Replies View RelatedI want to take a shot at this spreadsheet exercise. Setup similar spreadsheet models.
Excel Exercise.xlsx
find the attached file. Book 2 there is 4 columns ie,reg no,fleet no,product & model and on book 1 there is fleet no,reg no and model and what i need is to capture fleet no & model from Book 1.
View 2 Replies View RelatedWe are trying to work out a pricing model based on how many units we will produce. In production the price per unit will be really high when you produce a low quanity, and the price per unit will be lower with the more units you produce (but never reach zero). What we are trying to accomplish is to create a formula to build a pricing structure that accounts for this curve without having to price every possible scenario.
For example, if we have the following data where for 1 unit we charge $3 per unit to produce, for 5 units we charge $2 per units to produce, etc...:
1 units @ $3 per unit
5 units @ $2 per unit
10 units @ $1 per unit
What I need to figure out is how much it would cost per unit if we were to produce 3 units. I'm guessing it would be somewhere around the $2.40 mark give or take. Or say 8 units, would be something like $1.50 per unit.
Is there a formula that can calculate this? I've tried thinking of it as a moving average, or prorating the pricing.
I'm try to do a what IF Formula, so I can automatically add the cost into the right year for a lifecycle cost
My criteria is in column K I have either New Build or Existing and column AT I have year 12 of the lifecycle and column AZ I have year 18
And what I want the result to say is IF Column K Say New build I want the value of the first maintenance year to start in year 18 and column k say Existing I want the value of fist year Maintenance to start in Year 12
I already have this formula in the Column AT&AZ working out he value
IF(ISERROR(MROUND((AQ$1-$Q2),$P2)),"0",
IF((MROUND((AQ$1-$Q2),$P2)+$Q2) = AQ$1,$AF2,"0"))
I have historical sales for each month from the last 3 years. I would like to set up a seasonality adjustment for forecasting. I know that it won't be perfect and some may even suggest using regression, but I'd rather not. I'd like to do the following:
In a control sheet, list the months in chronological order beginning in cell A1; January, A2, February; etc., etc. through December.
Next, below each month, I'd like to have a factor. For example, in B1 beneath January, I'd like to be able to plug in 75%. This would say that for each January going forward, that it is 75% of the annual historical average for all periods. Whereas, say in June, it is 140%.
I have the catalog #s of some products in column A, and I have the brand in column B, Product name in Column C, and the model # in Column D. I want to create a formula that if I select the catalog # it should autopopulate the brand in column B, product name in column C and the model # in column D!!
And I have the brand/product & model # in a different sheet!!
Is it possible that a cell contains both numeric and alphanumeric data and to do calculations on that?
For example: if a cell conatain the value "10a" or "8.5b" etc. Would it be possible to have a column that gives me the hours worked (the numeric value in the cell) and a line that gives me the amount of people that are working on shift "a" (the alphanumeric value in the cell).
Is this at all possible? Or does that require VBA/Macros and stuff (in which case this is posted in the wrong part of the forum )
I need a formula that will bring up the Model type of a product based on its Material code. All the models I need to generate have a "root" number in them (the first 6 digits define model type). This is what I need to fill out:
View 2 Replies View Related