Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Quick excel 2007 questions

Posted on 2012-04-11
9
Medium Priority
?
448 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 2000 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 2000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Closing Comment

by:abdb469
ID: 37836949
Thank you, that worked nicely.
0
 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
In this post, we will learn to set up the Group Naming policy and will see how it is going to impact the Display Name and the Email addresses of the Group.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

571 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