Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 450
  • Last Modified:

Quick excel 2007 questions

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
abdb469
Asked:
abdb469
  • 5
  • 4
2 Solutions
 
Robert SchuttSoftware EngineerCommented:
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
 
abdb469Author Commented:
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
 
Robert SchuttSoftware EngineerCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
abdb469Author Commented:
Thank you, that worked nicely.
0
 
abdb469Author Commented:
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
 
abdb469Author Commented:
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
 
Robert SchuttSoftware EngineerCommented:
Yes sure, that's even better, just use:

=Decision_variables

as source
0
 
abdb469Author Commented:
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
 
Robert SchuttSoftware EngineerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now