LinkBack URL
About LinkBacks
I'm using excel 2007.
I have a master invoice worksheet and a name/address worksheet within the same workbook.
I want to create a dropdown bar? in the invoice, to select a name and address to put into the invoice.
Can anyone help please?
I'm using excel 2007.
I have a master invoice worksheet and a name/address worksheet within the same workbook.
I want to create a dropdown bar? in the invoice, to select a name and address to put into the invoice.
Can anyone help please?
Last edited by BongoBill; 02-11-2009 at 11:41 AM.
you need to name the Name range...first
Select the range containing the names and go to Insert|Name|Define
Enter a name...e.g. "Names" without quotes.
And enter the range in the source field.
Now go to the cell you want to put the drop down in and go to Data|Validation.. select List from the Allow choices.
enter =Names
in the address field.. enter formula: =Vlookup(A1,Sheet2!A1:B100,2,FALSE)
where A1 is the drop down cell containing chosen name... Sheet2!A1:B100 contains the list of Names and corresponding addressess...
Revise references to suit your data
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
When I try the Insert tab I can't find Name?
Sorry... should have noted that you are using XL2007...
Then Define Name is in the Formulas tab...
and the Data Validation is in the Data tab
I've got the name bit sorted but havin trouble with the Vlookup values.
I've uploaded spreadsheet, can u tell me where I've gone wrong?
Many thanks for the help!
Hope this helps.
Yes it will. we just have to figure out how...
If I have helped you, PLEASE click the * and add to my Rep.
Also, if the problem is SOLVED please mark it as so.
Try:
=VLOOKUP($B$8,ADDRESSES!$A$2:$E$100,ROWS($A$1:$A2),FALSE)
this will take Address 1..
When/If you copy the formula down a few cells, it will then, consecutively, take the other address info too.
your range in the Vlookup
ADDRESSES!B2:B100
Should be:
ADDRESSES!A2:B100
A2 not B2 you have to include the names you are looking for and all columns that you want information from.
Last edited by Dunc3142; 02-11-2009 at 11:03 AM.
Thanx Dunc3142, I've got that bit sussed and defined all the columns it's the vlookup bit I'm struggling with.
I understand the "Vlookup(A1,Sheet2!" bit of NBVC's post, it's the bit after....
I assume the A1:B100 bit refers to the addresses range but not understanding the 2,FALSE bit
Keeps returning #N/A
Soz, just seen your new post.
I just worked that out but it's still showing a return of #N/A
Who's Soz?
the 2 means look at column number 2 withing the Vlookup table range (A1:B100) and the FALSE means look for an exact match, as opposed to an approximate match...
See Vlookup help in help files...
My version... Rows() function in place of 2 just allows you to drag the formula down so that it auto generates the column index number, instead of you have to hard code it for each instance of the formula.... which you can still do if you want.
Result! Thanx guys.
Ah, the joys of being a noob. Still u don't ask u dont learn eh?
Keep up the good work, and thanx again.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks