How can I look up data in a different workbook in an excel spread.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
DoctorG said:
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?
Click to expand...
 
DoctorG said:
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?
Click to expand...

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
 
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!
 
Yes, I meant to use worksheet in the title, not Workbook. See Max's reply for
the correct solution.
 
I take it there's no way to do it if the workbook is CLOSED though? Has
anyone developed a workaround on this issue?
 
Back
Top