Solved

Quick excel 2007 questions

Posted on 2012-04-11
9
440 Views
Last Modified: 2012-04-17
Hi

I'm having difficulty trying to do the following in Excel 2007:

1. Insert a list based on the data listed in another worksheet (but in the same spreadsheet).  When i select data validation / List / Source, I am unable to select the other worksheet titled "Field names for lists" and in cells B1 to B3.  If I change the name of the worksheet to a single name without space, then it works fine, but I want to use multiple words

2. I dont know how to insert another worksheet from another spreadsheet in this version of excel.

Thanks.
0
Comment
Question by:abdb469
  • 5
  • 4
9 Comments
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 500 total points
ID: 37832775
1. As source you can use: ='Field names for lists'!B1:B3

2. Similarly for external file use: ='[external_list.xlsx]Field names for lists'!B1:B3
0
 

Author Comment

by:abdb469
ID: 37833051
Ok but for 2, how do I actually insert a worksheet.  I dont need the formula for question 2, just the actual technique i.e. menu commands etc
0
 
LVL 35

Assisted Solution

by:Robert Schutt
Robert Schutt earned 500 total points
ID: 37833495
ah, then I think you need to open both excel files (workbooks) and use the right mouse button on the worksheet in the source workbook and choose "Move or copy", then you can move/copy to the other workbook.

see the full instructions here: http://office.microsoft.com/en-us/excel-help/move-or-copy-a-worksheet-HP001217035.aspx#BM2 

Or, if you need to be able to do this repeatedly I can help you set up a VBA macro.
0
 

Author Closing Comment

by:abdb469
ID: 37836949
Thank you, that worked nicely.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:abdb469
ID: 37836968
Actually just one further follow up question.  Instead of referencing a range on the other worksheet e.g. B1:B3, if I have named that range "Decision_variables", is it possible to reference a range name rather than the cell range?
0
 

Author Comment

by:abdb469
ID: 37836984
And in the drop down list, if I wanted the first option to be blank, then do I have to always leave a blank row in the source spreadsheet range, there is no option in the data validation option to keep the first row in the drop down blank?
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 37836994
Yes sure, that's even better, just use:

=Decision_variables

as source
0
 

Author Comment

by:abdb469
ID: 37837209
great, so there is no need to reference the other worksheet because range names work across worksheets.  Cool.

Final q - How can I have the words "Please select" at the start of the drop down list so that by default the spreadsheet when opened, makes it clear that those cells are drop downs before even clicking on it?

At present the cells are blank until you click on the dropdown.  

Thanks again.
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 37837263
I only have an old version of Excel here at home but it seems to work the same.

I don't think you can add it to the list other than by putting in the source range and have it become a valid option.

You can however put the text in the cell before you specify the Data-Validation-Source-List.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now