How To Maintain Formatting (such As Highlighting) When Importing In Ms Query
Apr 3, 2013
My current solution is to filter the Master Log so I only see Region 2, copy everything, then paste over Region 2, and repeat with 3, 4 and 5.
I have a master log (attached) that I import using Microsoft Query to 4 other spreadsheets (called Regional Logs), depending on the contents of the column "Region." I have all this figured out, but I can't figure out how to have the highlighting persist through the export and refresh. I don't think the attached file has any highlighting, but how I use this is I edit the Master Log throughout the week and highlight changes as I go. Coworkers use the 4 Regional Logs throughout the week to monitor different cases, particularly the new ones, so they need to see all changes for the week highlighted in yellow, which means the Regional Logs need to be linked to the Master Log and the highlighting must be visible any time a coworker opens the Regional Log. In addition, they are emailed across the state to county directors and they need to see the yellow-highlighted data, as well.
Currently, when exporting data using MS Query, no formatting persists. How can I ensure the highlighting persists? Or, is there another way to easily export the data, depending on the contents of the column "Region", which would allow the highlighting to persist
View 3 Replies
ADVERTISEMENT
Mar 20, 2014
I have to import data into Excel from Access (yes, I have to import it to Excel and cannot use Access exclusively). When I import; however, the formatting does not come over correctly, most especially losing the formatting of numbers. I need one of two solutions:
1.) How to get the formatting to move over correctly?
2.) Or how I can save the formatting of a table, then be able to apply it as a "saved" format? The biggest thing is that the numbers will changed the number of decimal places and/or change from general numbers when I want to see them as currency.
The top table shows how i want this formatted, the bottom shows how it is importing.
View 2 Replies
View Related
Sep 19, 2007
I am trying to create a Karaoke Song List in excel, which would show four columns on each page with Artist, Track, Artist, Track. I am importing a text file to do this. The problem I have is that I can import the file so that it puts Artist and Track going down, however, it would then need to go back to the top of the page to fill in the right hand side of the current page. Is there any code that retrieves the current print page number of the current cell (so that I can initiate the code to go to the top right of the current page and continue importing)? Or is there a better way of doing this?
View 3 Replies
View Related
Mar 12, 2008
I am working on a macro to insert a row below if a cell with a validation list has an item from the list selected. The new row needs to maintain the same format and formulas as the original row. To explain this better I have attached a very generic spreadsheet Called Custom Order. In this example cell A3 has a validation list. If the user selects one of the items in the list... I need the macro to insert a row below and maintain the format and formulas found in row A3.
Also this is posted on another forum. http://www.excelforum.com/showthread...=1#post1893257.........
View 2 Replies
View Related
Nov 20, 2010
I have some source data which is used to generate a pivot table. The data is conditionally formatted to color certain cells based on some other values. Once the data is transferred to the pivot table this formatting (as well as the number formatting) is lost.
I have found some code to fix the number formatting issue but can't seem to locate something similar to set the interior color of the cells in the pivot table to match that of the original source data.
View 2 Replies
View Related
Jul 3, 2007
with code in which I import data from the web via a query then try to use end(xlUp).row to find the last row of the query. Right now, the code continues while the worksheet still displays "Getting Data...", so the last row always comes out as row 1. After the data is finished importing, however, the last row is most definitely not 1.
If I put a break point after the query and wait for the data to finish importing, then let the code finish, it runs perfectly. But how can I have the code automatically wait to move on until the data is finished importing?
View 9 Replies
View Related
Apr 6, 2014
I have made a price comparison table.
It is set out as follows:
B = price , C= supplier, code D = price, E = supplier, code F = Price, H = supplier code, J = Price, K, Supplier code, L = Price, M = supplier code, N = price, O = Supplier code.
Each row is a product, so I am trying to compare each suppliers price (B,D,F,H,J,L,N) per row and highlight the cheapest price.
Whenever I get it to work it looks for the cheapest price in the entire table rather than on a row by row basis.
View 14 Replies
View Related
May 29, 2014
how to highlight a particular cell red if the value in one cell is less than or equal to the value in another cell. For instance, How do I make it so that the "Hours to go" cell turns red if the value in the "Actual Last Completion" value is less than or equal to the value of "Last Completed" cell?
View 3 Replies
View Related
Aug 19, 2009
Is it possible to highlight a1:h1 if cell a2 contains dark?
i used this code in cell i1-->=MATCH("dark",a2,0)
and i dont know what's next, my friend in the office told me to use conditional formatting.
View 2 Replies
View Related
Nov 5, 2013
I have a query relating to conditional highlighting - specifically highlighting a cell's column and row based upon whether it contains data or not i.e. if it is blank, it's column and row remain unhighlighted, but when it contains data, it's column and row should become highlighted. I could probably manage this myself but I have a few more specific requirements which I have illustrated with a few screenshots, however I can't see how to make these available to view here!I
View 1 Replies
View Related
Jan 14, 2013
I am wanting to highlight the ID's on list 2 which don't appear on list 1 and vice versa highlight the ID's on list 1 which don't appear on list 2.
View 2 Replies
View Related
Feb 2, 2013
I am using this Formula =Sum(Offset(A1,,,D1)) where I put for example number 4 in D1 and I get the sum starting cell A1 till Cell A4
I want the range to be highlighted as well, but I don't know how to do it.
View 1 Replies
View Related
May 18, 2013
I want to have Excel highlight one or two numbers/cells in one column based on the value in another cell. I am attempting to do this using Conditional Formatting.
For example, if X1 is BETWEEN A2 and A3, I want to highlight A2 AND A3. I have used various AND and IF functions to achieve this goal.
Also, if X1 is EQUAL to A2, I want A2 to be highlighted. This is easy to do with a simple formula.
But my problem is this: if X1 is equal to A2, I want ONLY A2 highlighted. If X1 is between A2 and A3, I want BOTH A2 and A3 highlighted. I cannot figure out how to achieve both. For example, when X1 = A3, A2 AND A3 are being highlighted, instead of just A3. I would prefer to do this without a macro, if possible. Can this be done?
Example formulas I have tried which easily achieve the highlighting of two cells when the value is between them:
Condition 1=IF(AND($I$35>I8,$I$35<I7),1," ")+IF($I$35<>I7,1," ")
Condition 2=IF(AND($I$35<I6,$I$35>I8),1," ")+IF($I$35<>I7,1," ")
It appears that the +IF, etc. functions are not doing anything.
This is more complicated by the fact that the value of, in the above example, I35 is based on a value from another sheet (I used a function to transfer a value from one sheet to I35 in the other sheet). As data are entered on Sheet1, the value in I35 in Sheet2 changes.
View 14 Replies
View Related
Jun 7, 2013
I'm having issues with conditional formatting. I used the INDIRECT formula to highlight specific rows.
For example, column A has the days of the week. I wanted the whole row highlighted if it was a Saturday. I used:
=INDIRECT("a"&Row())="Sat"
What I want to do now is to highlight specific cells (Columns F and G) if column A has Friday in it. I do not want the whole row highlighted if it contains Friday.
View 4 Replies
View Related
Jul 11, 2006
Well I spent some time yesterday trying to figure this one out. It was only last week that I have taught myself how to use VBA in excel. However this looks like a though one.
Simply what i would like to do is highlight a column of a certain date (till a certain row number) based on a list of dates.
eg. Across the top I have a set of dates and on another sheet i have a list of dates.
I understand that there is a VBA code for such conditional formatting but i can't get my head around it.
View 9 Replies
View Related
Dec 23, 2005
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 Related
Jul 17, 2012
I have 2 worksheets. One has locations with numbers, example:
275 Location 1
276 Location 2
I have all my locations on the second sheet, but in 2 different columns, listed with numbers only, example:
271 275
272 300
I have 2 scenarios I need help with.
1st:
If any of the numbers on sheet 1 match the numbers in column 1 on sheet 2, highlight the number on sheet 1 in green If any of the numbers on sheet 1 match the numbers in column 2 on sheet 2, highlight the number on sheet 1 in red
2nd:
If the number on sheet 2 matches any number on sheet 1, highlight green
I want these to apply to all the cells that have numbers (it could apply to all cells I guess as it should ignore it if it doesn't match, I would assume)
View 7 Replies
View Related
Mar 19, 2014
I want to highlight all the cells in a worksheet that have dropdown lists in blue color. Not sure how to do it.
View 13 Replies
View Related
Jun 1, 2014
I have a data with say around 500 rows and want to determine MAX for each row and HIGHLIGHT them, also if data in all cells is zero then it should ignore and highlight none.
I have tried this formula
=B2=(MIN(IF($B2:$E2>0,$B2:$E2))).
But this highlights all the zeroes, you can refer attachment for sample..
View 4 Replies
View Related
Jun 2, 2009
I have a program in unix that ouputs a text file in .csv form which allows me to import my data into an excel spreadsheet very easily. I am wondering if there is anyway I can add formatting, like to tell excel to make a cell green or something like that.
If it can't be done with .csv, is there any other tool or format available that will accomplish this?
View 9 Replies
View Related
Apr 1, 2014
I'm using Excel 2010 on my Mac. I would like to set up the data as follows:
A
B
C
D
E
F
NAME
DATA X
DATA Y
DATA Z
DATA XX
DATA YY
NAME
90%
88.2
410
88.4%
97.8
[Code] ........
Now what I would like to do is have the data in each column highlighted a certain color based on specific ranges. For example in Column B I want anything from 92% - 100% highlighted blue. I would like 83% - 91.9% highlighted purple, and anything under 83% highlighted red. Do I need to have a conditional format entered in each cell? Can I set up a conditional format for the whole column? Once I can figure the formatting in my example the remaining columns will be a breeze since they will be set up in the same manner just different number ranges, same colors will apply.
View 2 Replies
View Related
Feb 2, 2010
I imported some data from an MDB file while working at home. created a macro to that effect. and it works fine at my home PC. but when i try t execute this macro on another PC or work place, then it gives error #13. i dont understand why. something is wrong with the code. i think it is the "WITH" code for query tables connection giving error ....
View 9 Replies
View Related
Sep 1, 2006
When opening a CSV file in Excel, it seems Excel makes an "intelligent" attempt at interpreting the data into a data type.
This causes a text field containing 7300070E-4 to be interpreted as 7.30E+0
View 9 Replies
View Related
Mar 19, 2014
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.
View 3 Replies
View Related
Feb 15, 2013
I found this spreadsheet on here and I have been trying to customize it to what I need. I am trying to have scores from skins match highlighted. I want only the minimum score to be highlighted but if there is another duplicate minimum score I don't want it to highlight anything. I also need to find a way to count the skins won by each player and have it off to the side.
For those not familiar with golf a Skin is a game where you try to get the lowest amount of strokes on a specific hole. Ex- 4 people play the hole one. P1 scores 4, P2 gets a 3, P3 and P4 get 6. The skin would go to P2 who has the lowest score on that hole.
Highlight lowest number in each column not highlighting if there are duplicates starting at L6 down to L11 and for each column till AC. And the same for the group just to the right on attached file.
On row 13 and 14 it tells me who won a skin. I want to tally up the total skins won by each player. so if Joe's names shows up twice on R14 I want it to tell me somewhere in the sheet Joe = 2
View 6 Replies
View Related
Jun 30, 2014
I have a simple macro which extracts EUR rates from a website but the number format as appears in Excel is a disaster.
I am attaching the file : Querry EUR Rates.xlsm
How to format the rates accordingly.
Also posted here: [URL] .....
View 1 Replies
View Related
Aug 20, 2013
I have the following data:
C D E F G
Jan Feb March Q1 TargetThreshhold
£ 2,067 £ 1,978 £ 2,161 £ 2,100 £ 2,310
£ 1,492 £ 1,422 £ 1,611 £ 1,600 £ 1,760
£ 2,718 £ 2,683 £ 3,263 £ 2,400 £ 2,640
£ 2,853 £ 2,335 £ 2,481 £ 2,300 £ 2,530
£ 1,412 £ 1,533 £ 1,526 £ 1,600 £ 1,760
£ 1,067 £ 1,133 £ 1,246 £ 1,400 £ 1,540
And I am wanting to use conditional formatting. But wondering if there is an easier way than what I have at the moment.
The Formatting im using is:
=$C4>=$G4 Where value is TRUE the cell returns red and this applies at the moment to the First Row.
Is there a way I can get Conditional formatting to recognise the next cells without typing for each set of rows.
aka Row B would Read =$D4>=$G4
Row C would read =$E4>=$G4
etc?
Id rather not have to do a conditional format for each row as I have about 50 rows of data to do this to
View 4 Replies
View Related
Feb 27, 2012
I have a spreadsheet with several worksheets in it. The main sheet contains a calendar view that is fed from a separate sheet with holiday date ranges. This aspect works well and displays correctly in the main calendar view.
I am now adding in additional conditional formatting to each cell. As a first example i want the calendar to display bank holiday days by colouring the cell (lets say black). I am using conditional formatting only and have used the following formula;
IF(C6=Holidays,1,0)
Holidays is a named range, separate sheet (A2:A13).
C6 is the date value field (runs C6 through AN6 - perpetual calendar)
It evaluates the cell (i can see, TRUE,FALSE,FALSE,FALSE,...) and will only shade the cell if the date value field is equal to the first date in the Holidays named range. if this is not the case (FALSE,TRUE,FALSE,FALSE,...) the field value is set to "0".
how i can change this, entered as conditional format formula, to set the cell to "1" if any are evaluated as TRUE regardless of where they come in the name named range.
I want to additionally add in, from separate worksheets in the same workbook, face to face meetings and conference calls. I have assumed that i will use the same method to do this as Bank Holidays.
View 4 Replies
View Related
Apr 23, 2009
I use a query table to import data into a workbook which works fine
With wsActiveSheet.QueryTables.Add(Connection:=sQueryString, _
Destination:=wsActiveSheet. Range("A1"))
.Name = CStr("main.html?LANG=de&search=true&searchterm=" & sIdentifier & "_1")
.PreserveFormatting = True
.BackgroundQuery = False
.RefreshStyle = xlInsertEntireRows 'xlInsertDeleteCells
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebTables = "9,14"
.WebPreFormattedTextToColumns = True
.WebSingleBlockTextImport = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
One of the fields I import has a value like 100:01 or 10:01 or 01:01 or other combinations (the above ones are the most frequent ones).
Unfortunately excel always converts this field into a date and a time but this is not what I want.
Is there any way to stop excel doing this? I tried to work around it with the text property e.g. formating it to text after I imported the data and formating the entire sheet to use text before
View 9 Replies
View Related
Apr 8, 2009
I'm tracking weeks by weeknumber in the format YY-WW. At the moment I am entering the weeks manually in a general cell, preceeded by a '. I would prefer to have the sheet work out the weeks for me as we move the sheet every week. I would like to format the initial cell as follows:
09-08 (or '09-08 if I must)
then have the next cell in sequence read 09-09, 09-10, and so on. With the initial week in cell A3, I have tried to accomplish this with:
=LEFT(A3,3)&RIGHT(A3,2)+1
However, this does not work for weeks 01 through 09 because excel kills the leading zero in the second part of the formula, regardless of how I format the cell (format 00-00 does not work.) How can I force Excel it to keep the leading zero in the week number?
View 5 Replies
View Related
