Each row in my file describes a country and a year. I have data for some years; I would need a way to copy these observations to the following years until there is a new value (conditional to being same country). On the other hand, if there was no value for that country, I would need a ".".
View 4 RepliesI am having trouble copying conditional formatting down my spreadsheet. When i copy it the number dont change. the formula i have in the conditional formatting is =O3
View 9 Replies View RelatedCell (A1) is formatted differently from the other cells of the same column. I added to it a conditional formatting (based on a formula) which I want to copy [alone] to the rest of the cells, without getting the other different formatting of cell (A1) copyed as well.
View 9 Replies View RelatedYou assisted me in writing a macro to move rows from one worksheet to another based on column criteria. I need a slight improvement to the code, ie it should only delete the rows after moving them if the rows are those that matched the criteria and not those that simply had blank cells for criteria after the rows moved are deleted.
Private Sub CommandButton1_Click()
Dim c As Range
Dim count As Integer
count = 0
If MsgBox("Are you sure you want to move rows to the archive ?", vbOKCancel, _
"Archive") = vbCancel Then Exit Sub
On Error Goto catcher
For Each c In Range("J1:J" & [sheet1].[a65536].End(xlUp).Row)
If c = "yes" Or c = "good" Then
count = count + 1......................
I have data like this:
James English
Bea English
Bea Math
Pat English
Sarah Math
Sarah Geography
Sarah Art
What I want is
James English
Bea English Math
Pat English
Sarah Math Geography Art
I have ~350 rows of subjects. Is there an efficient way of doing this? At the moment I'm cutting and pasting.
I have a set of values on Sheet1. These 'original' values will never change.
I then have a set of values on Sheet2. The 'updated' values get updated every month.
On Sheet3 I want to show the updated values again, pretty much a copy of Sheet2, but where a value is higher than that on Sheet1 or lower than a value on Sheet1 I want to use conditional formatting to show this.
Is there a simple way to use conditional formatting in such a way? I have a long list of values so don't want to individually create a rule for each cell.
I have one excel 2007 workbook with three sheets in it.
Sheet 1 is a master sheet with 1858 rows and 45 columns of data.
Sheet 2 has the qualifier data, that is, one column with 255 unique 6-digit integers each in its own cell.
Sheet 3 is a result or output sheet that is currently empty.
Here is what needs to be done.
Compare all of the 255 unique 6-digit integers from Sheet 2 with the integers in column number 15 (or Column O) of Sheet 1.
If there is a match then copy that entire row from Sheet 1 into Sheet 3.
i want to copy data from one excel workbook to other (or ever between diff sheet wud do ) on basis of search on a complete column by picking data one by one from other column & on finding the exact match copy 4 columns form one workbook(or sheet) to other. this has to be done 4 a bulk of data (1000+) so manually is next to impossible.
View 2 Replies View RelatedI have :
A1 = "Banana"
A2:10 = blank cell
B1 = xxxxxxx (always 7 digits) (number)
B2:B10 = xxxxxxx
How to fill the range of A2:A10 with "Banana" only if the value in Column B is having a value (or not blank)?
The second one is, how to delete entire row if the value in column B is text?
I have applied conditional formatting to a cell using formulas and i want to copy that formatting to a new cell using similar formula but pointing to different cells. Is it possible to do so? I have tried to copy and paste special>formats, but that just copies the conditional formatting with the same formulas.
Sample: =IF($C$5/$C$295>$R$5,TRUE,FALSE)
I want to copy to $D$5/$D$295>$R$5,TRUE,FALSE) without having to paste the new formula in each time.
How to copy and paste conditional formatting with our changing the cell values. My first row of data starts in row 4 and here is what I have in D4
If D4 is less than or equal to AA4, AW4, BS4, CO4 then it will highlight D4
Now I want to copy that format and paste it for all rows in column D. The problem I'm having though is when I paste the formula it doesn't change to match the row I'm on. For example, when I paste it in D5 this is what I get
If D5 is less than or equal to AA4, AW4, BS4, CO4 then it will highlight D5
How do I paste it so that it will update to show AA5, AW5, BS5, and CO5. I want to be able to do this for 500 rows.
I have a workbook containing two worksheets of staff training records.
The first work sheet contains a list of names and the dates they completed various training courses. I have used some simple date based formulas and conditional formatting to colour-code their name depending on whether their earliest retraining due date has passed, is coming up in the next few weeks, or is a long way off. The data is set out alphabetically, one person per row of data.
The second sheet contains the same list of names, but each person's data is split across two consecutive rows. The cells in column A which contain the staff names are merged in pairs so that the name heads both rows of data.
I want the colour coding of the merged name cells in sheet 2 to automatically copy the colour coding applied to the single name cell in sheet 1, but don't know how.
I'm using excel 2010.
I'm a 2007 user. I am trying to conditional format a range of F1:J10, where F1 will be conditionally formatted relationally to A1. G1 is relational to B1. H2 relational to C2, etc., all the way down to J10 relational to E10. My conditional format formula in cell F1 is >A1*2 (will format F1 as orange). I want to be able to copy the conditional formatting over so the formula in J10 for example would be: >E10*2 (will format it orange).
I omit the "$" in the conditional format formula in an attempt to prevent it from being absolute. But every time I copy the conditional formatting over to the other cells, the formula within those cells remains identical to the formula in the original cell.
Excel 2007.
I have conditional formatting set up so that the cell becomes highlighted if it contains a specific text.
Example of wanted conditional formatting:
Conditional formatting rules manager
Rule
Format
Applies to
Stop if true
[Code] ......
For some reason this formatting is inherited by another adjacent cell as we continue to input information.
For example:
Column M is formatted so if 'AP' is placed in any cell in that column the cell fills pink. As more information is inputted into the sheet, the conditional formatting copies to Column N. It does not happen with every entry and I have not been able to isolate the specific steps to recreate the copying. Multiple people use the same sheet and fill it out and needed.
After working with the sheet changes are made to the "applies to" column without people meaning to.
For example:
Conditional formatting rules manager
Rule
Format
Applies to
[Code] .......
I would like to make this formatting so that is only applies to the Column M and not "travel" to other cells of the sheet.
I am working on a project to import cellular phone usage data from Sprint into a workbook.
All cells in Column A are blank. Column B contains 1 instance of the cell phone account #, followed by dates of calls made, which continues until the next cell phone account # appears, and it’s calls made. The # of calls made obviously varies per account #.
The dates are in date format. The cell phone account # is in text format and is formatted with blue fill.
I need to copy the cell phone account # into column A for each line that has a date value in ColumnB, but the value copied must change when it reaches the next cell phone account #, so that the calls are matched up with the cell phone account # and can be imported into SQL.
Acct #
Date
Time
City Called from
State Called From
Called #
City Called
State Called................
I want to copy the current region on sheet 3 and paste that into sheet2 starting with cell E4. But I don't want to actually use the copy method. I believe there is a way to do this. I was thinking the following:
[Code] ......
The idea I had was to simply state that cell E4 would be assigned the value of CurrentRegion on sheet 3. Is there a way to bypass the copy method?
If not, how to copy, then paste?
I am trying to make the cells Iam am copying paste hidden cells with all formats - seems to work fine other than the security part of them, Iam makeing a sheet for work and just trying to make it were it will not get destroyed by other users-
View 12 Replies View Relatedi have created a table. when i m editing new row then formulas is copying bu value is not copying ?
View 3 Replies View RelatedIs it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.
Possible???
Having trouble changing this formula from its current Median to a Quartile or Percentile. The median formula looks like this:
(MEDIAN(IF(DATA!$B$15:$DZ$15=1000,DATA!$B26:$DZ26))) and works perfect.
However I can't figure out how to manipulate it into a percentile keeping the condition of
IF(DATA!$B$15:$DZ$15=1000)
I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.
View 2 Replies View RelatedI have a list containing blocks of stock price values with each block representing a series of values at a given time e.g
1400 156
1400 99
1400 74
1400 86
1400 256
The number of entries may vary for each block but is never more than 60. the next block may be e.g.
14:05 15
14:05 42
etc
and down a very long list of around 65000 entries. I'm looking for a routine that will add up all the values in each block and calculate the total for that time block. I then need to fill in an adjacent column with the perecentage that each value is of the total. A completed block may look like;..............
I have about 5000 rows of data and I need to do some copying.
Here is the situation:
I have a product number in cell A2 that needs to be in A3, A4, A5, A6, A7.
There is then a different product number in A8 that needs to be in A9, A10, A11, A12, A13.
This pattern continues down the sheet for about 5000 rows, A unique product number every 6 rows, that needs to be copied down the following 5.
Is there a macro to automate this process? Rather than going down every five rows and AutoFilling the rows.
My main sheet has a couple drop down menus controlled by vba which depending on what is selected, it assigns cells differently. For example, from the drop down menu, if 'A' is selected, cell G16 will be assigned as "abc", but if 'B' is selected from the drop down menu, cell G16 will be assigned as "xyz".
Just for additional information as it may help, "abc" and "xyz" are actually assigned around G90 and G91, but when 'A' or 'B' are selected, it cuts "abc" and "xyz" for G90 or G91 and pastes them to G16.
What I am trying to do is have cells in another worksheet show what is in the first screen. But if I assign cell G16 in Sheet2 as 'Sheet1'!G16, it will work if 'A' is selected from the drop down menu, but return #REF! when 'B' is selected from the drop down menu.
I have these values in one column
706
706
706
512
512
12
12
12
12
In an another column I have same values and in the next column some more values that correspond to the previous values
12 red
512 blue
706 orange
I need a formula that can automatically copy those text values according to number values.
706 orange
706 orange
706 orange
512 blue
512 blue
12 red
12 red
12 red
12 red
I have tried =lookup and some variations of =if, but no good result so far.
Here it goes, my worksheet has multiple data (numbers) that are distributed like this :
Column L Column AColumn B
75,36-6,73 30,74
74,75-6,68 30,76
50,7 15,0519,55
49,3115,5219,94
69,44-8,28 36,3
68,41-8,31 37,03
47,4917,5 18,75
46,5617,7 19,39
71,07-6,38 35,99
70,05-6,42 36,84
44,2523,0418,24
42,8823,7 19,25
68,65-2,4 34,39
67,75-2,35 34,84
There are about 200 rows with values, I need to copy the numbers frow row 1 that are 75,36 -6,73 30,74 ,then in row 5 they are 69,44 -8,28 36,3...etc.
So the point is I have to copy every 4-th row with values in a nearby column. Of course I've tried the Ctrl + left click for every 4 rows then copy/paste it, but I was wandering is there a faster way??
Case: I have 4 worksheets and I want 2 sheets will populate based on the filter value template in the new workbook. I have created the code however I do not know how to copy the other tab follow the filter value template. The other tab only contain general information.
Question is how do you add together the other tab into population of filter value template? I want tsht & csht together in the new workbook.
Option Explicit
sub fillouttemplate()
'from sheet 2 data fill out template on sheet 3 and save
'each sheet as its own file/
dim lastrw as long, rw as long, cnt as long
dim dsht as worksheet, tsht as worksheet, csht as worksheet
dim makebooks as boolean, savepath as string
dim szrelationshipID as string
dim iNumrows as integer
[code]....
i use the following to copy columns from and external sheet to the current workbook
Dim wbCopy As Workbook
Dim wsCopy As Worksheet
Dim rngCopy As Range
[Code]....
this works fin but doesnt keep the column widths in the active workbook
I have tried adding Paste:=xlColumnWidths to the pste special but i get a "Paste Special Method class failed" error
I am trying to load the columns from the external sheet to the active workbook sheet but i want the column widths to stay as they are in the active workbook
Using VBA; How do I copy a formula in a cell to the next row and at the same time incrementing the row numbers in the formula?
For instance: Formula in D1 is = A1 + B1 + C1
Copy the formula in D1 to D2 but then D2 must contain the formula = A2 + B2 + C2
What VBA code will copy column A in Sheet 1 to column B in Sheet 3? The range of column A to be copied begins in row 2 and varies in length. Column A ends right before the last nonblank cell.