Help with Microsoft excel spreadsheets

Lifeisbutadream

scottishlass wrote: »

In the input range I just typed in =[nameofotherworkbook.xls].NameofSheet.Range

It just tells me the formula contains an error.

scottishlass

Lifeisbutadream wrote: »

It just tells me the formula contains an error.

I don't know then sorry

Lifeisbutadream

scottishlass wrote: »

I don't know then sorry

Thanks for trying to help. Looks like I will just have to carry on with what I have!

viv0147

Type into Google Excel tutorials and you may find the answer your looking for there.

Chippy_Minton

Lifeisbutadream wrote: »

OK, maybe I am just being really thick, but I have my drop down list set up, right click on it and select 'Format Control', where I get a window asking me 'Input range' which is where I put where the information should be picked up from. I dont know what to put in there to make it pick up from another workbook?

If you managed to make it work, what formula did you have in here? are you working on Windows Vista?

To link the Input Range to cells in another workbook, you must have that workbook open. Then you can enter something like:

'[Contact List.xls]Sheet1'!$A$1:$A$20

if Contact List.xls is in the same folder

or

'C:\Documents and Settings\YOURUSERNAMEHERE\My Documents\[Contact List.xls]Sheet1'!$A$1:$A$20

if it's in a different folder.

However populating the combo box by setting the Input Range is quite restrictive. You have to keep the Contacts List.xls open all the time, otherwise the list is emptied and the $A$1:$A$20 part is a fixed range so it's not dynamic. That's why you need VBA code to populate the combo box.

BTW I'm using Excel 2003 on Windows XP. Are you using Excel 2007?

Also note that there are 2 types of control -
http://www.ozgrid.com/Excel/Excel-controls-forms-toolbox.htm - I don't know if this is still the case with Excel 2007.

Chippy_Minton

Lifeisbutadream wrote: »

Thank you for going to all this trouble.

When I set up all the original sheets I was using Excel 97-2003 and found is straightforward. I am now using windows vista and I have no idea how to insert a dropdown box! the drop down boxes are already there - I appear to have everything OK in VB, but cant get any further than that.

It's the version of Excel you're using now that's important, not the OS. For Excel 2007, you have to enable the Developer tab - see http://support.microsoft.com/kb/291073

Lifeisbutadream

Sorry I thought the version was Windows Vista - it is 2007.

Fairwinds

If you have a named range you can uuse "validation" from the data menu to give you drop downs.

In the Allow box choose "list" and in the source box put the named range reference

espresso

I would guess that the OP have given up on this one!

Lifeisbutadream

Hi - no I havent given up yet, just not had time to do anything else!

I am going on hols for 2 weeks on Monday, but will try again when I get back... thanks for everyones help!