Large Spreadsheet With Conditional Formatting
Apr 3, 2009
I have multiple large spreadsheets of questionnaire data. Responses are as %'s
What I would like to do is use conditional formatting to pick out the top 3 of each set of questions per column not including 0% (if it falls in the top 3). Using different shades of green in the cell background and a white font.
so.....
The spreadsheets are up to 3000 rows long and up to column BB wide. The sets of questions are of random length spaced by an empty row. So question 1 may have 11 parts to it, each taking up a row so 12 rows in total. Question 2 may have 5 parts taking up row each so 6 rows in total...............Question 80 may have 7 parts so 8 rows in total and on and on.
What I have been doing is working down the first column of responses and manually adding the conditional formatting. Then using the format painter to copy across the relevant rows and columns. For example if Question one was from c5:bb26 I would format c$5:c$26 with the top three then use format painter to drag across to bb5:bb26 ...
View 9 Replies
ADVERTISEMENT
Jun 25, 2013
I have the attached spreadsheet which makes the question easier to understand. Basically when you read across and down rows and columns, I'd like the intersecting cell to turn red if the horizontal and vertical row columns letters are different. So in the attached B5 is red because B1=A and A5 is B. Where the intersecting cells match in values, the cells will stay white.
I can create a formula to conditionally format one cell to red: =A2<>B1
But I have a large grid and it would mean re-creating each formula for each cell. How to expand it to the whole range?
View 14 Replies
View Related
Jul 28, 2009
I'm trying to get working is that I want it to auto-adjust what holes I get shots on if and when my handicap changes. This would work something like this:
I have a sheet with the course score card mapped out. Holes 1-18 etc with the stroke index of each hole (level of difficulty - i.e. hole 1 might be stroke index 14, hole 2 stroke index 6, hole 18 might be stroke index 18 etc etc)
I also have a sheet with my handicap cell which I can enter and change accordingly.
On this same sheet are my scores, with holes 1-18. The way I'd like it to work is that if my handicap is currently 8 holes that are stroke index 1 to 8 are given a red (or whatever colour) border. The rest are left alone. If my Handicap changes to say 6, I enter 6 in the handicap cell and this automatically adjusts so that holes with a stroke index 1 to 6 are bordered in a colour.
View 5 Replies
View Related
Jul 22, 2009
I have a spreadsheet that tracks Auditing Dates. Cell A1 has today's date =Today()
Column B2 has the first Audit Date (hard keyed), cell C2 has the second Audit Date (formula =+B2+182), cell D2 has the third Audit date (formula =+C2+182), etc. . . I would like the format of the Audit dates to flag the last audit date in the row red if it is prior to today's date (cell A1).
View 9 Replies
View Related
May 29, 2014
I have a very large spreadsheet that was exported from an ecommerce site with close to 1000 products. I have one column that I need to extract some text from. This column holds all of the html from the product description and is huge. I only need to extract the actual description of the product, but am having a very hard time figuring out how to do it. I've tried using the mid, left, and right function; but not all of the html is the same so it's not really working the way I need it to.
I have multiple tags throughout the html that I can use with the mid function, but there is more than one occurrence of them. So, how can I tell it to start at the 4th occurrence? I've spent countless hours searching, but I'm a complete novice when it comes to excel and I don't even know what to search for. I end up looking through sites that explain how to pull the Y out of XYZ, which is what I need, just on a much larger (and more complicated) scale.
It was suggested that I set up a macro that will find the 4th occurrence of the word, and then uses the mid function to pull the data out, but when I try to find the word, it says it doesn't exist even though I can see it right in front of me.
View 10 Replies
View Related
Jul 10, 2013
I need to be able to convert a large spreadsheet to pdf for sending to a commercial printer. The spreadsheet is a family tree and covers an area a1 to ca107. The printers normally do the conversion from an emailed copy of the .xls file but charge for this.
Printing will be done on a wide carriage printer so the pdf has to be one sheet not a large number of a4. I have a copy of Acrobat but cannot work out how to get the spreadsheet to save as one sheet.
View 7 Replies
View Related
Mar 12, 2009
I have created a sample spreadsheet (the real data is sensitive) but I am basically faced with a spreadsheet of many columns of which I would like to remove the 4th,5th, 8th,9th, 12th, 13th etc...
So every 3rd column starting from Column B, and also every 3rd starting from Column C, (every 3rd, and every 3rd + 1 starting from B?), it's obvious what I'm after from the spreadsheet I've provided.
Unfortunately manually isn't an option, this needs doing every week, and it goes all the way across to column HZ!
View 2 Replies
View Related
Jan 6, 2010
Need the code to put into a command button which jumps from the cell selected, X number of cells to the right. Also will need the code to jump back again when clicking on a different command button.
View 5 Replies
View Related
Mar 1, 2002
I generated a large spreadsheet and now for legal reasons all the data has to be in Upper case. Is there a quick way to convert all the text.
View 6 Replies
View Related
Oct 3, 2008
I have a large spreadsheet with over 50 columns. Many colleagues use the sheet and often use the format, hide, column function to hide columns they do not need but then the next user has to unhide them and re-hide others until they are left with the different columns they require.
Is there anyway of creating a menu which allows users to select from a list of all the columns those they wish to display, all the others therefore being hidden.
View 5 Replies
View Related
Mar 2, 2013
I'm have Excel 2010 and Windows XP. Each week I get 3 spreadsheets and each has data unique to it. Each spreadsheet has a week number column which is common to all three. I want to combine the three worksheets into one and make create several dynamic charts for management. How should I organize a large spreadsheet? In addition to my week number I have a host of other date fields. Some of the data I get is (1) vehicle VIN numbers (2) City/State/Country (3a) I break up the VIN to give me vehicle type (3b) year of assembly (3c) car type (3d) number of doors (4) mileage (5) complaint (7) defect code etc. etc. What are the do's and don't when setting up a large spreadsheet? I have data by week which goes back to 2006 and so my date fields go top-down. I inherited the three spreadsheets and would like everything under one roof, so to speak. One spreadsheet which I can make my charts.
View 1 Replies
View Related
Nov 11, 2013
I am creating a spread sheet with a large amount of data.
In column a I have a list of managers, in B I have a list of employees and C, I have a list of activities that have been recorded throughout the day.
What I want to do is create a list (on a new sheet) so in column A that would bring back all the managers. Then in column B, bring back the employee that is tied to the manager from column A and finally in column C, bring back the activity that is tied to the employee.
I know how I can do this by doing list validation with INDIRECT, but with the large amount of data that I have this would take forever in a day.
Is there an easier and quicker way to do this?
View 1 Replies
View Related
May 22, 2014
I have a large spreadsheet which contains multiple rows of rate data by employee. I have sorted by effective date with most recent date on the top to get the most recent rate of pay, however, is there a way or formula to be able to identify or extract the previous rate of pay after that? I have included a sample below. There are different #'s of rows of rate of pay based on how long someone has been in company, as well as different rates of pay and effective dates.
Employee #
Name
Rate of Pay
Eff Date
655220
John Smith
$ 99,000.00
1/1/2014
[Code] ..........
View 3 Replies
View Related
Oct 3, 2008
I have a large spreadsheet with over 50 columns but usually only need to view a small selection of them. Many colleagues use the sheet and often use the format, hide, column function to hide columns they do not need but then the next user has to unhide them and re-hide others until they are left with the different columns they require.
Is there anyway of creating a menu which allows users to select from a list of all the columns those they wish to display, all the others therefore being hidden.
View 9 Replies
View Related
Jun 19, 2007
I have a large spreadsheet (1000-plus entries) that all have about 10 or 15 columns of information (product title, product number, etc).
I also made a form that I want to use as a master form, where all the titles will not change.
What I am wanting to do is, by going off the product number, when you click it, this pre-made master form will pop up, and all the information that was located on the large spreadsheet for this product number will be inserted where it is designated to be on the master form.
While I could probably do something like this through Access, we need to keep it on Excel, and in the same workbook so that it will automatically update things once I put a new number or input onto the large spreadsheet.
View 12 Replies
View Related
Jan 31, 2014
I work with Excel 2010 and have a very large spreadsheet with data that I need to manipulate in several different ways. I have been filtering and then cutting and pasting but this is very time consuming . Is there a way to extract specific data from the spreadsheet and transfer it to different worksheets? I don't really know how to use macros.
[URL] .....
View 14 Replies
View Related
Feb 27, 2014
i have some large reports that i get each day and have to sort into date time order and only keep the newest occurance based on column 2
at the moment i have a vba that converts the list to space delimetd columns, then selects all sorts into date time order, then deletes dupliactes as of column 2 keeping the most recent,
is there a more efficient way just to keep the most recent occurance and removing the rest
example file. so the 2nd row would be what i am left with
0 zztimrdevappg01 tbbzz02/26/201419:04:30
3 zztimrdevappg01 tbbb bb02/27/201402:04:31
8 zztimrdevappg01 hhbbzz02/26/201419:04:32
0 zztimrdevappg01 hhbbbb02/26/201419:04:34
View 5 Replies
View Related
Nov 16, 2011
I have inherited a series of Word reports containing links to charts in several Excel files. I can tell by the link in Word that one of the charts is named 'Chart 6'. Is there an easy way to locate which chart in the Excel file is 'Chart 6' (and on and on for all of the other charts linked in the Word doc)? There are upwards of 21 charts in each Excel file linked to each of the Word reports. I am using 2007 but the files are in 2003 format because of limitations of the program from which the data comes for the charts.
View 4 Replies
View Related
Mar 12, 2014
I have a very large spreadsheet that is a consolidation of approximately 105 separate of contacts lists. The new consolidated spreadsheet contains demographic data, and a column for the name of each of the 105 lists. Each contact in the consolidated list had an "X" placed in the column if they were a member of that particular list. Each contact can be in more than one list. This is not very useful for me.
Here's a visual representation of the current list along with what I need:
FName
LName
Address
Phone
City
List1
List2
List3
List4
List5
List6
[Code] .....
I would like to add a column to the spreadsheet and call it "Source"
And then have a macro or VB script to do the following:
1. As shown above, put the column name (ie "List2") in the "Source" column whenever there is an "X" in that row for the corresponding Column.
2. Because each contact can be in more than one list, I need to append the column names so that "Source" column contains all of the column names (Lists) that had an "X"
3. Once all of the list names have been added to the "Source" column, I will delete the list columns.
View 1 Replies
View Related
May 15, 2007
I have a large order spreadsheet with 3 date related columns. One for customer requested delivery date, one for factory quoted delivery date and one for actual delivery date.
I want to be able to set something up that if it gets near either the requested or quoted delivery date (by say a week), that the cell flags red so it's a visual reminder to check whether the order has been dispatched etc.
View 9 Replies
View Related
Sep 7, 2012
How do I make the column headings appear when I'm working on a large spread sheet so I don't have to scroll to the top each time to see where I am. This is on Excel 2007.
View 5 Replies
View Related
Aug 25, 2009
I have a large spreadsheet which I need to split into individual workbooks by reference to a particular column.
Rather then doing this manually and splitting the data out one at a time - I would like a macro to do this for me.
I am a novice excel user.
View 9 Replies
View Related
Jun 17, 2014
I have an issue where I need to apply certain formats to a number of text strings which are domain names. This is purely for reporting purposes. I don't need to actually visit the url. This video demonstrates a bit more. [URL]
View 1 Replies
View Related
Sep 16, 2013
A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
dust 1.xlsx
View 2 Replies
View Related
Mar 20, 2013
Is 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???
View 3 Replies
View Related
May 5, 2009
Is there "code" for different formatting in a spreadsheet so you can use an IF statement to do something like:
if(A1=blue background,"Yes","No")
or
if(A1=red text,"Yes","No")
etc.?? But replace "blue background" and "red text" with some sort of number code? I want to compile a list of the items that are formatted with certain background colors and/or text colors and then organize only those items into a chart.
View 5 Replies
View Related
Jul 5, 2013
Any VBA that I can put into a macro that will convert conditional formatting into fixed formatting..? So when the cell contents/formulas are deleted the formatting remains. Assume that the range I want to convert is A1:D200...
View 9 Replies
View Related
Feb 9, 2009
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 Related
Aug 20, 2014
When I attach a 1 page spreadsheet to an email and send it to my boss, the spreadsheet is no longer on one page when he opens it. What causes this and how can I fix it on my end or fix it from his end to keep the same format?
View 2 Replies
View Related
Jul 18, 2007
We have a list of numbers in a spreadsheet (telephone numbers). The numbers have been put in with () around the country code and hypens separating the rest of the numbers - for example (44) 0111-123456
Is it possible to remove the formatting to just read as a full number e.g 0111123456? I have loads to do and the thought of doing it manually is painful!
View 10 Replies
View Related