Cell Formatting -Sheet Agreement When Sorted
May 29, 2009
I am trying to reference a cell in sheet All Employees in sheet Brock. This is an employee training database.
Currently there is no data in the cell I want to reference (B9).
When I use the formula ='all employees'!b9 the cell in the brock sheet shows "january 00 00" even when the referenced cell is empty. This problem is eliminated if I formate the Brock cell to General. It then displays a zero.
However, the cell in the All Employees Sheet is going to be a date. If I format the Brock cell to general, when I enter a date in All Employees, it gives a large number, not a date. This problem is eliminated by changing the formating of the Brock cell to Date.
What I would like to do is be able to type the date in the All Employees sheet and have it magically transfer to the Brock sheet without any additional work. I would like the brock sheet to be printable, and with January 00 00 written all over it, not only is it messy but its incorrect.
View 9 Replies
ADVERTISEMENT
Jan 9, 2014
I know how to reference cells from one sheet to another but for some reason I cannot for the life of me work out how to do this and that particular figure remains even after sorting sheets? Basically every time I reference some numbers its fine but when I sort the original sheet being referenced the figures are all wrong. I tried linking the cells and that didn't do it either?
View 5 Replies
View Related
Sep 12, 2013
I have a simple sheet consisting of about 900 rows and 4 columns. The cells in column D are filled with numbers. I converted column D to a table and sorted to show only numbers over 10,000. I can now see the results including the cell values for rows A,B,C, and D. I would like to delete the rest of the unused rows, or create a new sheet with my results. Right now, some row numbers are not visible, so the visible rows go from 4, 27, 45, 48, 52, etc. When I try to copy and paste what I see in my results into a new sheet, the entire original sheet still pastes.
My final goal is to just have a sheet with 4 columns, and the corresponding values as long as the values in column D are over 10000. I do not need a table, just a regular sheet.
View 3 Replies
View Related
Jan 28, 2014
I was wondering if there was any way to do a cell format so that if I type in a word in sheet1 say "mike" on sheet2 the word "mike" cell would be highlighted? or if there is a formula you could use to do that.
View 14 Replies
View Related
Dec 12, 2013
I am getting a debugging error when I try to highlight the first row of a column that is sorted. the error occurs on the "if .autofiltermode then" line. the code does highlighting like I want but is giving me a run time error.
HTML Code:
Private Sub Worksheet_Calculate()
ColorAutoFilter
End Sub
[Code]....
View 1 Replies
View Related
Dec 6, 2006
how to filter rows of data, in ascending or descending order, whilst still retaining formatting (in my case cell borders) and conditional formatting?
At present, when I filter rows in ascending / descending order, the cell formatting & conditional formatting stays in it's original position, rather than moving with the cell. I can't find a way to resolve this.
View 9 Replies
View Related
Nov 7, 2013
I have a worksheet with a list of four digit codes that are colored. On a separate worksheet (in the same workbook,) I would like to be able to type the four digit code in, and have it return the color I have the cell turned. E.g., I have a cell with 8810 colored green on one sheet, and I need to be able to type that code on another sheet and have the cell turn green. Is this possible? The codes are all random, with three different colors to choose from, so conditional formatting is probably not the best solution.
View 5 Replies
View Related
Jun 22, 2008
I have locked some cells on my Spreadsheet so other people can not change them. What I would like to know is how do I change the colour of the font? I also would like to Bold/unbold the font.
View 6 Replies
View Related
Feb 11, 2010
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.
View 5 Replies
View Related
May 25, 2014
First off I have an excel sheet that I have split into two windows. excel sheet.jpg
I am looking for a formula that will change the bottom sheet number a color if it exists on the top sheet.
View 8 Replies
View Related
Mar 29, 2009
When it is sorted by ascending and next time when they click header I want it to be sorted by descending.
Right now I have implemented to sort Descending . Now how will I check in which order does it sorted?
If know the order in which it is sorted currently then I will sort it in the opposite way..
View 6 Replies
View Related
Mar 5, 2013
I have a data set of about 600 records. I want to be able to extract the Top 10% of the records and get rid of the other 90% - the values can change over time, so I always want to start with the full 600 records.
I am aware of the conditional formatting function that will identify the Top 10%, but I was looking for VBA language (does not have to be VBA - just automated), that calculated the 10% subset and either moved it to a different sheet, OR deleted the bottom 90% - in either instance the result being just the list of the top 10% of the data set.
View 9 Replies
View Related
Sep 19, 2006
I have a list of entries on one sheet. This sheet contains part numbers in random order. I would like to draw out the part numbers onto a new sheet, with no repeats and in numerical order. I want this to be done automatically without using a sort command. Also, I would like to avoid VBA if possible.
View 8 Replies
View Related
Apr 21, 2013
I have an issue sorting values. i can get the numbers together and sorted, but want to keep a name associated with the values being sorted. so when the values are sorted, and they change the name associated with each value moves with it. Its acutally a first-tenth place in a nba fantasy league.
View 7 Replies
View Related
Jan 8, 2010
Does anyone know of an Excel object class that you can put objects into with a key and the process it in key-sorted order? If not, is there a library that I can reference to get such a thing?
View 6 Replies
View Related
Oct 27, 2011
Is there a way to run a macro after a worksheet is sorted (after sort event)?
Scenario: I have a several hyperlinks that use defined names to jump to locations throughout a worksheet. When the sheet is sorted the defined names don't update to the new cell locations (Microsoft KB 58245). I've written a subroutine that fixes the defined names but I don't know how to have the fix run automatically.
I prefer not to use Worksheet_Change without other conditions to prevent a lot of unnecessary calls to repair subroutine.
View 1 Replies
View Related
Sep 20, 2013
Explaination of Data:
I have 2 Columns in the "RawData" sheet in the file attached. One says the Campaign No, and the other says the Site Code. One Campaign, will always never have duplicate Site Code.
I want to organize the data in the following fashion (as seen in the "Format" Sheet). I wish to acquire the trends of grouping of Site Codes, meaning which site (by Site code) is sold together, to understand the hottest combinations.
The idea is to see which Site Code sells more with a particular Campaign.
As you can see in the attached file, I'd like to know how many times a particular Sites (by Site Code) was sold with the other. As you can imagine, I have about 300 Campaigns, but have about 1500 different Sites codes to deal with, this activity will save me hours of time.
The sample file is uploaded on Google Docs. [URL] .........
View 1 Replies
View Related
Aug 19, 2008
I have data in a spreadsheet that need to be re sorted. Here is the data:
Name Address Address Address Phone
XYZ 123 Main NA WI, 12345 123-123-1234
ABC 123 South NA AZ, 1111 456-456-4567
is there a way (i.e marco) that can reorder this info to look like this?
XYZ
123 Main
NA
WA, 12345
123-123-1234
ABC
123 South
NA
AZ, 1111
456-45-4567
View 9 Replies
View Related
Apr 21, 2009
Is there any way to determine whether a column is sorted? Like a property of a column like ".ascending" true or false?
View 9 Replies
View Related
Jul 3, 2009
Still i could manage with Sorting of data. Now i have following data
Name Project Artha Artha Prj Artha Artha Prj PBB ACR Seema Brew Seema SEMC Seema handset dev & testing Seema SEMC Seema handset dev & testing Shashi PRJ1 Shashi PRJ1 Surajit FLV Surajit FLV Surajit FLV
And i want to have following result using a macro
Name Project Count Artha Artha Prj 2 PBB ACR 1 Seema Brew 1 Seema handset dev & testing 2 Seema SEMC 2 Shashi PRJ1 2 Surajit FLV 3
View 9 Replies
View Related
Mar 22, 2013
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.
View 3 Replies
View Related
Jan 27, 2009
I have an Excel sheet with a list of data, which is likely to change frequently and has to remain in sorted order.
Requiring that users of the spreadsheet maintain the data in this way (i.e. sort it every time it changes) is a really bad solution and I'd rather not use a macro if I can avoid it (too much stuff can go wrong).
Is there a good way of doing this? At the moment, I've implemented merge-sort within a sheet, which works, but is a heavyweight solution, taking 6 columns per merge-sort iteration (so 60 columns to sort 1024 rows, 96 columns to sort 65536 rows). I could try to compact it, but my head was starting to implode programming it even with as few as 6 columns.
There must be a better way of getting Excel to maintain an automatically sorted list.
View 11 Replies
View Related
Aug 22, 2013
I have data that I need to be dynamically sorted in the highlighted manner. The values on the selection criteria are dynamic.
View 14 Replies
View Related
Jan 4, 2008
I want to copy the first 30 rows of a autofilter list can someone point me in the right direction?
View 13 Replies
View Related
May 1, 2009
Dependent Dropdowns from a Sorted List:
i have one file here..
how did they created validation here.
is there any macros?
i tried..but i didnt able creat like this.
i got this file in this link.
http://www.contextures.com/xlDataVal13.html
i saw this problem in this post..
http://www.excelforum.com/excel-prog...hierarchy.html
View 6 Replies
View Related
Jul 13, 2009
I have been struggling to figure this out for several days now and i don't really know where to go from here.
I have 2 columns and i need to break them up at certain points.
The attached excel file should hopefully be clear enough to show what i mean.
The first tab is how the data starts the second tab is how i want it to look.
To clarify i need to break it after each change in the first column. ie from 1 to 2.
Furthermore, i need to break the "sub groups" the second column up after 15, and 30. I will never have a number larger than 32 in the second column.
I think the excel file will clear up what i mean.
View 13 Replies
View Related
May 13, 2009
if i have a column of number that is not sorted(it cannot be sorted due to other requirements), is it possible to find the cell that reflects the nearest number that i want to look up?
View 4 Replies
View Related
Sep 28, 2011
I have a list of strings as such
A1: A
A2: B
A3: D
A4: E
How can I check to see if this range is sorted with vba?
I have searched but havent found anything for strings.
I can only think of adding each cell to an array, creating a copy array, sorting the copy array and then comparing the two arrays. I want it to return TRUE, FALSE or 1, 0
View 3 Replies
View Related
Apr 27, 2012
When clicking on the sorting button of filtered data near the top of a field, I can select different things to show or not show. In a date field, it starts to arrange things by year, which can be expanded to months, which can further expand to show individual days. All well and good.
But many of my dates do not show up in the year categories. They are instead shown below the years as individual dates. Furthermore, sorting the date field in question is imperfect for these dates. Here is an example where some dates from april are jammed inbetween others:
4/2/20124/2/20124/2/20124/2/20124/20/20124/20/20124/20/20124/20/20124/20/2012
...
4/27/20124/27/20124/27/20124/27/20124/3/20124/3/20124/3/2012
These jammed dates are some of the same ones that are orphaned outside the year groupings. All the dates have the same date cell type. No other filters are applied.
View 2 Replies
View Related
Mar 9, 2008
I have a matrix of mixed numbers (going across many columns and rows) in Sheet1. They are a mix of positive and negative numbers, In Sheet2 I need 2 lists to be automatically displayed, taken from the data in Sheet1.
The first list (column) is for positive numbers only, the second is for negative numbers.
There is one filter: only numbers over a specified value (for the positive list) must be used from the data, and only numbers under a specified value (for the negative list) must be used from the data. The 2 filters can be stored in any cells in Sheet2 (the worksheet where the lists will be), lets say, for ease of use, A1 and B1.
But here's the tricky part: I need the location reference of each number in the matrix displayed next to the number in the list!!! Eg: in the matrix, I have names running down the left (A2 to A25) and the same names in the same order running across the top (B1 to Y1) - just like any basic matrix! If A6 is called "John" and L1 is called "Pete" and their value (at cell L6) is 10, then I need them to be displayed in the "positive list" amongst the others (sorted automatically into numerical order) with the words "John" in the cell to the right of the score, and "Pete" in the one after that to the right: so it will look like:
A2 B2 C2
10 John Pete
(remember that these 2 lists are on Sheet2, not on the matrix worsheet! A1 and B1 contain the 2 filters for the positive and negative lists).
Of course, this must only happen if the filter in A1 (the positive filter) is 10 or under. If it was anything above 10, then this one would not make the list.
As for duplicates in the matrix coming up twice in the lists, that's not a problem this wont matter! It's ok for the lists to have constant duplicates displayed eg: 10 John Pete will also have next to it 10 Pete John.
View 9 Replies
View Related
