Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

show only open issues on sheet2

My spreadsheet is meant to track property management issues.
Column M is called "Status"

How can I show only the Open issues on sheet2.

sheet1: column M <> "closed"

Thanks for any info about how I can do this.
Rudy


Post your answer or comment

comments powered by Disqus
I currently have a workbook that has 72 worksheets in it. 2 of these worksheets are table of contents. One is for 2006 and the other 2007. I need both tables of contents to show when opening the workbook. I used the code below, in the ThisWorkbook section of the VBA code, when opening the file to hide all sheets but Table of Contents. Now that we are in a new year I need both Table of Contents to show and the rest should be hidden. Can some help me modify the code to get this working?

Thanks


	VB:
	
 Workbook_Open() 
    Dim c As Worksheet 
    For Each c In Sheets 
        If c.Name  "Table Of Contents - 2006" Then 
            c.Visible = False 
        Else: c.Visible = True 
        End If 
    Next c 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Can anyone help me, I want to be able to type a nem in one cell and be able to click search and it will look up an tell me where that name is in a particular Colum, like th e search feature but so that it will show only that name on the sheet, then I can press reset or something simular and the sheet will go back to all names.

Is this possible at all??

Shazz

Hi all, is there a way of capturing either the User name or Machine name that the workbook is being opened on?, i would like to send a workbook to some people but do not want to allow them to open the workbook on any other machine than their own so if their machine is called Office1 then they can only open it on there else close the workbook, all the network users are narrowed to being able to log on on one machine only i do not want them taking the workbook home!, single machine users outside the company i would like them to only open the workbook on the machine that recieved the e-mail with the workbook in.

probably an impossible task..........but maybe just maybe one of you may have an angle on this!

Regards,
Simon

Hi,

I do'nt know really my problem is regarding excel or with windows, actually stange thing starts happening, I open multiple excel files but only one general excel instance is showing on the task bar. If I need to switch between the file I have to minimise the one I am using and then select other to open it. Even shortcut Alt+Tab switch is not working to switch between the excel files. ohh strange I pull my remaining few hair out ......
Please help me

I am trying to help someone figure out why their new workbooks open on Sheet2 instead of Sheet1 when they create a new workbook.

Is there any option to uncheck that may start the new workbook on Sheet2 or could that only be happening with a Workbook_Open event?

I do not have access to the workbook to be able to check myself.

Thank You
Ken

Have 2 sheets, on Sheet1 there are 20 rows, each row is a product. Sales people enter of # of units for 1 or more products. Normally there are only 2-6 products sold of the 20 rows.

On Sheet2 only want to display the entire rows from Sheet1 where the cells in Column headed "# of Units" is greater than zero and this may be different on each use of Sheet1.

I want the rows on Sheet2 to appear on consecutive rows-- meaning if only 3 rows on Sheet1 have "# Units" greater than zero, only 3 rows will be shown on Sheet2 and those 3 rows will be consecutive.

It is getting the rows from Sheet1 to appear only in consecutive rows in Sheet2 that stumps me.

This is for Salespeople quotes to prospective customers.

Hello. There are several discussions about that topic but I'm not able to get my whole idea working based on these (some really good) peaces of knowledge. So I decided to ask help here.

I am trying to have an excel workbook after opening to show only certain sheets for one user, some other sheets for second user and so on. This is not based on security need, but for convenience and to prevent accidental mistakes:

- On open the workbook should show only one sheet (Login sheet) and a userform for inserting the users password.
- This userform should check from the data set in another hidden worksheet, if the entered password is valid. This hidden sheet contains all users UserNames, Passwords and SheetNames (multiple sheets for each user), that are to be shown when the user enters his password.
- On closing all the sheets, except Login sheet, must get "very hidden" again.

Hope I made my wishes understandable.

Any help and guidance would be greatly appreciated. Thanks!

Until this morning, when I have a number of workbooks open, they showed as separate tabs on the start toolbar at the bottom of the screen. So, I'd have Outlook, then a button for one workbook and another for a different workbook.

This morning, they're only showing as one instance of Excel. It's a pain because I used the buttons to see what I had open rather than flicking through the open workbooks.

Can I get them back as separate buttons/tabs on the system toolbar, and if so, how? I'm using Excel 2000 and Windows NT ME (sadly!)

I have a situation whereby I have one set of records on sheet1 (1 rec per
row) with a different set of records on sheet2. While there is only one
record per client (1 row) on sheet1, there are one or more associated records
on sheet2.
I want to allow the user to click on the key field cell for a record on
sheet1 that will open sheet2 and display only those records (1 or more rows)
with a corresponding key value.
EXAMPLE:

SHEET1 SHEET2
1 1
2 2
3 2
4 2
5 2
3
3
4
5
5
The user clicks "2" on sheet1
This opens sheet2
sheet2 displays those rows with "2" in the key field (4 rows)

Can anyone help me?

Suppose I have the following data set:
(x-axis) (y-axis)
Date Price
01/01/03 $10.00
02/01/03 $20.00
03/01/03 $30.00
04/01/03 $40.00
05/01/03 $50.00
06/01/03 $60.00

I make a simple x-y line chart and it shows all the dates on the x-axis and the price on the y-axis. Say I only want to show the 2nd, 4th and 5th date on the x-axis. Is it possible to "turn-off" the rest of the text on the x-axis. It seems that excel treats the x-axis as a whole (a category axis object) and doesn't allow you to change the attributes of it's "sub-objects" such as the text of the 2nd, 4th and 5th data points. Is this assumption correct? Is there a way to get at the sub-components of the category axis object via vbe? Any help/insight would be appreciated.

Hi. I was hoping for a little help coding this.

I'm using this to hide and show ranges on my sheet1

If Range("Blood").EntireRow.Hidden = True Then
        Range("Blood").EntireRow.Hidden = False
    Else
        Range("Blood").EntireRow.Hidden = True
    End If
I want an onchange even i think its called.

That as soon as "Blood" range which is on sheet 1 is hidden=false (visible) THEN

a range on sheet2 i have designated Blood_Show will automatically become unhidden

as soon as that range blood is hidden again then Blood_Show would again dissapear/behidden on sheet 2

Any help with this would be really appreciated! Thanks so much!

In Excel 2010, Is it possible to chart a timeline of data (with the beginning date of 9/1/2002 and the ending date of 2/29/2012) while only showing the end of year dates on the X Axis?

I'm charting data over a time frame of several years. My start is 9/1/2002 and my end date is 2/29/2012. I only want my Excel chart to display the the last day of the year (e.g., 12/31/2002, 12/31/2003, 12/31/2004...12/31/2009, 12/31/2010, 12/31/2011).

Changing the Min and Max Axis to 12/31/2002 and 12/31/2011 won't work, as I need to show the data that occurs outside of those dates.

I appreciate any help.

Thanks!!!

Hello guys, I'm just an amateur in Excel. I have found a lot of solutions to my daily work problems here in the forum but now I stepped into something I can't see the answer.

I'm doing a report to show the winners of a competion.
I stated a cell (G104) that has a dropdown list of the approved by criteria A.
Now i want to use these names to evaluate criteria B.

So, I need to filter a column inside a pivottable to show only the item correspondant to the person listed on the cell.

I tried to record the hide of all names and unhide of one name. But, I discovered that excel sets to false the visibility of each item one on one. I need a command to make all false in a simples step.

Something like this:

	VB:
	
 ActiveSheet.PivotTables("PivotTable2").PivotFields("Trainee") 
    .PivotItems("(SELECT ALL)").Visible = False 
    .PivotItems(Range("G104").Value).Visible = True 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I know that the "(select all)" inside the code don't existe/work.

Anyone can figure how to solve?

Dear all,
I'd like to have a combobox content of two data but showing only one data. When it's chosen then data which is not shown appear.
For example: combo box shows employees' name but when it is selected then his/her badge is shown at "B1".
The following is what I found, but it shown non unique data


	VB:
	
 
Sheets("Sheet1").ComboBox1.Clear 
Myarray = Worksheets("Sheet2").Range("A2:B19") 
With Sheets("Sheet1").ComboBox1 
    .Clear 
    .ColumnCount = 2 
    .BoundColumn = 1 
    .List() = Myarray 
    .ColumnWidths = "0;" 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Your assistance is higly appreciated
Alle2233

Hi, I want to show 2 pivot tables on one tab. The 2 tables are to show amounts for items for multiple years. So my column will be yearly based. However, one table contains items requiring 2 columns as identifications (project number and project name), the other one contains only one. I cannot merge the project number and project name to become one column.

What can I consider?

If I have a drop down list that references a column, and there are certain values that are highlighted in color. Is there a way for the drop down list to only show the values that are highlighted in color?

My spreadsheets are now showing only numbers on the columns Rather than A, B, C, D etc.

Do you know what may be causing this?

I have a search code in wich I can select and copy values to an other sheet, but instead I want to filter the database in place to show only those rows that applies to the search word. I have defined a name "Dbase" for my database.
So I'm looking for a string to use the filtering instead of copy to the other sheet.

	VB:
	
 Range 
    Sheets("Search").Select 
    Rows("4:500").Select 
    Selection.Delete 
    Range("A2").Select 
    On Error Goto ErrorZone 
     
    x = Range("Dbase!B1") 
     
    Set MyRange = Range("Dbase!Dbase") 
    Set Found_Range = Find_Range(x, MyRange, xlValues, xlWhole).EntireRow 
    Union(Found_Range, Found_Range).Copy Range("Search!A3") 
    DisplayEnd:    Exit Sub 
     
     
    ErrorZone:     MsgBox ("Can't find your search data, try to use your search word with a star behind and after your word")

    Resume DisplayEnd 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

	VB:
	
 Range, _ 
    Optional LookIn As XlFindLookIn = xlValues, _ 
    Optional LookAt As XlLookAt = xlPart, _ 
    Optional MatchCase As Boolean = False) As Range 
     
    Dim c As Range, FirstAddress As String 
     
    With Search_Range 
        Set c = .Find( _ 
        What:=Find_Item, _ 
        LookIn:=LookIn, _ 
        LookAt:=LookAt, _ 
        SearchOrder:=xlByRows, _ 
        SearchDirection:=xlNext, _ 
        MatchCase:=MatchCase, _ 
        SearchFormat:=False) 
        If Not c Is Nothing Then 
            Set Find_Range = c 
            FirstAddress = c.Address 
            Do 
                Set Find_Range = Union(Find_Range, c) 
                Set c = .FindNext(c) 
            Loop While Not c Is Nothing And c.Address  FirstAddress 
        End If 
    End With 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have an extremely long column/list of names which I would like to sort on the fly using excel 2003, I have some experience in excel/VBA, although limited. Here is what I would like to do… As I begin typing a name via a validation drop-down list or some sort of a combo/search box, I would like the following to happen.

I type “sam or Sam or SAM”, and there are say 20 names that began with “sa or Sa or SA”, a new list is displayed showing only the names that match the characters that I have type. The original list will be formatted as TEXT, and will have zero blank rows. I have auto sort using data validation working perfectly using the information I picked up from this thread

http://www.ozgrid.com/forum/showthread.php?t=69632&page=1

But I still have to click the arrow to the other names that match. I would like to see a list of all names that match my search criteria, and then pick from that list the name that I want to perform a function on. Any and all suggestions are welcome the more the merrier. Thanks for your help.

Hi!

Hopefully someone can enlighten me with this one.

I noticed this morning something weird.

I'm the only user of my files, my computer is hooked to a private home network, and know for a fact that my girlfriend didn't access my workbooks.

I open up my 10 workbooks to validate the structure, and I get some weird message about the file being used by someone else, therefore I can only open it as read-only. Duh? When I click cancel and try to re-open it, no problem.

I get this validation box randomly on a couple of files (3-4) out of the 10 files.

Why?
Can I avoid it?

Thanx!

I'm trying to help a user who can only open an Excel file from inside Excel. When she clicks on a shortcut to a file or on a file icon, Excel opens, but not the file. If Excel is already open, she can use the Open dialog box on the File menu and the file opens with no problem. She can even open a file from the recently used file list on the File menu. It doesn't matter what file she tries to open.

She's using Windows XP, Office 2003.

Does anyone have an idea what may be happening? How to fix it?

Hi everyone,

I'm trying to write a statement in a macro to copy and paste sections of data from several workbooks into several different worksheets in another workbook. At first, I want to create a new worksheet, then just select it when coying over the rest of the data. The problem is that I may be starting the process from a different workbook each day, meaning I cannot use a statement to open the destination workbook in each of the macros. I only need it in the first instance, then I just need to reference the already open worksheet in subsequent instances. If the open statement is used when the workbook in question is already open, it will overwrite any changes.

Is there a way to only open the destination workbook if it is not already open. And if it is open, to just select it? I can't think of an if construct to do this.

I would be extremely grateful for any help on this. I have been trying all sorts of things all day and I just can't see a sensible way around this problem.

Many thanks,

vcoder

I have a workbook that I want to show different Userforms
based on condition within the spreadsheet. When I only
have the Private Sub set up everything works well, But
when the 2nd one is added I get an error for anbigous name.

Can anyone tell me how to correct this?

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("P1BetDown") < Range("CBetDown") Then
UserForm7.Show
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("P1BetFlop") < Range("CBetFlop") Then
UserForm8.Show
End If
End Sub

Thanks
Pete

I'm opening files on a network share and the most recent changes made by me
on another computer are not showing up. They will show up if I navigate out
to the network share and right-click the .xls file and tell it to
synchronize, and then open it.

I don't want to have to manually synchronize every time I open the file.

Anyone have any ideas?


No luck finding an answer? You could always try Google.