I want a range to be filled based on 2 cell values in my sheet. The autofill range should be filled based on the values in the input range. For instance in cell A1 I place the value 1 and in cell A2 I place the value 10, then the macro should automatically fill in the numbers from 1 to 10 in another column. If I change for instance the input values to 5 and 15, then the autofill range should fill up the numbers from 5 to 15.
I have a workbook which has one sheet "Raw Data" where I will cut and past blocks of data of various numbers of rows. Another sheet "level one calcs" conducts various calculations on the raw data. I was wondering if there would be any way to create a macro which would copy down (autofill) the last row of my my calculation page for the exact number of rows I added into the raw data. The goal here would be so that I dont have to highlight and copy down the formulas on the calc sheet each time I add in new data.
I have an Excel file for work that has the following:
Column 1 is Agent Column 2 is Interval (in 15 minute intervals from a report that I pulled) Column 3 is Agent Calls (has a number anywhere from 1 - the highest currently is 19, which tells me how many calls that Agent had on that 15 minute interval.)
What I have currently is that if it says:
John Doe 15:00 5
It will automatically insert rows after the number based on a # -1, so it will add 4 empty rows after this row.
I have another step that will automatically copy Column 1 and Column 2 to the blank rows directly beneath them until it reaches a cell with data.
Now, what I need is a macro that will take Column C and where it says 5 automatically know to change that to '5a' then proceed to go down the list with 5b, 5c, 5d, & 5e. If it sees 4 it will know to change that to '4a' then proceed to go down the list with 4b, 4c, & 4d. I currently have numbers ranging from 2 - 19.
I have built custom list with this information so if I change all the numbers from 4 to 4a or 19 to 19a and double click on the black box it will autofill exactly like I want but it only goes down to the next cell with data, and I have 100+ lines per agent, and up to 21 agents a day, so this gets time consuming.
Here's how it looks when I'm ready to start the macro:
how best to do this (bare with me I'm not quite a pro at excel yet!). I'll attach my doc (JMP Excel Forum) so you know what I'm taking about. I've set up the data to work in a similar way to this, as you can see I've a table set up (Appliance Sheet) which feeds information to the Main Sheet which contains the drop down lists.
What I need it to do is autofill the wattage column (Main!F8 onwards) with the wattage for the device as described in Appliance!N3 or N4 for example. For the wattage column to equal 28 (equivalent to Appliance!N3) then the following would show in the dropdown selections Type>Computers SubCategory>Monitors Device>BENQ G2222HDL Status>On
Is there a way to do this with the set up I have?
I did have a previous version of the whole worksheet that each Device had its own table and I looked up the wattage using and If formula to lookup a info on the device and knowing where to look it up based on the subcategory
And I got it to work but it was getting to cumbersome to add in new devices and update the formulas. If attached that doc (OLD) so you know what I'm talking about. The last two sheets are the same sheets that make up the new document (JMP Excel Forum)OLD.xlsm
I really prefer the new way I've got it set up. Its easier to add new information or options to the dropdown list
I'm trying to set it up so that if a particular cell has any data in it, the contents of a different cell get sent to another. I want the no data/data to serve as a trigger.
As an example, if I put anything into cell A3, I want the contents of cell A1 to copy to H1. I'd also like it to be dynamic so that if I put something into cell B3, the contents of B1 get copied to H1, etc.
I had an report and the rows of the report needs to be colored based on another cell value. My layout looks like this:
A1B1C1D1AVG 1011121330 14151617100 18192021400
For each cell, i need to compare the value of cell with that value in AVG coloumn and if condition is met, cell should be colored. Eg : If first cell value under A1 is Less than 0.2 * AVG of first row then color of the cell A2 should be red. After coloring the entire rows, then i should check and color the header row based on the color of Rows.
I tried first one, i,e coloring the rows based on conditional formatting and then written VB code to compare the color of rows and colored the header row. But the color index used in conditional formatting and the code are mismatching and the desired output is not coming.So decided to write code for coloring the rows cell.
I have so data I am trying to combine based on count value of investment on Col A. If it appears more than once on col A then I want the col B values on col D. I have macro that works but I need make sure if Col B have any TRAC and TRL1 then I want to add that on Col D. For example so Col D1 would be EURX, TRL1 at 83.50, FTID and Col D5 would be TRAC at 87.88, Markit. Below is the macro.
I've schedule header date 1-oct,2-oct,3-oct.....etc and have two cells Last Date and Hours I need from those cells once i add date and hours to copy the hours and paste in schedule header in the exact date..
for example # Last date Last Hours
but was too slow takes long time.
2nd VBA code to copy last hours to schedule date based on condition on last date cell.
I have 8 columns of data, the first 4 and the last four columns have the values which correspond to each other.. Here is an example:
VALUES(A to D)NAMES(A to D) ABCDABCD 1.3451641.3383581.286221.359808321325370391
So value 1.345164 corresponds to 321 and 1.28622 to 370. I need a function that will return a value from one of the VALUES columns which corresponds to the second smallest cell value from the NAMES columns. For example – here the second smallest number in NAMES column is 325 so the function would return the corresponding value of 1.338358.
i m trying to format a cell so that it colours in red when a specific text is chosen in another cell, ive tried conditional formatting but I cannot get it to work. e.g column A has a drop down list, if GB or IEN is chosen in that cell then cell in column K will become red. (as if these two are chosen then they have to input a number into column K). Once they have inputted the number into column K i want it to go back to its original format. Or if they dont select GB or IEN then it would stay in its original format aswell.
I need to force our salespeople to enter information in a cell based on a condition. This is the condition: If I5=3, then I need to move the user to cell I3 with a message that says "Enter miles to site" -- I don't want them to be able to move from the cell until they enter the information. If I5 does not equal 3, then I don't need anything to occur. I need to apply this code to four separate worksheets in the same workbook. Also, they will be entering a number in cell I3, so is okay to leave I3 formatted for Number or General Number? Note: This question is also posted at the following link, but has not yet been answered by that group: http://database.ittoolbox.com/groups...dition-2270643.
I am working on creating a simple Excel Database of an Engineering Tool Room Inventory for work. However I can not seem to get one particular 'NESTED IF Function' to do what I want it to do. (In advance warning, I may be going down the wrong route and might have used the wrong Formula in Excel but from my understanding the 'IF' function seemed the most plausible.
Issue: All I would like to do is for a NESTED IF Function to be able to say this:
1. When a user inputs data into one column, for example 'Column A', with the following data: 4" Square 2. Then the neighbouring column, for example 'Column B', would have a NESTED IF Function. 3. Which would output a logical response, "A, B, C, D, E, F etc...", depending on what is written in Column A. If 'Column A' - 4" Square, Then 'Column B' - A If 'Column A' - 5" Square, Then 'Column B' - B & So on... But the NESTED IF Function should only put a logical response if there is certain values within the cell, for example: User types a value into 'Column A' which contain - 4 & S, Then 'Column B' - A User types a value into 'Column B' which contain - 5 & S, Then 'Column B' - B & So on...
Currently to do the above function, I have utilised the following Excel NESTED IF Function:
& The following happens: 1. User inputs data into 'Column A' 2. The NESTED IF Function then provides an automatic response. 3. However it relies on the data being inputted without the ' " (Quotation Symbol)' 4. & With me defining the exact phrase that should be typed into the cell, for example, "4" Square"
Other: I have attached an example worksheet of the NESTED IF Function in practice, with two tables. One showing what is happening with the code above and one table showing what I would like to happen but with no function.
I am trying to write a formula to figure out Body Mass Indexes for certain age groups and whether or not they fall into a High or Low risk category. So, I am trying to write a formula that does the following. I have 3 columns, Gender, Age and BMI. I need the formula to do the following.
IF Gender = M AND Age >18, <39 AND BMI >7%, <19%, Then return an "L" into 4th column IF Gender = M AND Age >18, <39 AND BMI >19%, Then return an "H" into 4th column IF Gender = M AND Age >40, <59 AND BMI >14%, <23%, Then return an "L" into 4th column..............................
I am trying to perform a calculation in a cell that leaves the value 0 if before Feb 2014, changes to the value of another cell in Feb 2014 and leaves the value unchanged if after Feb 2014. I can get to the Feb 2014 value but after that month the value changes to 0.
The Cell formula that I am trying to use is as follows:
I am trying to write some code that keeps throwing me the error message "Autofill method of range class failed". But I cant understand why, I think it may have something to with my range notation:
Code: Sub SummariseSheets() 'collates individual client development data from consultant 'worksheets and compiles in one sheet on summary page
The error is in the "Selection.AutoFill Destination:=("C" & LR & ":C" & LR2), Type:=xlFillDefault" line, however I cant figure out why? The code essentially takes a section of data from multiple worksheets, then pastes it onto the summary page. It then adds the name of the sheet the data has come from into column C next to the relevant rows.
i'm trying to do is have a formula that when certain text is typed into column 'b' (fmc, nmcmc, etc... anyways..) when an airplane is 'fmc' its 'green' and i want the aircrafts tail number block in column 'a' to autofill in with a green color, and when its 'nmcmc, i want it to be red. when its 'bq' i want it to autofill in as yellow.
Above is a table we have for high level tracking on specific tasks due for projects.
What I want to do is: If the last cell in a column = Jan then it will automatically copy the whole column and insert it into a Sheet named January and have it automatically update. Below is the end result.
Here is my test example I am trying to figure this out with.
Sub a() Dim rg As Range 'set formulas to be filled Range("A1").Select ActiveCell.FormulaR1C1 = _ "=IF('sheet2'!RC="""","""",'sheet2'!RC)" Selection.AutoFill Destination:=Range("A1:L1"), Type:=xlFillDefault 'now get autofill to populate until no more data on sheet 2 Set rg = [a2] Set rg = Range(rg, Cells(Rows.Count, rg.Column).End(xlUp)) rg.Cells(2, 2).AutoFill Destination:=rg, Type:=xlFillDefault End Sub
Without VBA, once I set the formulas for A1:L1, I can use the mouse to autofill all columns until Im around row 27,000.
Id like to be able to run a check for the end of data in sheet 2 so that if the sheet2 changes, it will all be included.
The resulting sheet1 cannot have any null values, so a perfect end of data check is necessary.