Formula Won't Copy As Required
Apr 25, 2013I am trying to copy formula as follows:
2
3
1
10
=A4*B4*D2
=A4*B4*D3
[Code] ......
I'm trying to copy the formula both down and across but I'm guessing both issues are the same.
ADVERTISEMENT
Object Required (Error 424) When Trying To Set A New (copy) Worksheet
Aug 14, 2014I am copying a WorkSheet and I want to set it directly to an Object. I don't want to use the activeWorksheet. I does work like this with .add so my idea was it should work with copy too.
[Code] .....
It does Copy the Worksheet, but afterwards VBA gets error 424
SO how do I set a Workbook.copy ?
How To Copy Paste Into New Worksheet Only Required Cells Of Different Sheets
Mar 1, 2009I have this Excel File which has some sheets. All sheets are the same format like this: "I have read-circled the cells of interest"
I want to have a macro that can read the data from all the sheets (regardless of their name) and from the specific cells "red-circled" and copy and paste them into new sheet named "Report" in table format. Like this:
Sales Commission Formula Required
Jan 14, 2009I have a new sale structure to put in place the commission is paid in the following way:
below 1500 zero commission
between 1501 and 3000, commission at 16%
between 3001 and 8000, commission at 23%
above 8001, commission paid at 30%
Ergo if you generate 5000 you would be paid 700 ie nothing for the first 1500, 16% of the second 1500 and 23% of the remaining 2000. ( I hope my maths is correct! )
I have tried to manipulate other solutions using sumproduct but my knowledge is poor, the formula I have tried manipulating is =SUMPRODUCT( (A2 > {0,1500,3000,8000}) * (A2 - {0,1500,3000,8000}) * {0,0.16,0.23,0.3}). I prefer single line formula rather than lookups as staff will not be able to see commission rates easily.
Working Time Formula Required
Aug 28, 2009I have a fairly complicated working time calculation I need to make, the sheet calculates how much 'actual working time' is spent on a product. 'Actual working time' means just that, breaks & any other non working time removed from the total.
My sheet set up is as follows:
Column Z = Start Date
Column AA = Start Time
Column AB = Finish Date
Column AC = Finish Time
Column AD = Actual working Time (hh:mm)
Column AE = Planned stop time (hh:mm)
Standard working times are:
Monday to Thursday 07:30 to 16:15
Friday 0730 to 1200
First tea break is 10:00 until 10:10
Lunch is 12:30 to 13:00 (except friday)
Last tea break is 14:40 to 14:50
1) Jobs can be 'in production' for many days, sometimes weeks
2) Overtime may be run before or after shift, also weekends
3) We can have 'Planned stop time' which will also need subtracting
Formula Required To Return Limited Range Value?
Jun 12, 2014I'm looking for a formula to do the following:
I'm building a table (see attached) where I could have a value in cells D6 to P6 that ranges from 0 upwards. In the respective cell beneath each 'value' cell (D7 to P7) I need a formula that will return a figure of 10 if the value in the cell above is 0.4 or below reducing to 0 if the value in the cell above is 1.0 or above.
Formula To Pickup Required Data From Table?
Aug 16, 2014Need formula to pic required data from Validation,
In the excel sheet Orange color (Data Validation) , If i select the data validation(Like, Jan-Orange- consumption) and corresponding data should display, so i required formula for that.
Formula To Find Required Character In Cells?
Dec 26, 2011I have following table,
Item NoSectorDestination
12346589BOM-DXBN/A
12346589DXB-FRAFRA
12346589FRA-DXBN/A
12346589DXB-BOMN/A
87665976NAG-BOMN/A
87665976BOM-DXBN/A
87665976DXB-PARPAR
87665976PAR-DXBN/A
87665976DXB-BOMN/A
87665976BOM-NAGN/A
Row 2 to 5 are of same item no. The result that is required is in Column C. The formula to check same item numbers in column A and give the result as "FRA" in column C3 with C2, C4, C5 showing as not applicable. Similarly for the next item no
Formula Required To Pull Some Info From Cell
Apr 15, 2008i have a list of names which also contain e mails addresses after the names, they are displayed as (In column B onmy spreedsheet)
Armani Stevens/GB/companyname/GB@soso
what i need from the above is just the name (up tp the first backslash)
so i would need Armani Stevens and disregard the rest.
What i would then need to do is to take the name and then see if the names is in the list which is situated in column A,
so to sum
once i have Armani Stevens extracted from Coulmn B i would want to see if this name is in Column A
Names are obviously of all different lengths and there is always a space after the first name and surname
Formula Required- Football Predictions Spreadsheet
Feb 12, 2009A pal at work runs a football predictions spreadsheet but due to a recent surge in numbers, it is taking longer to adminster each week.
Basically, you get 3pts for a spot on home score, 4pts for a spot on draw, 5pts for a spot on away win and only 1pt if you get a correct result but not the actual scoreline, with no points being scored for being completely wrong.
At the moment, he manually inputs 0,1,3,4 or 5 pts each Monday, of course this is open to human error. Is there a way of getting excel to calculate this for him once he inputs the correct score in the first column?
AZ Or ZA Formula To Replace Macro Method Required.
Apr 18, 2006The example Spreadsheet shows the current method of Ascending or Descending
Data according to Macro. Is there a way to replace the current Ascending or Descending macro code with a formula for the required cell groups only?
Macro To Find Required Data Than Copy/paste In Specific Cell Address
Nov 4, 2009I want to copy some data from internet and paste it in to worksheet location Range A1:A30. the data look like following information.
LocationKuwaitP.O.Box:4819 SafatAddress:ShuwaikhTel:-22253580Fax:-24343645Category:CONSTRUCTION COMPANIES
the above information have following header ie. Location, P.O.Box, Address, Tel, Fax and Category.
every time one of the header inforamtion is missing ie. Address or P.O.Box, Or Fax, due to this problem every time cell address of information aganist header is change. supose in first copy/paste Tel inforamtion is in A15, but second time it may be on A13, and 3rd time it may be on A17 and so on.
I want to make a macro which search header ("Location", "P.O.Box", "Address", "Tel", "Fax", "Category" ) from a Range A1:A30. than down two steps to copy header information (75325412) and paste in fixed cell location C1:C5 (C1=Name, C2=Location, C3: P.O.Box, C4=Address, C5=Tel, C6=Fax, C7=Category) in same sheet. and repeat the steps to copy information aganist header from all header in Range A1:A30.
Formula Required To Calculate Number Of Days Remaining
Apr 1, 2009A1, B1, C1 and D4 are cell in Excel worksheet and values are 2, 4 and 6 in A1, b1 and c1 respectively.
A1 B1 C1 D4
2 4 6 C1-Formula is: =sum(A1+B1)
Now I have to write formula for D4-cell:
i.e. =sum(C1/Total days remaining of the month)
Suppose Today is April 01, 2009, C1/should be devided by 30,
Tomorrow will be April 02, 2009, C1/should be devided by 29.
Formula To Calculate Number Of Hours Between Now And Date Required
Sep 24, 2012i am trying to work with a formula that will look at date today (NOW) and compare this to a due date and in return provide me with only the working hours total. Working hours are 8am to 4pm (8 HOURS).If the due date is passed this will be a negative figure.
View 9 Replies View RelatedFormula To Determine Required Values To Achieve Target Percentage?
Aug 24, 2014Data is;
83300 - hypothetical number of times I have fired my gun at target.
43209 - hypothetical number of times I have scored bullseye.
So, my bullseye percentage = 43209/83300, or ~51.87%.
Need formula to determine how many more consecutive bullseyes I need to shoot, in order to achieve 70% ratio.
Since each shot from now on will be a bullseye, both values (hits & shots) will increment together.
Formula Required To Calculate Points For Football Results Prediction
Aug 2, 2009I'm creating an (English) football predictions competition for me and my family.
One problem that has stumped me is how to get the scores based on the 'home' & 'away' score predictions.
The rules are: If I predict the correct exact result I get 3 points. I want to add another 'rule' whereby if I predict the correct winner, I get 1 point. Incorrect predictions get 0 points. I don't know how to do this using a formula.
Formula Required To Take First Letter In 1 Cells And Add Random Number To Create Unique ID
Mar 27, 2014I have 1200 doctor/patient records to input into an excel spreadsheet for import to an online EHR database. I can set up all the normal formulas and formatting but for the life of me not figure out how to create a custom formula to take the first letter of the patient first name and last name and add 6 figures to create a unique patient identifier.
ie. James + Smith+ random 6 figures = JS245318.
In my spreadsheet the first name is under Column 1, Last name Column 3 and the unique number generated in column 4.
Modify The Formula Entries In The Following Code To Cycle Or Loop Through All Checks Required
Feb 22, 2009I'm working on a spreadsheet to compile and print checks. All the check information except the date and first check number is contained in a Wins sheet. I also have a Checks sheet which contains a master check. I used text boxes on the master check to contain individual check information. Each text box is filled in from the Wins sheet by means of formulas. The problem I'm having is how to modify the formula entries in the following code to cycle or loop through all checks required. A sample spreadsheet is attached.
View 2 Replies View RelatedExcel 2013 :: Formula Required To Remove Unknown Character And Extra Space From Name
Aug 19, 2014i am trying to remove the unknown character and extra space from the name. Though i use formula as trim or proper(trim), it is not removing the Unknown character / extra space. I have attached the few name as sample. Formula to remove these Unknown character / extra space, double space, special character from selected cell?
Note : I am using ms office 2013
Conditional Formula Assistance (default To "training Required" Rather Than The Value It Would Give)
Oct 28, 2009=ROUND(SUMIF($C$26:$C$106,D126, $F$26:$F$106)/COUNTIF($C$26:$C$106, D126), 0). the formula is from a sheet which is used as like a questioning/ skills matrix.
The reference of d126 is a category eg: complaint handling and there might be 4 questions on complaint handling each of the answers being displayed as a number between the cells f26-106. If any of these individual cell results are equal to 0 or 1 then i want the overall answer for the formula to default to "training required" rather than the value it would give.
Index And / Or Match Formula With Multiple Match Required To Return A Value
Jan 21, 2014See attached file, "Rate Sample Index-Match Formula".
I need a formula to return the value at the cross section of two (2) lookup values that match. This formula will be input into column D under, "RATE" on the 1st tab, "TEST FILE".
In the 1st tab, "TEST FILE" there are a series of columns as follows;
A = Service
B = From
C = To
D = Rate
In the 2nd tab, "RATES" there is a series of rates with drivers From (green) & To (blue)
The formula needs to do the following;
1. Lookup the "From" value in column B on tab, "TEST FILE" and match to column B2:B59 on tab, "RATES" both highlighted in green
2. Then Lookup the "To" value in column C on tab, "TEST FILE" and match to row C1:BH1 on tab, "RATES" both highlighted in blue
3. Then return the value at the cross section of the match "From" (point 1 above) & "To" (point 2 above) in range C2:BH59
For Example;
The rate From SYD To CBR = 0.33. I have highlighted this in yellow on both tabs to show where the formula needs to lookup the data to return the answer.
Additionally, if we were to add service as an additional lookup match how would this work?
Copy Pairs From 2 Columns To Formula Reference Cells & Copy Updated Formula Results
Jun 24, 2008I am currently working on a data analysis project (data mining) and need to collect and later analyze statistics for the inputs which control a series of calculations. These statistics are shown in the Statistics 1, Statistics 2 and Statistics 3 cells in the workbook that I attached. The inputs are X,Y; all possible values for these inputs are listed in the N,O columns. Basically I need a macro which would take the values from these two columns and place them pair after pair into the controlling cells (K3, L3), then it would copy cells H2 through L3 (updated stats) to a new sheet after each copy operation - so that I will finally have a list of statistics for all of the input pairs.
View 3 Replies View RelatedAlter Existing Formula To Copy Specific Cells In Row Instead Of Copy Entire Column?
May 1, 2014I need the macro to look at cells B9:B84 on the Sheet1 tab of the Cost Template. If it finds an x I need it to copy the 3 cells to the right of the x and paste them in a template. For example if it sees an x in cell B9 it would copy cells C9, D9 and E9, open the Purchase Order to the Detail tab, then paste it to cells B3, C3 and D3. It would continue looking for an x down to B84. So if it found 5 cells with x, it would give me 5 instances of the Purchase Order with 3 cells pasted into each.
I've attached my Cost Template and the Purchase Order it needs to copy to. In the Cost Template is a macro called Create_PO. This is what I was trying to alter to make this happen. I can't seem to get it right! FYI in case it matters, I had to change the Cost Template from .xltm to .xlsm in order to upload it on this site.
Copy Down Formula Macro Without Knowing How Many Rows To Copy Down
Feb 18, 2008I have a macro that inserts a formula on the first row and then copies it down. The problem is the row count is variable but I know it won't exceed 5000 so I just copy it down 5000. For the blank rows I get the ugly #NAME, #VALUE, etc errors.
I know there are ways to supress those error messages, but how about a way so that the macro checks to see how many rows are populated so it doesn't copy down on the blank rows.
Copy A Formula In A Cell And Then Paste Only The Text Of The Formula
Jul 16, 2007I would like to copy a formula in a cell and then paste only the text of the formula, but I can't figure it out. Basically, I would like to avoid going into the cells and absolute referencing or hitting F2, then copying the text.
When I hit "Ctrl C" to copy the cell, then hit "Alt/E/S/F/Enter" to paste the formula, it is just like a regular copy/paste formula-wise in that the references move.
Listing Required
Jun 17, 2009I have been given a worksheet which has 5000 rows spread along 13 columns.
There are about 200 cells that I am interested in from this sheet, these cells being the subtotals of the cells above them.
I have extracted the individual cells containing the subtotal values to a new, empty column but they are spread down this column with blank cells in between.
Is there a function / formula to get rid of the 4800 blank cells so that the data I need is compressed into a stack of 200 cells all filled with data?
Object Required
Sep 27, 2007i am having alittle trouble with this line of coding.
With ActiveWorkbook
.Sheets("Hidden Data").Cells(rngFound.Row, 9) = Sheets("Data").Range("L18")
.Sheets("Hidden Data").Cells(rngFound.Row, 12) = Sheets("Data").Range("l20")
.Sheets("Hidden Data").Cells(rngFound.Row, 15) = Sheets("Data").Range("l22")
.Sheets("Hidden Data").Cells(rngFound.Row, 18) = Sheets("Data").Range("l24")
.Sheets("Hidden Data").Cells(rngFound.Row, 21) = Sheets("Data").Range("l26")
.Sheets("Hidden Data").Cells(rngFound.Row, 24) = Sheets("Data").Range("l28")
End With
with the line "Sheets("Hidden Data").cells(rngfound.row, 9) = sheets("data").range("l18")" the error messages states Object Required, but i am not sure what this is trying to say.
any idea's or thoughts would be greatly appreciated.
at the top of the coding i have listed "Public rngFound"
Only Print What Is Required
Nov 13, 2009I am creating a training matrix that will be used in a number of locations. Some only have a few staff members whilst others have lots. I want each location to print a list on a formatted sheet but I want to set it up so that the locations with fewer people don't end up with lots of blank lines.
View 9 Replies View RelatedSet Objects To Nothing. Required Or Not
Dec 21, 2007Why should I set objects to nothing (ie clear them) just before ending a sub routine? Doesn't excel dump the variables after the sub is finished running?
Example:
Dim ws As worksheet
Then at the end...
Set ws = Nothing
VLookup :: Last Row And Get The Required Numeric Value
Dec 28, 2009I have two columns E, F in excel. Rows are dynamic. In the sense that sometimes only 2 rows appear or sometimes newer rows appear......
Now I want to lookup the last row and get the required numeric value. I have written the following formula to get the result. =VLOOKUP(CONCATENATE("E",SUM(COUNTA(E2:E16),1)),E2:F16,2). It returns value 60. But when I add 2 new rows.
7 F 70
8 J 90
It should return 90. But it is still showing up 60. when i checked CONCATENATE("E",SUM(COUNTA(E2:E16),1)). it is returning 'E8' correctly. But VLOOKUP unable to return correct value.