How To Fill In IF Function Across Columns Changing Column Ref Only
Jan 16, 2014
My formula in sheet "A" in cell D7 is this
=IF(Activity_Log!C2="BH",Activity_Log!F2,"").
What I would like to do with it is fill in row D7 to AH7 with this formula without changing the row reference in the IF function, only the column reference.
On the Activity_Log worksheet, my data is listed from cell C2:C32, which is selected from a drop down list, and F2:F32, which contains the formula =(E2-D2)*24.
I've attached a sample : Sample_MonthlyReport.xlsx
View 9 Replies
ADVERTISEMENT
Dec 18, 2013
When I hit the macro code you see below I get both columns D and E, starting from row 18, to get 'filled down' to the specified spot. Every second row has a conditional format (when a value is entered in column A) to change the row to the color grey, and every row between it has a conditional format (when any value is entered in column A as well) to have the row changed to the color red. The issue here is that the Macro code messes up the conditional formatting and uses the conditional formatting of those two cells, which are being dragged down, for those entire filled-down columns! This is what I am starting off with test1.xlsm and this is what I end up with using the macro code below (or doing it manually)
test2.xlsm
Is there a way for the Macro code to bypass this issue?
View 6 Replies
View Related
Jun 24, 2009
I would like to automatically color fill a series of rows based on like information from a single column. When the information in the column changes then the rows would either stop filling (this would be best) or fill with a different color until the information changes again.
Summary: series of alternating rows would either be filled or unfilled based on changes from the column information.
Hope this isn't too confusing. Below is an example except I would want the cells filled, not the text or numbers to change. The highlight is changing based on the changing of the numbers.
ABCD 12345
ABD 12345
ABCD 12349
ABDF 12349
ABCD 12358
ABF 12358
View 9 Replies
View Related
Jul 13, 2014
When I hide columns in a column chart the different fill colors I used on specific columns no longer show up with the colors I originally had. Is there a way to maintain the proper sequence of column fill colors even when some columns are hidden?
View 1 Replies
View Related
Feb 19, 2014
I have a control chart macro which is being used for patient doses of a medication. Right now my macro automatically fills columns D-N down to row 100. However, I would like it to stop at the last row of data entered in column A. I have attached the code related to the formulae a
J
Sub Control_Chart_1()
'
' Control_Chart_1 Macro
[Code]....
View 1 Replies
View Related
Mar 4, 2014
I have 1 sheet called Setup which users enter their character name, their class, and their race, additionally the entire workbook manages data for up to 9 individual characters.
On another sheet called C|R|E, I have tasks listed that are based on each class type and each race type that are available. Currently there are nine classes and four races. Currently on this sheet each class and each race has its own individual data lists and such, and formulas are done in a way that user inputted data can be entered anywhere, but wont count unless the class and race match those selected on the Setup sheet. To keep the sheet short in appearance I used command buttons to hide/unhide classes or races not in use by the user.
What I was wondering is (and this could be hard to describe), can data in one column be changed to reflect data based on another column's focus? I will explain in more detail.
Column B contains the task information for each Class and Race. For example Cells B13-B51 for one class, Burglar. Columns D-L is where users enter data to show if the task is completed or not. In this example lets say the users first character listed on the Setup sheet is a Champion. What I want to do is check the Setup sheet Char 1. Then based on what Char 1 is selected as on the Setup Sheet, C|R|E B13-B51 data is changed to reflect it, IF Column D currently has focus. If Column E gets focus then B13-B51 changes based on the class selected on the setup sheet for character 2. and etc.
If this can be done I can remove the 13 different sections and have it down to 2 sections, one for class, one for race. For now I am using check boxes to hide classes and races they are not currently using, but would like to move all classes into one table and all races into one table to they dont have trouble with columns they have to skip over currently.
This is a sample of how I am currently working on the C|R|E sheet, the full code is much much longer since I am using so many command buttons.
View 1 Replies
View Related
Sep 25, 2013
I have a worksheet with data in columns F,G,H and I. There is a total in column J. (=SUM(F1:I1)
I've written some code that allows a user to insert additional columns. However, I'm finding it difficult to find a way of changing the calculation in the total column automatically when a new column is inserted.
View 1 Replies
View Related
Dec 31, 2013
I am trying to create a sheet on docs.google.com that takes the sum of 1 row divided by the sum of another row removing numbers from the denominator if fields are left blank. Hard for me to explain so here is an example:
Counter
Column 2
Column 3
[Code]....
Leaving a blank value in column 2 makes it read 4/6 giving me 66%, Column 3 giving me 50%.
What I would like it to do is if there is a blank value in any column remove column one from the denominator.
Upon completion the total output percentage for column 2 should be 100%, and column 3 should be 75%.
View 3 Replies
View Related
Feb 1, 2010
Is there some easy way to fill out column B referencing column A, but only incrementing by 1 row in A every 2 cells in B?
Example:
Column A:
A1 = 1
A2 = 2
A3 = 3
etc...
Column B (I would like to fill this, referencing column A):
B1 = A1
B2 empty
B3 = A2
B4 empty
B5 = A3
etc
View 4 Replies
View Related
Jun 23, 2014
I have no knowledge of VB programming and the conditional formatting seems limited. I want to change to the color of cells to yellow if the cell value is greater than 80 but less than 99, and if it is 100 or greater to fill red. Is this possible and if so can it be applied to a range of cells?
View 3 Replies
View Related
Nov 24, 2008
How can I change the fill in the ChangingCell to, say, ColorIndex = 15, if the goal seek method is successful
Currently I've in a loop:
Cells(myrow, colSet).GoalSeek Goal:=Cells(myrow, colVal), _
ChangingCell:=Cells(myrow2, colChange)
View 9 Replies
View Related
Jun 4, 2014
I am trying to copy a countif formula down a column using the fill handle, but when I copy the formatting down the column the range changes. Is there a way to make it so that only the variable changes and the range stays the same?
View 2 Replies
View Related
Mar 3, 2012
Is this possible? If
A B C
0 0 0
0 0 0
1 4 5
2 4 8
0 0 0
0 0 0
0 0 0
2 6 9
8 9 3
3 5 6
0 0 0
In the raw data, the 0s split up the data into different steps. I am trying to create a function that selects the data from the ABC column and puts it into new columns for each step. I.e:
New Columns with:
1 4 5
2 4 8
and
2 6 9
8 9 3
3 5 6
In the raw data files, there will be 10 steps, with a varying length of data.
View 2 Replies
View Related
May 29, 2014
I need to fill 2 other cells with varying colours dependant on what is chosen within another cell.
I have a drop-down list in cell A2 to select either ON SITE, ACTIVE or CANCEL.
What I need then basically is...
if A2 equals 'ON SITE' then fill other two cells (A3 and B1) GREEN
if A2 equals 'ACTIVE' then fill other two cells (A3 and B1) YELLOW
if A2 equals 'CANCEL' then fill other two cells (A3 and B1) RED
[File attached]
View 8 Replies
View Related
Jun 20, 2014
I have a calendar set up with dates going along P6:CU6. Column C9:C27 has various status' for the job such as "Complete" or "In Progress" etc. Next to that in rows D and E I have start and end dates for the job. What I am trying to do is change the fill colour of the empty cells that are in line with each job, so that between the correct dates they are the corresponding colour to the status. So a complete job on row 9 that started on 26/05/14 and finished on 28/05/14 would have three green cells along Q9, R9, and S9.
I have tried an IF/AND/OR statement which looks like this:
=IF(AND(Q6>=D9,Q6<=E9),OR(IF(C9="Planned","P",""),IF(C9="In Progress","IP",""),IF(C9="Late","L",""),IF(C9="Complete","C","")),"")
I am getting a #VALUE! data type error, I have tried reformatting the cells to various date formats but no luck so far. This could be because my dates are set up as =P6+1 etc. but I am not sure. The error only occurs when the cell falls within the dates, otherwise it remains blank.
I intend to hide the value in the cell and use conditional formatting to change the cell colour based on the hidden input.
Once this is working I also need to take weekends into account, column G has any 2 value combination of Y and N to say whether the employee is working Saturday, Sunday or both. Row 7 has a "1" value underneath every Saturday and a "2" underneath every Sunday. I can imagine how this would work but combining it with the above formula could be complicated.
View 1 Replies
View Related
Apr 17, 2007
Let's say I have column C blank, but every time it's filled in with something I want column A to be today's date and B to be current time. Unfortunately, the functions NOW() and TODAY() don't give me what I want. They get recalculated every time that I update the spreadsheet.
Another thing that I can do is just press ctrl+shift+; to generate a non changing date/time, but I want it to happen automatically (being super lazy).
View 9 Replies
View Related
Jun 10, 2006
how I can change the color of an autoshape based on a value in a cell?
View 4 Replies
View Related
Mar 3, 2014
It's quite easy, I am trying to do a droplist (that's easy) but I want that drop list to take infos on 2 different columns and fill the 2 columns I want in the main sheet (see attachment)
On tab 'dealers' I have dealers name and account numbers. On tab 'Mileage' I have a cell call "NAME' and a cell call 'Accnt #/NB'. Here is what I want to do:
On sheet 'Mileage' I want to choose from a drop list a dealer name and I want Excel to populate the cell "Accnt#/NB" automatically by chosing the account number that is appropriate to the dealer choosen which is column 'B' in the dealers tab.
View 2 Replies
View Related
Apr 30, 2014
I have a spreadsheet with 10 columns which are for tags for products. Some columns are filled in for 10 products and some are not. For instance, product 1 has 7 tags while product 2 has only 2 tags and so on. Now what I am looking to do is combine all the tags for each product with a "," inbetween each one. The problem is if I just use a simple =A2&","&B2&","&C2 etc formula is that if there are only 2 tags then I get 8 ","s. I also tried it with an IF statement but by the time I was done nesting items I became cross eyed and said there has to be an easier way to do this.
I have create a spreadsheet with the data and the result that I typed into the "result" column.
View 3 Replies
View Related
Oct 13, 2009
On sheet 1 I have a list of 1000 firstnames
On sheet 2 I have a list of 1000 emails,
I need a function that states If a cell in the email column contains a string or value from the names column, it will result in a true statement so that I can separate out the emails that have these peoples first names.
View 5 Replies
View Related
Apr 25, 2009
I am trying to create a table showing the yearly average occupancy rates per quarter for every year between may 1998 and december 2008 inclusive (for licensed hotels, motels and guest houses, and serviced apartments in Australia; if that interests you).
I have a spreadsheet with a row of data showing the appropriate quarterly results for those dates. Let's say the data for this is displayed A1 through to A44.
For the yearly per quarter averages, let's say they are to go from B1 through to B11, I know I could put, for each year, an average formula with the relevant data range. Finding this to be tedious, I tried to fill-down.
I learnt the hard way however (not being too aware of how excel's grammar ticks) that the fill down function will not work for me. i.e., if B1 contained the average for the data range A1:A4 (the four quarters of 1998), the filled-in B2 would have the data range A2:A5 (i.e. the 2nd quarter of 1998 through to the 3rd quarter of 1999).
View 2 Replies
View Related
Jan 10, 2009
In the worksheet just say:Cell A1: Jan-11-2009 as today date. Cell A2: What is a date function I need to put in there so when a computer clock change to 12:01am it automatically fill in a next day it should be Jan-12-2009 and continues do so for the next day at A3 ....when computer clock change again. Remember I wanted a cells A2...A3...A2000 are blank until the computer clock change to 12:01am.
View 5 Replies
View Related
Jan 26, 2007
I have a function =TimeSinceIns(3) in cell K3. Where the 3 represents the row number. I would like to fill the function all the way down to cell k100. And be able to do this reguarly. Unfortunately when I use the fill handle it leaves 3 as 3 when I want it to be changing to the appropriate row no. Ie in cell k4 would be =TimeSinceIns(4) and so on.
View 3 Replies
View Related
Jan 31, 2013
I have a large spreadsheet converted from pdf whose data still appears in A4 reading format.
I need to move part columns of data from 6 columns to form 1 large column in column A.
For example, move range B8 to B76 beneath range A8 to A76 and range C8 to C76 beneath that etc, page by page working through all 270 pages !
Also need to delete unnecessary 'page headers' throughout as in rows 2-6
View 2 Replies
View Related
Dec 27, 2012
Weight
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
[Code]...
Monday Input Weight: 23
Monday Input number: 5
I'd like to autofill the above table from the "Input" numbers and weights I would enter elsewhere.
Previously, I would do that like this:
=IF(Y$10=0; ""; IF(Y14
View 6 Replies
View Related
Mar 21, 2009
Is it possible in anyway to make a formula that changes the colour and boldness of a number depending on whether it is bigger than 50 or 100?
eg. if i enter all my numbers as dark grey. then i enter say, 65, i want it to automatically change to black and bold. if i enter 105, i want it to automatically change to red and bold without me having to do it manually?
View 4 Replies
View Related
Nov 19, 2008
I have a calendar I have developed that highlights the current date and opens by default to the current month. Each worksheet is a seperate month. The current date highlighting is written with a public macro and the opening to current month is a private macro. The problem comes when this workbook is shared and the other users have to agree to accept my macro because of security. I would like to bypass that requirement and just have the workbook stand alone without the permission.
View 5 Replies
View Related
Mar 29, 2007
the function I'm referring to is when you have a value in a cell and you hover your mouse cursor over the edge and get the black cross with arrows on all corners. Once you drag the cell into another location, it copies the the data into the other cell.
I need a way to stop this from happening, because this causes reference errors on other sheets when users do this by accident. However, I still need to allow the user to be able to type in that cell, use the autofill option, and the Ctrl+C option. Is there a way to program a macro that can be put in the sheets to do this for specified cells or ranges?
View 5 Replies
View Related
Sep 30, 2006
I'm trying to create a simple VBA function in Excel so that I can sort and calculate results based on various conditions. I can get everything to work except changing the color of the cell where the output is placed. Here is what I have so far:
Function nameOfMyFunction(arguments)
If conditions Then
nameOfMyFunction = "No GRP"
ActiveCell.Font.Color = RGB(255,0,0)
End If
I'm sure that its something simple, but I've spent long enough on my own trying to figure it out and nothing seems to work.
View 5 Replies
View Related
Aug 5, 2008
I understand the specification of a Function procedure does not provide for it to change the contents of cells. However, I have the need/preference to be able to change cell data by using a custom function (instead of a Sub) because of its ease of use and execution of multiple what-if scenarios - displayed jointly.
Is there a way around this specification/limitation - any way? Are there different techniques (standard or non-standard) to achieve thisr goal?
View 8 Replies
View Related
