Solved

Quick excel 2007 questions

Posted on 2012-04-11
9
439 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

706 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

19 Experts available now in Live!

Get 1:1 Help Now