I had below data in excel which i need in one cell with some formatting. I had lots of lines like this, some of the input are below
JT12345
JT12346
SB06
SB07
SB 08
SB 09
I want output in one cell say in A1 cell like 'JT12345','JT12346','SB 06','SB 07','SB 08','SB 09'
Sometime the data might be only 6 letter or 4 letter or 4 letter with space in middle.
I'm having a little trouble figuring out how to do some conversions. For instance, I would like to know how to convert a decimal number like this:
1.800
which is a ratio of 1.800:1 to display like this:
9:5
So, when the 1st value changes to something like this:
1.778
It would be displayed like this:
16:9
Likewise, a ratio of 1.333:1 would be displayed as 4:3.
I also don't want it display in Time format as Excel has a habit of converting values with colons to time format.
I have a spreadsheet that we use to generate visual data, with very small column widths. I have a column width of 1 at the moment, and some data displays, while some doesn't. I want it to show a number which appears every 5th column, without having to merge and center each set. I used to use a macro to merge and center across the data set but the data set length is now dynamic so that is difficult.
View 1 Replies View RelatedWhen using the format cell function, I have found some cells don't respond to the format until I enter and exit the cell (or edit). Other cells with the same data change when formatting is applied. The cell format is being changed from text to decimcal number. I have a few thousand records and about 30 cells and about half of them change when the format is applied. It would be impossible to manually edit each non-responsive cell to change the format.
View 3 Replies View RelatedI would like to be able to change the color of fill in a cell if there are 3 occurrences in cells. Attached is a sample.
In the attachment, if the cells A2, D2, G2 all have a number "1" in them I would like cell K2 to have a color fill for the cell.
The same would be for the other cells, if B2, E2, H2 all have the number "1", K2 should have a fill in the cell as well, same as if C2, F2, I2 have a number "1", fill K2.
I only want the fill color to be in cell K2 if all 3 of the cells, ex: A2, D2, G2 have a "1" in them. Also, some of the other cells may have a number "1" in them but I would not want them counted unless it is 3 of the consecutive cells.
I have a piece of code that runs through various excel files and takes the data (minus the header) and pastes that into a separate workbook. The piece of code that does the actual copying i think is this (i have used code from the msdn website)
Code:
With sourceRange
Set destrange = destrange. _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value
I have tried replacing the sourceRange.Value with sourceRange.Text however when i do that it will copy nothing any more.
I have a spreadsheet where I have a column of dates and I want conditional formatting to highlight the cell red if the date is less than or equal to today but if the cell is blank to do nothing.
At the moment I have the following formatting applied using the "format only cells that contain" option I have cell value less than or equal to =TODAY() except that obviously highlights every cell red that doesn't contain a date. Is there another conditional format I can apply in addition to this that will not highlight the blank cells ?
I have inherited a monstrous speadsheet on which all data appears on a single sheet rather than being broken up across several sheets in some logical fashion.
One portion of this monster is a set of quite a few form letters, each of which reference various cells elsewhere in the spreadsheet.
I want to move all of these letters, together, to another tabbed sheet as a first step in reorganizing the monster.
Simply copying and pasting the cells or columns doesn't work. It fails in different ways depending on which pasting options I employ.
A very simplified version of my problem appears on the 5 sheets of the attached file, with what I hope is just enough further detail about the difficulty.
By the way, I also tried using the "Insert Copied Cells" option when pasting but since this failed with the exact same results as one of the other options I didn't include the results in the example shreadsheet.
I have a table that is a list for validating values in a column .what I want to do is instead of getting an error message anf stopping if the entered value is not valid the cells become red.
View 6 Replies View RelatedI'm running into a problem when trying to use INDIRECT in combination with Conditional Formatting Data Bars.
Basically I want to show a Data Bar in a certain cell based on the value from another cell.
It would be easy if I could simply copy the value from the other cell across into the cell in which I want to show the data Bar. Unfortunately I cannot do this as the values in the cells where I want to show the Data Bars can change.
As a solution when using Conditional Formatting Data Bars I figured out I can simply do the following:
> Under 'Minimum' use 'Type': Number with 'Value': Zero.
> Under 'Maximum' use 'Type': Formula with 'Value': =INDIRECT("B'&ROW())
This will pick up the value up from column B in the same row.
> In column B i then have this formula: =A1/C1. In C1 i have the percentage that the Data Bar should be using. By dividing the value in A1 with this percentage B1 will return a value that is used for the "Maximum" setting which will then ensure the Data Bar will display correctly.
For example: If A1 value is 4 and C1 value is 25%. Then the formula in B1 will return value 16. The scale used for the Data Bar is then 0 to 16. Seeing the value in A1 is 4 the Data Bar will correctly show 25%. If I then change the value in A1 to say 2 the scale used will change to 0 to 8 and the Data Bar will still be showing 25%, which is exactly what I'm after. So effectively this means a value from another cell is used to work out the Data Bar.
So all is fine up until this point. The problem arises when I want to apply this same logic to the rest of the column.
If I change the 'Applies To' to the range I want it applied to all the "Maximum" will only be based on the top row value. I'd expect cause I'm using the INDIRECT reference with ROW() it would have picked up the values from each individual row. I could create a new CF rule for every row, but that's not practical as there are way too many rows.
Why does the INDIRECT function with ROW() not work in this case? How to make the CF rule copy down properly?
I'm importing data from Excel 2010 into a database.
I'm joining to 2 cells into one. A1 = 12 Rushwood Street and B1 = London. So C1 = 12 Rushwood Street London
I need C1 to look like this:
12 Rushwood Street
London
I tried wrap text but when I import it comes out as one line in the database.
I have a Workbook (Test 1) with several columns A-S. I'd like to be able to continue to add data into Test 1, and have some of the data pulled into a new Workbook (Test 2). Basically pulling data from Test 1, cols. D, L, J, E, I, C, K, Q and S - into the new workbook Test 2.
Some of the columns in Test 1 have conditional formatting and data validations, but I need the data in these columns to be replicated into Test 2 - without having to copy/paste between the 2 workbooks.
I figured how to do this in Excel 2010, but my office is still using 2007 and I can't seem to get it to work....
I'm trying to analyse all of my bank statenments to see where all my money went!! I've got online banking so I thought it would be a relatively easy process of copying and pasting each month into excel.
Well, that in itself was easy, the problem is with the formatting of the financial numbers. Excel doesn't seem to recognise them as numbers, so at the moment I can't do any manipulation with the numbers. I have tried everything such as:
Format Cells > Number > Number and Currency
Copy and Paste Special > Values only
Copying into Notepad and back into Excel
Even if it appears to have changed the numbers to 'number' or 'currency' formatting by right justifying the numbers, if I try to add up the numbers using the SUM function, it doesn't want to know.
For some reason nothing seems to work - what am I missing?
For an example, I have attached a snippet of the spreadsheet with my starbucks transactions.
I am working on a project that has 5 worksheets. I have been able to figure out everything else I need to do but this has me stumped. I have data in Sheet1 A6, that i want to place in Sheet2 A6, Sheet3 A6, Sheet4 A6 and Sheet5 A6 and keep data and formatting(BOLD AND UNDERLINE). So I change Sheet1 A6 and the other 4 sheets change also. I'm using Microsoft Excel 2007.
View 1 Replies View RelatedI have one column that contains a monetary amount (column AQ) , and another that contains text reading either "inflows" or "outflows" (column AC)
When AC says "inflows", AQ should be positive, and when AC says "Outflows", AQ should be negative.
I need the text in column AQ (the monetary amount) to become red when the the opposite is true.
i.e. When AQ is negative and AC says "Inflows", AQ should become red. And when AQ is positive and AC says "Outflows", AQ should become red.
I have two columns. In column B is the date of "last check". I column A is the date of "next check". I would like to have cell A2 in yellow color 334 days after the date entered in cell A3 and than in red color 365 days after the date entered in cell A3. Same thing for cell B2 related to date entered in cell B3. Yellow color in cells announces that check will expire within 30 days and red color that check has been expired.
View 1 Replies View RelatedThis is a conditional formatting problem I have not been able to resolve:
I have a range, say A1:N30
Each cell contains a text/number combination.
I would like to highlight each cell on a specific row if the vale exists within the row above - the issue is that the cell values are not in the same columns.
I have attached a sample workbook with the desired output.
Conditional formatting.xlsx
I am trying to alert our purchasing mgr when order dates are approaching or not meeting our project deadline.
As of now i have the following rulesif order date is due today or past due - redif order date it greater than project date - redif order date is due within 2wks - yellow
Now all I need is a rule where there is an order without a due date but the project deadline is within 2wks (yellow) and past due or due today (red)
KO_04.01.14.xlsx
I have a column who's content is determined via about 6 nested if statements from data on that row. That cell is then conditionally formatted to a certain color based on the text that is ultimately printed from the nested ifs (simply an extra visual legend for the text). All of this contributing info and about 1000 lines items make a very large and difficult to print page. What I am trying to do is a make a summary sheet that simply takes the index number of these 1000 rows and copies or links the conditional formatting of the mentioned cell onto this number on another sheet. I have already linked this status cell and put it adjacent to the index number which works well in that with two columns I can show the index and status but if I could combine the color of the status cell onto the index cell, it would be even better. I am pretty sure another conditional format for this summary sheet would not be possible or be extremely complex since the contents of the index cell I am conditionally formatting have no bearing on the conditions for the format. Was hoping there is some VBA magic that could simply mimic the conditional formatting from one cell and put it on another.
View 1 Replies View RelatedI have Excel 2000, recently when I format a cell to display mm/dd/yyyy and enter mm/dd/yy, it is displaying dd/mm/yyyy. Or it starts out correctly and during a future opening of file it displays incorrectly.
View 2 Replies View RelatedI'm using Excel 2010 and I would like to format a cell (say, the font of that cell turns RED) if the value of another cell meets a certain criteria.
View 9 Replies View RelatedI would like to format cells with a currency based on a value selected at the top of my worksheet. Cell C3 is a drop down of currencies. And I would like cells range E11:E200 to format in whatever currency selected in cell C3.
View 9 Replies View RelatedI've set a conditional format to flag the row green if cell e2 has a Y or a N in the cell.
Currently i have the following conditional format formula is as
=IF($E$2="y",TRUE,FALSE) this is set to change row to green
=IF($E$2="n",TRUE,FALSE) this is set to change row to red
My Questions =
What i want to do is copy this formula through the 500 or so rows and have the formula adust to
=IF($E$3="y",TRUE,FALSE) For row 3
=IF($E$4="y",TRUE,FALSE) for row 4
=IF($E$5="y",TRUE,FALSE) for row 5
Ect.
I've tried using the formate painter but all it does is set all row to conditionally format dependant on what is in Cell E2. Thoughts on how to copy this formula is conditional format so i don't have to manually adjust it line by line. Doing this 500 times over would be a pain.
I would like to create a cell that will tell me if a dimension has failed.
I have a tolerance that the value must be between and if it is not then i would like a cell that will input an X
i have an application that can export tables to Excel. But in Excel, the data is only recognized as text, and not as or numbers. A typical column looks like this:
$300,40
$500,23
($350,00)
etc.
An amount in brackets signify a negative amount. Is there an easy way how i can convert this cells into numbers i can work with in excel? I have a similar problem with dates, times and cells with both date and time in them.
My data currently looks like this: 2iiaRXl.jpg
I would like to render it into something like this: TZ1ibNb.jpg
I tried to use pivot tables but I couldn't get it to look right: Jyn1CKl.jpg. If possible, I want the companys' ratings to be displayed along each corresponding year row, similar to the picture in the second link.
I'm working with a data logging system to collect data on a circuit. The logging software allows you to export the data collected to Excel in real time using DDE, and having talked to the logger company I managed to obtain a macro to get the data into excel (I'll put it at the end of the post). This works fine, and I can now press a button and Excel updates itself with the new data every second. What I want to do is quite complicated and I have no idea where to start:
1. The data obtained via the macro only displays in static cells, that is when the new data comes in it overwrites the old one. I want Excel to take a copy of each new bit of data as it comes in, and copy and paste it into a column on the same sheet so that it can be stored. I then want it to move down a row and take the next reading and so on, so that each time a new reading comes in, Excel takes the value and puts it in the next row down in a set column
2. Having a time elapsed measure would also be very useful, so that it records the time after starting the other macro in a column next to the ones collecting the data as described in 1. In this case, each new row would be 1 second further on. The actual time is not massively important, just the 'relative' time since the data collection started.
3. Once the data is copied into the columns to be stored (1.) I then want to calculate two sets of moving averages and place those in columns next to the data. My current plan is one average of 30 samples and another of 5.
4. Once I have the moving averages I can then get Excel to plot some graphs to show the progression of the two values
5. Since my project is revolving around detecting a short circuit in a coil of wire, it is looking for drops in resistance. I want Excel to be able to monitor the two calculated moving averages and if the difference between them exceeds a set threshold for more than 5 samples then some alarm is triggered (for now just a cell changing colour to red or a pop-up box or something).
I am importing the NFL standings into a worksheet - [url]. In the Home, Road, AFC, NFC & DIV columns, the team records are showing up as dates....
View 9 Replies View RelatedI'm working with a data logger which saves a basic text file in this format.
02.472413
1-0.004203
02.884523
1-0.004805
02.884523
1-0.004805
10.390663
02.884523
Where the first number is the channel of the logger, and the second number is a voltage. Channel 0 is reading distance in volts, and channel 1 is reading force in volts.
The data consists of over 2000 readings like this. I am trying to seperate it in a way that all of the channel 0 readings are in one column, and all of the channel 1 readings are in their own column.
02.472413 1-0.004203
02.884523 1-0.004805
02.884523 1-0.004805
02.884523 10.390663
OR
02.472413
02.884523
02.884523
02.884523
1-0.004203
1-0.004805
1-0.004805
10.390663
This is my first post on this forum and I hope I'll find here all the help needed! This is VERY important for me. My boss is expecting me to finish this week. I am not really an expert in Excell but I have to do some pretty complicated things. At least they are complicated for me. J
OK ... here we go! ....
