Excel 2010 :: Consolidating Info In Correct Format
Sep 11, 2013
I have a spreadsheet with five cells (L3 - L7) that have dates entered into them in the format of 9/9/2013, 9/10/2013, 10/20/2013, 10/21/2013, 10/22/2013. In another cell, cell n2, I have the formula:
=IF(L2=0,"",IF(L2=1,L3,IF(L2=2,CONCATENATE(L3,L4),IF(L2=3,CONCATENATE(L3,L4,L5),IF(L2=4,CONCATENATE(L3,L4,L5,L6),IF(L2=5,CONCATENAT E(L3,L4,L5,L6,L7)))))))
In cell n2 I get: 41526415274156741568481569 This is the excel number for the above dates, but I need it to look like:
Sep. 9 - 10, 2013, Oct. 20 - 22, 2013
I also need it to be able to isolate single dates for example if the date in cell l5 was 10/5/2013 instead of 10/20/2013 then I would want n2 to look like Sep. 9 - 10, 2013, Oct. 5, 2013, Oct. 21 - 22, 2013. but my formula currently only changes that number in the string from 41567 to 41552.
The reason I need this is because this information is part of a identifier and also maybe used in reports, the people reading the reports won't know this long number is actually dates and won't be able to read them if they did.
Also the cells L3 - L7 data depends on cell l2, 1 = one date entered into cell l3, 2 = two dates, one in cell l3, the next in cell l4, and so on for up to five total days that may or may not be consecutive. Also, it seems to work as is, but only for one day.
View 1 Replies
ADVERTISEMENT
Feb 14, 2012
I have several sources of information for client files. the number format includes a reference number and a month, eg 123/11, 456/02. In some cases, the numbers may be presented including the company initials, eg ABC/00123/11. I want to make sure all numbers on all sources are the same as I am using Vlookup and this reference no to copy information from one source to another. Sometimes when I change the format Excel sees this as a date, eg 11/11 is 11-Nov, and in some cases Excel sees a fraction, eg 123/11 is 123 11ths or 123 divided by 11.
I can easily take out the reference no with a find and replace FIND ABC/ replace with . But this often results in a number with leading zeroes, eg ABC/0123/06 will leave 0123/06.
I am using Excel 2003.
View 1 Replies
View Related
Jan 14, 2014
I have a table created in Excel 2010 by a SQL query. the query pulls 3 columns of data - Resource Name, Contract Company, Labor Category. I then add a column called "KEY". KEY is derived using VLOOKUP. VLOOKUP is matching the Contract Company from the table created with SQL to a table called "Rate Key" in another worksheet in the spreadsheet based on the matching the Contract Company. That all works fine. If the table changes due to changes in data coming from the SQL db, the column I've added using VLOOKUP recalculates correctly. Then I add 2 more columns. One is called RATE - which is a number that I manually type in. The last column is called RCode and is calculated by the RATE times the KEY. Whenever I refresh the SQL query, all of the the calculated rows work fine; however, the data in the RATE column that I manually enter does not move - it stays with the row regardless of whether or not the name changes when the new SQL data comes over. How do I get the values manually entered in the RATE column to move with the correct row when the rows change?
View 2 Replies
View Related
Dec 3, 2013
I am trying to create a formula for a work report and I am afraid I am a bit of a novice with the more complex formulas. Here is what I am trying to do: Sheet 1 has Sales order numbers that duplicate based on the sales lines. Sheet 2 has single lines of sales order numbers and their assigned PO number. I would like to have Sheet 3 where it combines all data and duplicates and fills in the PO number to match all of the sales order numbers that repeat on the other sheet. I hope this makes sense. I can't seem to figure out if I need a VLookup or If or Match formula (or all of the above).
View 1 Replies
View Related
Jan 26, 2014
We receive about 20 sales files of several hundred lines of data each day from various agencies. I want to create a macro / VBA code which checks that the data submitted is correct so that we can upload it into our database without import errors and / or having to manually check each line of data.
I envisage something like an output report:
#####################
149 entries
Column A - Date - OK
Column B - Customer_Phone - Errors (Should be 11 digits)
Row 21 - Customer_Phone - Error (Not 11 digits)
Row 108 - Customer_Phone - Error (Contains letters)
Column C - Outcome - OK
Please correct and re-check.
#####################
View 4 Replies
View Related
May 9, 2014
So in my excel document I have it such that on the first sheet (Labeled 'Sorted') the data is set up as follows: LOCATION, EXTENSION, NAMEWith the appropriate data under each header. On the second sheet (Labeled 'Locations_Ext') I have a named range called Locations; it contains extensions from a separate document, names of people associated with the extensions from the document, and their locations on the map. I am trying to have it such that the excel matches the extension number given on the first sheet with the ones that I extracted from the document on the second and output the location into the first column on the first sheet. The code I have set up for that process is:
=VLOOKUP(B(//SomeValue),Locations,1,FALSE)
I then want the excel to cross check the information that I took from the Visio to see if the visio is up to date with the latest info we have received (Which is the data on the first page under Extension and Name). The code:
=IF(C(//SomeValue)=VLOOKUP(B(//SomeValue),Locations,3,False),"Good","Error")
Both codes seem to be giving me this: #N/A as the results for every cell.. I'm using MS 2010
View 2 Replies
View Related
Aug 1, 2013
I have an Excel 2010 spreadsheet where the default number format keeps switching to the date format. I keep switching it back and it'll stay as a number format for a time, but then it'll switch back. This is also the case for any new worksheets in that one spreadsheet.
This only happens with this one file - everything else behaves according to my regional settings or to how the file had been previously formatted.
View 3 Replies
View Related
May 1, 2014
I have around 30k data. which is in invalid format.
Ex: 12987654321vinay kk 876543219
32567456789 kkccjhg fo 345678921
I want to convert this into correct format as below with start letter from
MOD, 987654321,, vinay, kk,87654219
MOD,567456789, , kkccjhg, fo,345678921
I want to know which are formulas I have to use to get this info in correct format.
View 1 Replies
View Related
Jul 1, 2014
Isn't there a checkbox somewhere where I can tell Excel 2007 to leave xls files as they are? My Excel 2010 doesn't urge me to save an old xls file format into xlsx but in Excel 2007 I can't find a switch (and I don't rememeber if I had to do anything in 2010). So ultimately I want to stay in compatibility mode when using old files.
View 2 Replies
View Related
Jan 23, 2009
I have a spreadsheet which needs to display currency in the correct format dependant upon whether a contract is in English or German Law. Excel defaults currency format as €310,000.00 however in German this needs to be €310.000,00 i.e. with the decimal point and comma in opposite positions. Does anyone know what expression I can use to format it as the latter?
View 2 Replies
View Related
Jan 16, 2014
Open a brand new spreadsheet whether the format is set to "General" or "Text" it will not let me enter numbers. For example, when I enter 2007 in the cell it displays 20 07 not really that big of space between the two zeros - but still a space. I can't format this no matter what I do.
View 1 Replies
View Related
Dec 17, 2011
i am trying to work on a database that has some errors that i want to correct the issue is that some of the cells have data that is listed last, first instead it needs to be listed into first last.
I can not seperate this into 2 fields as it would break the program this database belongs to.
this database is linked from excell to access i am using excell to update an access database
i use office 2010
View 9 Replies
View Related
May 5, 2014
In cell 'I6' if I enter the text "Balance" I would like it to display as "Actual" but if any other text is entered I would like it to display the text entered. How to do this using custom cell formatting. (Currently on Excel 2010).
View 2 Replies
View Related
Oct 19, 2013
I am working with a 2010 Excel spread sheet and need some direction.
One column has dates in it. Some dates are like dd/mm/yyyy (eg 15/03/1974) and others in the same column are in the following format 14th October 1983.
I need all of the dates to be in the first format (dd/mm/yyyy).
Is there a way to do this without manually changing each field? I have already tried highlighting the column, then clicking the 'numbers' arrow and picking 'date' from the number tab but that didn't work. It's never that easy, is it?
View 14 Replies
View Related
Jan 28, 2013
I am looking for a vba to use a button to save a file using the cell as its name and save the file in xlsm format. I am using Excel 2010.
View 12 Replies
View Related
Mar 27, 2014
I need Macro to Format all cells in column according to value. The cutoffs are >20, between 21 and 50 (>21;<50) and greater than 51 (<51).
I have macro to consolidate cells from multiple files to single workbook. Now I have to 'color code' the value in all cells in column 'B', all as part of one large macro.
View 1 Replies
View Related
Oct 23, 2012
Using 2010 - When I paste a chart I can initially retain the format of chart bar colours (Paste Special - source formatting). But when I select a new data source for the chart the formatting of the colours is not retained.
View 1 Replies
View Related
Jan 16, 2013
I have a large spreadsheet that I would like to indicate the location of the cells (Change cell color or text color) which contain formulas. My thought is that conditional formatting would be the easiest way...
View 3 Replies
View Related
Jan 10, 2014
I just upgraded to Excel2010 and noticed that if I calculate percentages resulting from other formulas, the result displays as a percentage with 15 decimal places and it ignores the format of the cell. My guess is that since formulas are being used in cells A1,B1, and C1, Excel is ignoring the format. When I hard code the numbers, I obtain the desired result.
Is there a way to change the default of 15 decimal places for formula results involving inputs that use formulas in their own creation? (outside of the Round function in each cell? Within Options?)
The data looks like this:
A1 = formula resulting in 1.76%
B1 = formula resulting in 1.90%
C1 = formula resulting in 1.69%
D1 = C1*B1/A1 resulting in 1.8218147382920100%
The desired result is 1.82%.
View 4 Replies
View Related
Feb 2, 2014
In Excel 2010, I'm using the following VBA code to unmerge cells:
Code:
Sub defusion()
With Selection
.MergeCells = False
.Locked = False
.Borders.Weight = xlThin
End With
End Sub
This don't keep the conditional format - the first cell, yes, but not the others. How to modify my signature?
View 1 Replies
View Related
May 27, 2014
I have a Column of dates in this format: 5/19/2014 8:08:44 AM I am trying to get this to read 20140519 or yyyymmdd It doesn't matter what format I change to, it will not update unless I activate the cell and then tab or enter out of it. I have 3000 rows to convert. with this either by setting or VB Sub?
Excel 2010
View 3 Replies
View Related
Feb 26, 2014
I need conditional format in column Q which highlight in red, any date over 14 days old.
This column also contains texts (non-dates) and these need to be left alone.
I've experimented with a few bits of formula I've found online, but nothing has worked . .
I'm on Excel 2010.
View 3 Replies
View Related
Jan 25, 2011
Using Excel 2010 - I have a large workbook with multiple worksheets - been using it succesfully every day for a long time. All of a sudden every empty cell, and any cell not specifically formated in every worksheet has a default cell format of Time. Any new workbook created is OK.. defaults to 'General' - But, if I add a new blank worksheet to this particular workbook it defaults to 'Time' format. How did the default cell format for this workbook become set to 'Time' from 'General' and how do i fix it?
View 2 Replies
View Related
May 16, 2013
Using excel 2010 on PC.
Paste URL as Hyperlink: I just want to paste a bunch of urls into a spreadsheet and have them automatically formatted as urls. i've checked my auto-correct options, and i have selected the checkbox that says: replace as you type/internet and network paths with hyperlinks.
See attached. if you copy and paste a url into the spreadsheet, it will be formatted as text.
Format Existing URLs as Hyperlinks : the attached spreadsheet contains urls that have been copy & pasted. is there a way to format them as hyperlinks all at once?
View 1 Replies
View Related
Apr 25, 2014
I would like to shade my cell yellow if the priority equals A or A-NEW and ACV=o.
In the data below, items 4 and 13 would be shaded yellow.
I am using Excel 2010.
Priority A, B, C
Consumer UPC
Item Description
ACV Weighted Distribution
[Code] .......
View 8 Replies
View Related
Aug 21, 2013
I have a column of dates formatted as:
20130201
The cell format in the column is General.
I need to change it so that the format looks like this:
02/01/2013
Is there a quick/easy way to do this in Excel 2010?
View 2 Replies
View Related
Jul 19, 2014
I'm using Excel 2010. Is there any solution which cover requirements given below.
1) I've converted a range in to table format (using Format as Table)
2) Need to Prevent pasting on data validation cell.
3) Need to Prevent change in cell format after pasting data (other than data validation cells as mentioned above) from same workbook or other workbook
4) Need to insert row in the table as per requirement.
View 3 Replies
View Related
Sep 27, 2011
Just recently My Excel 2010 has decided to not let me right click to format cells, delete or insert rows. I can do these functions from the ribbon, but not via right click. this happens in both existing spreadsheets where I am the author, or even a brand new spreadsheet like in the image below. we have restarted the computer, Uninstall and reinstalled Office and still get same symptoms. I got here thru google but cannot find an answer anywhere.
I am very computer literate and even our IT personnel have looked at this with no answer. as you can see in the image, these options are greyed out.
Running Office standard 2010, Windows 7 pro x64
View 8 Replies
View Related
Mar 20, 2012
Formating Date Issue
--
Excel or Access version:Excel 2010
Computer operating system:Windows XP
Sample data:No sample data
Formula(s) right now:Mar-5-2012
Current result(s):Mar-5-2012
My goal:To convert the above text into a date format.
Error message:Can't format the way I want it.
How error occurred:No error message
Generated in:Excel
View 3 Replies
View Related
Dec 19, 2012
I have two columns of data and cannot get the column to change the format to display as currenty or accounting, with a currency £ sign and comma separator...I select it all and go to Format cells to do the changes, but it does nothing, at all!
View 2 Replies
View Related
