Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How can I look up data in a different workbook in an excel spread.
My spreadsheet has three worksheets - Sheet1, Sheet2, and Data.
The Data worksheet has several columns of different lengths that contain data to be used in several dropdown lookup lists on both Sheet1 and Sheet2. Both Sheet1 and Sheet2 use the same columns of data. I can not use Data Validation lists to look these up because this function requires that the lookup lists be in the same worksheet. I can use a ComboBox to lookup the data, but I am unable to format the cells containing the combo boxes, which appear with small fonts, no matter how the original cell or the cells in Data are formatted, and there is no formatting option on the ComboBox properties tab. How can I look up and properly format cells containing lookup lists without having to repeat the data on the two worksheets? |
#2
|
|||
|
|||
"DoctorG" wrote in message ... My spreadsheet has three worksheets - Sheet1, Sheet2, and Data. The Data worksheet has several columns of different lengths that contain data to be used in several dropdown lookup lists on both Sheet1 and Sheet2. Both Sheet1 and Sheet2 use the same columns of data. I can not use Data Validation lists to look these up because this function requires that the lookup lists be in the same worksheet. I can use a ComboBox to lookup the data, but I am unable to format the cells containing the combo boxes, which appear with small fonts, no matter how the original cell or the cells in Data are formatted, and there is no formatting option on the ComboBox properties tab. How can I look up and properly format cells containing lookup lists without having to repeat the data on the two worksheets? |
#3
|
|||
|
|||
I can not use Data Validation lists to look these up
because this function requires that the lookup lists be in the same worksheet .. If you use a named range/list, you can .. DV Settings tab ---------- Allow List Source: =MyList where MyList is a named range refering to the DV items in say, Sheet2 Try Debra's page at: http://www.contextures.com/xlDataVal01.html -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "DoctorG" wrote in message ... My spreadsheet has three worksheets - Sheet1, Sheet2, and Data. The Data worksheet has several columns of different lengths that contain data to be used in several dropdown lookup lists on both Sheet1 and Sheet2. Both Sheet1 and Sheet2 use the same columns of data. I can not use Data Validation lists to look these up because this function requires that the lookup lists be in the same worksheet. I can use a ComboBox to lookup the data, but I am unable to format the cells containing the combo boxes, which appear with small fonts, no matter how the original cell or the cells in Data are formatted, and there is no formatting option on the ComboBox properties tab. How can I look up and properly format cells containing lookup lists without having to repeat the data on the two worksheets? |
#4
|
|||
|
|||
"DoctorG" wrote in message ... My spreadsheet has three worksheets - Sheet1, Sheet2, and Data. The Data worksheet has several columns of different lengths that contain data to be used in several dropdown lookup lists on both Sheet1 and Sheet2. Both Sheet1 and Sheet2 use the same columns of data. I can not use Data Validation lists to look these up because this function requires that the lookup lists be in the same worksheet. I can use a ComboBox to lookup the data, but I am unable to format the cells containing the combo boxes, which appear with small fonts, no matter how the original cell or the cells in Data are formatted, and there is no formatting option on the ComboBox properties tab. How can I look up and properly format cells containing lookup lists without having to repeat the data on the two worksheets? In Data, you can use something like =Sheet1!A1 The data is now in the same worksheet. This will give you the data from a different worksheet. The subject line mentions a different workbook. Did you make a typo? /Fredrik |
#5
|
|||
|
|||
Yes, the use of a named range works! For some reason, I had been trying to
use $MyList instead of just MyList (where MyList is the name I had assigned to the data range for the list) in the validation formula, and it never worked. Thanks for showing me my error! "Max" wrote: I can not use Data Validation lists to look these up because this function requires that the lookup lists be in the same worksheet .. If you use a named range/list, you can .. DV Settings tab ---------- Allow List Source: =MyList where MyList is a named range refering to the DV items in say, Sheet2 Try Debra's page at: http://www.contextures.com/xlDataVal01.html -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "DoctorG" wrote in message ... My spreadsheet has three worksheets - Sheet1, Sheet2, and Data. The Data worksheet has several columns of different lengths that contain data to be used in several dropdown lookup lists on both Sheet1 and Sheet2. Both Sheet1 and Sheet2 use the same columns of data. I can not use Data Validation lists to look these up because this function requires that the lookup lists be in the same worksheet. I can use a ComboBox to lookup the data, but I am unable to format the cells containing the combo boxes, which appear with small fonts, no matter how the original cell or the cells in Data are formatted, and there is no formatting option on the ComboBox properties tab. How can I look up and properly format cells containing lookup lists without having to repeat the data on the two worksheets? |
#6
|
|||
|
|||
Yes, I meant to use worksheet in the title, not Workbook. See Max's reply for
the correct solution. "Fredrik Wahlgren" wrote: "DoctorG" wrote in message ... My spreadsheet has three worksheets - Sheet1, Sheet2, and Data. The Data worksheet has several columns of different lengths that contain data to be used in several dropdown lookup lists on both Sheet1 and Sheet2. Both Sheet1 and Sheet2 use the same columns of data. I can not use Data Validation lists to look these up because this function requires that the lookup lists be in the same worksheet. I can use a ComboBox to lookup the data, but I am unable to format the cells containing the combo boxes, which appear with small fonts, no matter how the original cell or the cells in Data are formatted, and there is no formatting option on the ComboBox properties tab. How can I look up and properly format cells containing lookup lists without having to repeat the data on the two worksheets? In Data, you can use something like =Sheet1!A1 The data is now in the same worksheet. This will give you the data from a different worksheet. The subject line mentions a different workbook. Did you make a typo? /Fredrik |
#7
|
|||
|
|||
You're welcome !
Glad it helped .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "DoctorG" wrote in message ... Yes, the use of a named range works! For some reason, I had been trying to use $MyList instead of just MyList (where MyList is the name I had assigned to the data range for the list) in the validation formula, and it never worked. Thanks for showing me my error! |
#8
|
|||
|
|||
Hi
you said: I can not use Data Validation lists to look these up because this function requires that the lookup lists be in the same worksheet. this is, in fact, not correct - check out http://www.contextures.com/xlDataVal05.html for instructions on how to achieve this - however, you can't format the font size in data / validation drop down boxes either - but check out http://www.contextures.com/xlDataVal08.html#Font for ideas on this one too. Cheers JulieD "DoctorG" wrote in message ... My spreadsheet has three worksheets - Sheet1, Sheet2, and Data. The Data worksheet has several columns of different lengths that contain data to be used in several dropdown lookup lists on both Sheet1 and Sheet2. Both Sheet1 and Sheet2 use the same columns of data. I can not use Data Validation lists to look these up because this function requires that the lookup lists be in the same worksheet. I can use a ComboBox to lookup the data, but I am unable to format the cells containing the combo boxes, which appear with small fonts, no matter how the original cell or the cells in Data are formatted, and there is no formatting option on the ComboBox properties tab. How can I look up and properly format cells containing lookup lists without having to repeat the data on the two worksheets? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I look up data in a different workbook in an excel spr
is there a way to reference a validation list from a different WORKBOOK?
"Fredrik Wahlgren" wrote: "DoctorG" wrote in message ... My spreadsheet has three worksheets - Sheet1, Sheet2, and Data. The Data worksheet has several columns of different lengths that contain data to be used in several dropdown lookup lists on both Sheet1 and Sheet2. Both Sheet1 and Sheet2 use the same columns of data. I can not use Data Validation lists to look these up because this function requires that the lookup lists be in the same worksheet. I can use a ComboBox to lookup the data, but I am unable to format the cells containing the combo boxes, which appear with small fonts, no matter how the original cell or the cells in Data are formatted, and there is no formatting option on the ComboBox properties tab. How can I look up and properly format cells containing lookup lists without having to repeat the data on the two worksheets? In Data, you can use something like =Sheet1!A1 The data is now in the same worksheet. This will give you the data from a different worksheet. The subject line mentions a different workbook. Did you make a typo? /Fredrik |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I look up data in a different workbook in an excel spr
You can use a list from another open workbook, as described he
http://www.contextures.com/xlDataVal05.html TheDude wrote: is there a way to reference a validation list from a different WORKBOOK? "Fredrik Wahlgren" wrote: "DoctorG" wrote in message ... My spreadsheet has three worksheets - Sheet1, Sheet2, and Data. The Data worksheet has several columns of different lengths that contain data to be used in several dropdown lookup lists on both Sheet1 and Sheet2. Both Sheet1 and Sheet2 use the same columns of data. I can not use Data Validation lists to look these up because this function requires that the lookup lists be in the same worksheet. I can use a ComboBox to lookup the data, but I am unable to format the cells containing the combo boxes, which appear with small fonts, no matter how the original cell or the cells in Data are formatted, and there is no formatting option on the ComboBox properties tab. How can I look up and properly format cells containing lookup lists without having to repeat the data on the two worksheets? In Data, you can use something like =Sheet1!A1 The data is now in the same worksheet. This will give you the data from a different worksheet. The subject line mentions a different workbook. Did you make a typo? /Fredrik -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I look up data in a different workbook in an excel spr
I take it there's no way to do it if the workbook is CLOSED though? Has
anyone developed a workaround on this issue? "Debra Dalgleish" wrote: You can use a list from another open workbook, as described he http://www.contextures.com/xlDataVal05.html TheDude wrote: is there a way to reference a validation list from a different WORKBOOK? "Fredrik Wahlgren" wrote: "DoctorG" wrote in message ... My spreadsheet has three worksheets - Sheet1, Sheet2, and Data. The Data worksheet has several columns of different lengths that contain data to be used in several dropdown lookup lists on both Sheet1 and Sheet2. Both Sheet1 and Sheet2 use the same columns of data. I can not use Data Validation lists to look these up because this function requires that the lookup lists be in the same worksheet. I can use a ComboBox to lookup the data, but I am unable to format the cells containing the combo boxes, which appear with small fonts, no matter how the original cell or the cells in Data are formatted, and there is no formatting option on the ComboBox properties tab. How can I look up and properly format cells containing lookup lists without having to repeat the data on the two worksheets? In Data, you can use something like =Sheet1!A1 The data is now in the same worksheet. This will give you the data from a different worksheet. The subject line mentions a different workbook. Did you make a typo? /Fredrik -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: Use a name with external workbook reference for data valida | Excel Worksheet Functions | |||
linking workbook data | Excel Worksheet Functions | |||
macro to browse for workbook, pick up data and looping | Excel Worksheet Functions | |||
Extract specific data into its own workbook via macro? | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |