Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Quick excel 2007 questions

Posted on 2012-04-11
9
Medium Priority
?
446 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
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…

597 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