Excel 2010 :: Enable Sorting A-Z In Locked Workbook

Jun 19, 2014

I have a workbook with a tab for each day of the month. This workbook is placed in a shared file and used by various yards in different locations. A new yard beginning the process is refusing to use it because due to formulas we must lock the workbook (contains many formulas and a live clock macro to track trip time) and they cannot sort A-Z. The sheets are labeled 1,2,3,4,5,6,7,8,9 and so on to 31. I know that you can write a Macro to allow sorting, but I cannot get it to work. the users CANNOT know the password due to hidden revenue etc. and formulas.

View 6 Replies


ADVERTISEMENT

Excel 2010 :: File Is Locked For Editing?

Jun 29, 2014

I tried to open excel file from shared drive but it's pop up

"File in use" xxxx.xls is locked for editing by XXXX, Open 'Read-Only' or click 'Notify' to open read-only and receive notification when the document is no longer in use.

I din't know how to insert the picture so that I wrote everything.

It happens almost every time even if file has not opened by any one. how can I stop it? Version: Excel 2010. OS Name: Microsoft windows 7 professional.

View 4 Replies View Related

Excel 2010 :: Enable Cell Locking Based On Value In Another Cell?

Jul 17, 2014

Is there a way in Excel (2010) to lock data from being entered into cells based on the value in another cell? Here is an example:

Row 4 contains dates from 2015-2030 as a header starting at column C (C4 and on) that represents years of revenue. Column B contains dates as well, this date can be any year but this becomes the driver for the data input in columns C through X. Column B contains the "Delivery Date"

Data is input in Columns C and so forth. The issue becomes that let's say that the date in C4 is 2016 and this is the beginning year. Obviously there should be no inputs for 2015 (C5) and the data should start to be input for this year at 2016 (C6). Often people just begin filling in the first available year not looking at what the year actually is.

Is there a way to add a formula or some logic/protection to prevent inputs in previous rows based on the values in column B? So if the value in column B is XXXX than there can be no inputs in cells less than that value?

View 6 Replies View Related

Sorting Data With Locked Columns

Feb 20, 2009

I have a spreadsheet on lets say 10 columns, now col 1 and 9 are locked so they cant be selected. Col 2-8 can be sorted by selecting them, but information in col 10 does not sort.

Question is there a way of connecting the cells in col 10 to the cell in lets say col 2, so thay when you sort the rows and cols between 2 and 8, the cells on col 10 are sorted as well As i have noticed you cant sort two differenr selections...???

View 2 Replies View Related

Excel 2010 :: Sorting Multiple Times

Aug 27, 2013

I have a sorting question in Excel 2010. Attached is an example workbook with a simplified version of the situation.

I want to sort a table multiple times. I have a table with part numbers and alphanumeric locations (Row, Shelf, Bay, Slot). I have formulas that divide up the location into 4 separate columns to be able to sort.

The first sort I do is by location, which I can easily achieve. The issue I have is sorting AGAIN by part number, while keeping the original sort somewhat intact. If a part number shows up multiple times (i.e. in two DIFFERENT locations), the Nth instance might show somewhere down the list.

Is there a way to sort my table to where you keep it in location order WHILE accounting for duplicates, which I would want grouped together in location order? Please see attached file : SortExample.xlsx

View 8 Replies View Related

Excel 2010 :: Sorting / Filter Dropdown?

Mar 25, 2014

I'm trying to add a sorting filter dropdown on some columns in a sheet. If I select the column, then use Data -> Filter, it adds the filter. on that column. But then, if I select another column, the "Filter" button in the tool menu is already selected, even though there is no filter for that column. If I click it, the filter on the other column disappears. Then, clicking it again adds a filter for every single column. It seems I'm not able to select f.ex. 3 different columns and add a filter to only those. It's either all or none. Is this how it's supposed to work?

View 2 Replies View Related

Excel 2010 :: Pivot Table Sorting With VBA?

Apr 10, 2013

Using Excel 2010

I have a workbook with multiple sheets.Sheet1 is named "UPC" and is a giant database for my workbook containing 80,000+ rows and 12 columns.

Sheet2 is named "Scan" and uses a bunch of index-match formulas. Basically, you scan a barcode and it auto-populates across the row all the information it pulls with that particular UPC from the "UPC" sheet. One of my columns, "QTY Scanned", in the "UPC" sheet has a countif formula to keep track of how many times that item was scanned on the "Scan" sheet.

Sheet3 is named "PSlip" and has a big button on it that you press and multiple macros go into overdrive. First macro takes all the rows from "UPC" with a quantity greater than 0 in the "QTY Scanned" column and copies it to the "PSlip" sheet.

Second macro takes that data and makes a pivot table.This is all clothing, pants, shirts, jackets, etc...When my pivot table is created, my Column Label is Size. When the pivot table is created, it automatically sorts it in ascending order, so it goes from numbers to letters.

This is great for pants because the size is in reference to a waist size; 24,25,26,27, etc...

HOWEVER, shirts, jackets, and other tops are not in a numeric size: they are in a Text format. XS, S, M, L, etc...

Sometimes we only scan pants for an order, sometimes only tops, sometimes both.

Is there a way for the pivot table to recognize when tops are in included and automatically sort the sizes?

I still want the numeric values at the beginning, but once the text starts it automatically sorts in this order:

XXS, XS, S, M, L, XL, XXL, MT, LT, XLT, XXLT, 1X, 2X, 3X, 4X, 5X

View 2 Replies View Related

Excel 2010 :: Sorting Different Sections Of The Same Worksheet Differently?

Aug 14, 2013

I am using Excel 2010 64-bit (with SP2 and all other updates installed).

I have a worksheet where some cells have a yellow background, based on conditional formatting of what is in column A. (Note that data in column A changes, so the row can sometimes be yellow and sometimes be white.)

I need to sort this worksheet as follows:

1. By background color, so yellow is on top, white is on bottom.
2. Then, within the yellow rows, data in column J.
3. Then, within the white rows, data in column A.

Currently, I do this manually by sorting the entire worksheet by column A, then selecting only those rows that are yellow and sorting by column J.

Is there a way to combine these steps, perhaps with a macro?

I initially thought to make each "background color" its own table, but since column A can change and thus the background color of a row can change, I need to be able to have rows move from yellow section to white section. I don't think having two separate tables allows that.

View 2 Replies View Related

Excel 2010 :: Sorting Multiple Values In Ascending Order

Dec 29, 2012

I am trying to sort multiple values in ascending order (example attached) however I can't get this to work. Tried looking at a few different forums and although there is plenty of sorting questions out there they all tend to be relating to dates not different values (i.e text and numbers).

View 14 Replies View Related

Excel 2010 :: Sorting Or Counting Based On Text Colour

Sep 10, 2013

I have a database of names in one column in excel 2010 and over time I have coloured the text of some of the names green to show that they have been added to a new online database. I want to be able to count how many of the names have been coloured so I can see if it matches with the total number already added on to the online database. I have over 800 entries so I don't want to have to manually count them unless I find I have missed any. Conditional Formatting is greyed out (it is a Shared document so that might be why).

in creating an IF (text is a certain colour) THEN 1, 0 or any others ways I can find the total in that colour. I only have two text colours in my spread sheet, black and light green.

Example of Table.JPG

View 1 Replies View Related

Excel 2010 :: Sorting Of Dates - Ascending Or Descending Order

Oct 25, 2011

Find the sorting method of dates in excel 2010. I have dates in one column and i want to sort it, but I am not able to sort in ascending or descending order.

View 1 Replies View Related

Excel 2010 :: Sorting Numbers In Descending Order And Related To Each Other

Jul 1, 2013

I wanna sort these numbers in descending order and related to each others in excel 2010.

H
G
F
E
D
C
B
A

1383.99
1533.954
831.197
1533.954
1533.954
1383.99
700
1533.954
1

[Code] .........

About descending and related to each others, I mean for example : #700 in column A,B,C,D,E,F,G,H places in the same row and the same happen to #1533.954 and others. About the numbers that are The One ( like 549.894), I need to put them in its column and a new row with empty cells in its other columns. In the end i want a table like this:

H
G
F
E
D
C
B
A

1533.954

1533.954
1533.954

[Code] .........

View 5 Replies View Related

Excel 2010 :: How To Stop Macro From Sorting Hidden Rows

Feb 3, 2014

I recorded a simple Macro in Excel 2010 to sort data by the first Column, heading "Ref". I have assigned CTRL + e as the keyboard shortcut for this.

My sheet contains a number of hidden rows (as I hide rows when I have finished working on that issue).

When I run the macro, it does sort by the number of Column A, but also, the hidden rows are then shown again.

I realise I could just click on the A-Z sort button to stop this happening, but I really like being able to use keyboard shortcuts, which is why I tried the macro route.

I wondered if it is possible for the macro to ignore hidden rows?

Code:
This is the macro:
Sub SortByRef()
'
' SortByRef Macro
'
' Keyboard Shortcut: Ctrl+Shift+E

[Code]....

View 1 Replies View Related

Formula To Enable Sorting Of Conditional Formatting

Jul 23, 2008

I have three conditional formats on my spreadsheet:

View 9 Replies View Related

Excel 2010 :: Sorting On Multiple Columns - Method Range Of Object Global Failed

Feb 25, 2014

I need a macro to sort on five columns (Column A, L, P, X, and Y) out of about 33 columns. Sort is all lowest to highest.

The code I have so far is:

Sub Macro1()
Range("A2:AG").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("L2") _
, Order2:=xlAscending, Key3:=Range("P2") _
, Order3:=xlAscending, Key4:=Range("X2") _
, Order4:=xlAscending, Key5:=Range("Y2") _
, Order5:=xlAscending
End Sub

However, I get an error "runtime error 1004 - method range of object global failed".

View 1 Replies View Related

Excel 2010 :: Close Second Workbook From Workbook BeforeClose?

May 27, 2014

I cannot make this work in XL2010.

I am opening a second workbook in ReadOnly in the Workbook Open and Closing it in the Workbook BeforeClose.

I don't EVER want to save changes on either workbook.

I need to leave excel open as the macro created a 3rd workbook which the user will need.

[Code]....

View 6 Replies View Related

Run Macro In All Excel Macro Enable Workbook

Jan 27, 2014

I want to run a welcome macro in all excel workbooks. Whenever I open any macro enable workbook the welcome macro should run.

View 9 Replies View Related

Excel 2010 :: After Applying A Data Filter And Sorting The Data / How To Revert Back To Original

Dec 20, 2012

I'm using Excel 2010 and I applied a Data Filter to a simple table. I then messed around with the drop downs in each column, sorting the data by different criteria. After doing this, is there a simple way to get the table to revert back to its original order/form?

View 3 Replies View Related

Run Macro From Another Workbook Locked

Feb 2, 2008

I have one spreadsheet that is locked and I cannot modify, with a button I want to press from a fresh new one that I have created.

The first one is private and cannot modify it.

Now, what code should I use in mine so I can "click" the button of the other one when I press it?

View 5 Replies View Related

Excel 2010 :: Pull Value From One Workbook Into Another

Aug 6, 2014

Using Excel 2010. I have 2 worksheets, saved in same folder (if that matters).

Worksheet 1=Master_List.xlsx
Column1
Column2
Column3
Column4
PartNumber

1002
Sony
Playstation
4D
W74859

Worksheet 2=Sample_List.xlsx
Doesn't Matter
Doesn't Matter
Doesn't Matter
Doesn't Matter
Doesn't Matter
Column1
Column2
Column3
PartNumber

Doesn't Matter
Doesn't Matter
Doesn't Matter
Doesn't Matter
Doesn't Matter
1002
Sony
Playstation

Basically, I'm looking to populate the blank cells in the PartNumber column (Sample_List) by looking at the data in Column1, Column2, Column3 then going to the Master_List, matching up Column1, Column2, Column3. The PartNumber value in the row with the matching values would then be copied to the Sample_List.

View 4 Replies View Related

Excel 2010 :: Paste Link Chart From One Sheet Of Workbook To Another Sheet Of The Same Workbook

Jul 20, 2014

I've created a chart in sheet 1 in a workbook. I want to copy and paste that chart into another sheet (lets take sheet 2) of the same workbook. I am using Excel 2010 version.

Whenever, I try to copy a graph and want to do "Paste Special as Link picture". The problem I am facing as "Paste Link" option is inactive.

I am attaching the Excel for your reference.

View 2 Replies View Related

Creating A Secure Locked Workbook

Oct 11, 2005

I want to create a completely uneditable locked workbook.

I know how to do the simple stuff like unlocking cells for the purpose of entering data values and locking the rest of the sheet.

What I want to do is also lock out users from changing headers, footers, etc.

Ultimately, what I'd like is, when the file is launched by the user, all that is displayed is the sheet with no toolbars and no menu bar. Finally, the last thing, is to have 'print' button (with obvious macro built in).

Simply put, this is an electronic version of a paper worksheet, so no saving is required either.

View 9 Replies View Related

Excel 2010 :: Run Macro In Another Workbook Without Copying It?

May 21, 2014

I used the button in Excel 2010 to record a macro that allows me to format a workbook font and stuff because i do that many times a day for several workbooks. However I want to be able to just push a button and have all open workbooks run the macro I recorded.

I am able to make a button- thats easy, but how do I get it to run for ANY workbook? My problem is that apparently it only runs the workbook that is named a certain way. should i post the code here?

View 1 Replies View Related

Excel 2010 :: Unfilter Workbook Before Saving?

Feb 16, 2014

In order to unfilter a workbook before saving, that is often left by the users with filters on, I use the following code:

[Code].....

The code works just perfect for a workbook that has a table in a normal range of cells.

If I insert a table (using Office 2010 and later option), the code simply doesn't work.

View 5 Replies View Related

Excel 2010 :: Compare Two Spreadsheets In Same Workbook

Nov 17, 2011

How to compare two spreadsheets in the same workbook in Excel 2010.

View 4 Replies View Related

Excel 2010 :: Macro Does Not Exist In Workbook

May 8, 2013

We have recently converted to Office 2010 from 2007 I have now got a name conflict with some macros on my templates

I have some code which will open up the affected templates delete the required modules & import the amended modules, this is working OK.

I now have to re assign the new macro to each button on all pages on all my templates., I started of by recording the actions to achieve this (see part of the code), but unfortunately the recorded code will not work, it runs but when I test the buttons I get the message that "this macro does not exist in this work book".

Code:
Sheets("page 2").Select
ActiveSheet.Shapes.Range(Array("Button 255")).Select
Selection.OnAction = "DIAM01"
ActiveSheet.Shapes.Range(Array("Button 1549")).Select
Selection.OnAction = "DIAM02"

[Code] ...........

View 2 Replies View Related

Excel 2010 :: VBA - Select Last Sheet In Workbook?

Feb 19, 2014

Excel 2010

My macro cleans up data in worksheet "Chalkboard".

After cleanup, I'd like to copy data and select the last sheet in the workbook (the one on the far right) and paste the data.

The last worksheet changes monthly: Jan Feb Mar Apr, etc.... Meaning each month, I'll add a new worksheet for the given month.

I need code that will select the last worksheet in the workbook.

View 1 Replies View Related

Excel 2010 :: Save Workbook As XLTM?

May 17, 2014

Windows 8.1 Excel 2010, I open a Template, make some changes in the template then I want to save the modified template as a Template ".xltm" under a new name. I'm using the following code for the new workbook name (which works except for the file extension);

ActiveWorkbook.SaveAs Filename:=ChkLst_TEXT_path & Application.PathSeparator _
& "NewChkLsts" & Application.PathSeparator & ShtNm

By default it saves the workbook as ".xlsm". How can I get it to save it as ".xltm"?

View 1 Replies View Related

When Protecting Workbook With Pw, Unlocked Cells Are Still Locked

Dec 16, 2009

I have had a workbook that has 2 sheets.

When I protect the main sheet with a password, the unlocked cells are also locked.

When I move my cursor over any cells, it has the pointed finger as though it has a macro over it, which it doesn't.

When I click on any cell, the screen flashes and a large part of the screen momentarily it looks like a selected area.

I also am using Freeze Panes. Removed the Freeze and still no good.

If I use the horizontal scroll bar and click the right arrow 3 times, they become unlocked.

If I move the screen 3 clicks to the left, it re-locks all of the unlocked cells.

View 9 Replies View Related

Excel 2010 :: View Same Workbook On Dual Monitors

Apr 9, 2014

I believe that someone showed me once that you could view the same workbook on two monitors with the vien arrange all mode, you could make changes on both screens and the changes would be saved to the same file similar to arranging two views. I cannot rememebr how to do this or even if it is possible.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved