How can I continue to replicate this formula automatically?
Interest RateAmountM1M3Formula Required
1%10000010001000=B2*C2
2%10000020001000=B2*C3
3%10000030001000=B2*C4
4%10000040002000=B3*C5
5%10000050002000=B3*C6
6%10000060002000=B3*C7
7%10000070003000=B4*C8
8%10000080003000=B4*C9
9%10000090003000=B4*C10
10%100000100004000=B5*C11
11%100000110004000=B5*C12
If I need to replicate a formula in some cells, I usually drag it down or sideways. Is there any easier way to do this? I have large numbers of cells into which to drag the formula. Can I specify which cell range the formula is applicable to (i.e. A1:A10,000 or something) instead of dragging? If so how?
View 5 Replies View RelatedActually i can do it just by using LOOKUP formula but the mark scheme says LOOKUP and IF.
If i use Just LOOKUP i should do the same thing for other cells but i want to generate a formula and replicate it through all the cells.
Here is the question;
Enter formulae in cells C2:C10 that refer to Book Orders.CSV and display whether the Text Book is Required by the student whose Student id is entered in cell A2.
I also uploaded the source file. Q3.xlsx
I have two formulas which I'm using: =180*(120.5-1)/119.5 and =90*(60.5-1)/59.5
In the first example, I want to replicate the formula across row 1, but with the value '1' incrementing by 1 each time, upto 240.
In the second example I want the '1' value to also increase by 1 for each cell down column B, up to 120. when I try dragging the formula down or across, I am only able to replicate the formula exactly as in the initial cell (ie. the '1' doesn't change). I've tried using the $ symbol, but this doesn't work. I don't fancy doing this manually for a total of 360 cells!
I have a workbook with ten sheets. The front sheet is a summary with nine divisional data sheets (DDS) following. In every sheet cell G12 has the date. At the moment the DDS reference the summary sheet and pick up the same date from it. Is it possible to set it up so that if I change the date in cell G12 on any of the sheets I can get them to all read the same?
View 11 Replies View RelatedI'm exploring a different approach to my current project.
Sheet1, CellA1 contains the year ie. 2007
Sheet1, CellA2 contains the storage directory ie. E:archiveyear
Sheet1, CellA3:A10 contains the names of the employees.
Here is what I would like to make happen.
Running this in a macro, the result would be a copy of Workbook2 renamed for each employee's name in cells A3 through A10 and all saved in a newly created directory of
E:archiveyear2007employee name1.xls
E:archiveyear2007employee name2.xls and so on..
I have a userform that has nested multipages (5 in the outer page, 4 in the inner page).
On each of these multipages, I want to have the same controls (sliders) laid out in the same order - but with unique names, named after their tab location, for each control so I can use their value property later in the code.
I've designed the layout and named all the controls on my first sheet (e.g. Slider1Outer1Inner1). I now need a way of automating the replication of these across the other 19 sheets (including the nested inner multipage!); so that equivalent slider for example would be called Slider1Outer1Inner2, Slider1Outer1Inner3 etc.
I'm not sure about coding VBA to act on items within VBA...
VB:
" For each multipage in outer
For Each multipage In inner
For Each Object In current multipage
Copy inner.object -> Next multipage
Inner.object.name = CurrentOuter & CurrentInner & CurrentSlider "
if i create a record in 'XYZ' worksheet, it should create the same record in TARGET worksheet.
Likewise, if i create a record in 'ABC' worksheet in the same workbook, it should create the same record in the SUBSEQUENT ROW of the TARGET worksheet.
For this to happen, it must be done programmitically either using a MACRO or VB Script.
I understand the end(x1Down), but i think I am missing something. I just want to go to the next cell left, right or Up, Down from whatever cell is currently selected. How do I replicate a 1 cell move as an arrow key would do?
I want to create a list from a "seed" word that consists of a word followed by a number (ex: seedword100).
What I want to do is to be able to create a list in Excel using that seed word and then have excel produce a bunch of replications of that seed word by incrementing the number part of the seed word by 1 for every replication.
So, for example, if cell A1 = "seedword100", I want Excel to replicate it up to "seedword199" like this...
B1 = seedword101
C1 = seedword102
D1 = seedword103
E1 = seedword104
F1 = seedword105
G1 = seedword106
H1 = seedword107 ...
and so on until the last cell = seedword199 (or whatever number I want it so stop at).
So I have a need to enter multi-line notes. Using a merged cell not working for me so thinking best to use a Textbox.
This works fine for entering the notes but I need to replicate the content on another Tab in the same Excel workbook.
So lets say I have named my Texboxes 'Textbox1' on one tab, and 'Textbox2' on another tab...... how do I replicate the contents?
I've tried putting =Textbox1 into 'Textbox2' like you would for a normal Cell but that does not work.
I created a UserForm to populate client billing info and job location info (so it's a two-parter). A clicked button will then populate a spreadsheet with all of the information. That part works fine.
When the client billing info is the same as their job info, I'd like to offer a checkbox that will populate the job info with the billing info rather than having to type everything twice (like you see on online billing and shipping orders).
I am trying to rebuilt a chartsheet without using copy/paste. I have chartsheets with 2 or more charts on it. I add a new chartsheet, then add a new chartobject and then I want to copy the same series ( name, xyvalues, values). Unfortunately it doesn't work.
Sub ReplicateCharts()
Dim Cht As Chart
Dim NewCht As Chart
Dim ChtObj, NewChtobj As ChartObject
Dim SrSerie As Series
Dim ns As Series
Dim x As Variant
If ActiveWorkbook.Charts.Count > 0 Then
For Each Cht In .Charts
Set NewCht = .Charts.Add()
NewCht.PageSetup.Orientation = Cht.PageSetup.Orientation
For Each ChtObj In Cht.ChartObjects...........................
Is there anyway in VBA to show the key sequence Alt+Shift+F10 then c?
Trying to find an easier way to convert text to number rather than having to use the Error Box method all the time as several large worksheets.
I have a single sheet broken into multiple sections of 10 rows (see attached). The first 5 rows of the section are formatted and I need to Insert 5 rows underneath and format them the same. I can't work out how to do this for the different sections (ie: add 5 rows to section 1, then section 2 then 3). These functions will be on separate buttons to use multiple times. I would have used multiple sheets but the project calls for a single sheet representation.
View 4 Replies View RelatedI've got this relatively simple file I use to calculate some values for a game mod. Its currently using ad-hoc excel functions, but its maths related to aerodynamics and they get unreadable quickly. I'd like to move to VBA functions for ease of maintenance, and programming the maths part of the code has been no issue so far.
However, I don't understand how to replicate the functionality of the default maths functions regarding range inputs.
Simple example, one of the intermediate values is the Aspect Ratio of the wing:
Code:
=ROUND(2 * Semi_Span / Mean_Aerodynamic_Chord, 3)
This works fine with both numeric inputs, cell references, or, as shown here, defined range names.
However, my VBA version:
Code:
Function AspectRatio(b_2 As Variant, mac As Variant, Optional round As Integer = 3) As Variant
AspectRatio = Application.round(2 * b_2 / mac, round)
End Function
Only works with single-cell references or numeric inputs.
When called as:
Code:
=AspectRatio(Semi_Span, Mean_Aerodynamic_Chord)
It fails.
From my limited understanding - I've only started looking into VBA about 2 hours ago - I need to do a IsNumeric test on the inputs in question, and then use the row from Application.Caller.Address to select the right cell if the input is a range? Am I on the right track?
How to craft a Sub I can call in the 15+ math functions I made.
Using Excel 2010.
The background to this question is that I'm trying to replicate conditional formatting using a user VBA function, because I require a thick border around the cells (and the conditional formatting within Excel only has thin borders)
In the final function, there will be 9 combinations of formatting {Red, Amber, Green} interior with {Red, Amber, Green} thick borders.
The formatting is determined by a number in another cell (the "target" cell), which returns a value 0,...,8
I've only got as far as filling in the interior for the first combination, but the function returns an error "Application-defined or object-defined error".
VB:
Function VBA000_003_SetRAG(strTargetRange As String) 'strTargetRange is the reference for the target cell that contains the value 0,...,8
On Error Goto handler [code]....
There is not a problem with the target range, if I remove the two lines relating to rngCaller then the function works OKExecuting the rngCaller.Interior.Color = RGB(255, 0, 0) command through the Immediates window works OKI've tried calling a subroutine & passing the range across
Same errorChanging the range in the subroutine to an absolute range (eg. Sheet1.range("A1")) also causes the
same errorExecuting the subroutine on its own (with the absolute range) works OK
So my guess is that it's a bug/limitation with Excel VBA when trying to execute commands from inside a user function
I have list of data with corresponding number of chances ( see attached column E) I need to replicate the data under column A,B,C & D to a new rows as per the number under column E and so goes till the end of the list using a formula.
Basically in the attached example i should have a 43 new data rows
how to setup the original RAG shapes in order to replicate the feature in other s'sheets.
If you drag away from one of the RAG shapes, it takes a copy of the shape but leaves the original shape intact. It seems to be locked in place, but there are no macros or VBA code doing it.
I'm looking for a formula or criteria within a formula to prevent a cell from recalculating or just causing it to revert to a value if another cell becomes populated. Something like =IF(NOT(ISBLANK(xx)),STOPCALC(xx),...
I'm trying to determine some weighted week to week projections and I want to be able to determine the accuracy of the projections without them being influenced by the actuals once they show up (the projections use historical actuals and related data which are all in the same range where the futures will come in). Also hoping for a shortcut to do what I described without having to manually go in and stop the (thousands of) cells from recalculating since the actuals for a given periodicity arrive staggered and not at one specific time.
I’m working on quite huge spreadsheets. The same formula will be used on more than 30 sheets.
So far if I have to change the formula in one cell I have to go to each sheet and change it manually (which is quite boring and time consuming).
I’m wondering if it’s possible to reference all the formulas on the sheet to one sheet (which some kind of a master sheet with all the formulas).
I can copy the formula with the “Past Special” function but the function is not updating if I change the formula on the main sheet.
So is it possible to link it and that it’ll be updated automatically when I’m changing the formula on the main sheet?
Is there any way to directly go to a formula? Instead of searching it through all the sheets? For example, if I have a formula =SUM(Sheet1!A1:A5) is there any way for me to automatically go to the source?
View 7 Replies View RelatedHow do I stop my sum formula from adding to it's range every time I type a number in? I have 12 columns of numbers by date and 4 quarter columns at the end totaling each of the 4 quarters. If I start typing directly across, the 1st quarter sum formula updates to include every number in the first 12 columns. The other quarter column sum totals stay the same.
View 7 Replies View RelatedMy issue is it automatically updates.
I have 4 collumns:
Location//S-1//CSM//SCO
I want to enter in the location in a drop down list, which I know how to do. The thing I'm trying to do is when the Location says To S-1 for example, the date that it is changed "To S-1" the collumn under S-1 will post the date. When location changes to CSM, it will put that day in that collumn, etc.
My issue is that if I use the NOW() or TODAY() function in an IF() then it will update evertime I reopen the file. So the dates won't stay fixed. So if I change the Location to S-1 on the 12th, open it again on the 13th... it will say the 13th not the 12th.
I have information been imported from another spread sheet into cell C1. but it will only display if i put data in to cell A1.
BUT, when i want to put data in A2, i want C2 to gather to formula info automatically.. I could drag it all down the screen but i dont want #N/A to be all over the page. Is there anyway i can get the formulas in the cells, without the #N/A all over it.
What can i do?
Would someone be so kind as to look at the attcahed sheet. There are boxes with data entered. Depending on the achievement the 3 parties are automatically ranked 1,2 or 3 as you can see.
All except for cells j23:j25, although I've used exactly the same formula as the other boxes, the ranking doesn't seem to work for some reason I cannoot fathom.
I know there is an issue with merging, but all the others work and are merged (to get them to work I created the formula before merging).
Currently in Column B for all the rows I have a basic math formula which adds the last three entries for that row and divides them by 3. Currently, I constantly am needing to readjust the formula to include the correct columns for each row every time a new column entry is added. I am looking to set it up so it automatically sets the formula to read the last 3 entries without having to manually adjust the formula to D+E+F= to E+F+G= ect... when a new entry is added.
View 1 Replies View RelatedIs there a way you can copy formulas to the next line when a new entry is automatically added to a spreadsheet via a form?
View 1 Replies View RelatedI am compiling a spreadsheet to determine various membership levels of Regular, Bronze, Silver, and Gold, which is based on the number of volunteer events attended at our organization (cell A1), as well as the level of giving (cell B1). Regular is the lowest level, and Gold is the highest.
Now let's say that I am looking for a formula that will be entered into cell C1. This formula would read cell A1 and cell B1, and then return the value that is the higher value. So, let's say the formula in cell A1 returned the value "Silver" and the cell B1 returned the value "Gold", ultimately, the formula in cell C1 would read these two cells, make the determination that the value "Gold" is the higher value, and return "Gold" in cell C1.
I am creating a sales per day/week worksheet for my reps to keep track of their sales. I have created the sheet that they only have to enter in what they have sold and it automatically calculates what they have sold per hour in the day, and what that makes their weekly total.
The problem is sometimes they will work 5 days, sometimes 4, sometimes 6 or 7.
im trying to fix the weekly average formula to divide by 7.5 for 1 day worked, 15 for 2, 22.5 for 3, etc all through 7...
this is the formula I have now, obviously it doesnt work
=IF(COUNT(B7:H7)=1,SUM(E42,E75,E110,E143,E175,E207,E238)/7.5,IF(COUNT(B7:H7)=2,SUM(E42,E75,E110,E143,E175,E207,E238)/15,IF(COUNT(B7:H7)=3,SUM(E42,E75,E110,E143,E175,E207,E238)/22.5,IF(COUNT(B7:H7)=4,SUM(E42,E75,E110,E143,E175,E207,E238)/30,IF(COUNT(B7:H7)=5,SUM(E42,E75,E110,E143,E175,E207,E238)/37.5,IF(COUNT(B7:H7)=6,SUM(E42,E75,E110,E143,E175,E207,E238)/45,IF(COUNT(B7:H7)=7,SUM(E42,E75,E110,E143,E175,E207,E238)/52.5)))))))
