A
art
I have two sheets in my Excel 2007 spreadsheet---Sheet 2 is a list of al
courses with several columns, but two in particular (Column 1 is COURSE ID an
Column 2 is COURSE TITLE) and Sheet 4 is a list of textbooks that all course
use with two columns in particular (Column AA lists the COURSE IDs that use
particular textbook and Column AB that shows the COURSE TITLE using vlookup o
Sheet 2).
Sheet 2:
Column A: Course ID
Column B: Course TItle
Sheet 4:
Column AA: Course ID (entered manually or pasted link from Sheet 2 Column A)
Column AB: Course Title (auto displayed using vlookup---search for Column AA
in Sheet 4 in Column A in Sheet 2)
The person managing the textbook sheet will add new textbooks on each row an
enter the COURSE ID (either by pasting a link to Sheet 2 or manually typing th
course ID) in Column AA. If the course ID is entered, the spreadshee
automatically shows the COURSE TITLE in Column AB using vlookup on Sheet 2.
This works fine if I enter one COURSE ID in a cell in Column AA.
Ideally, I would prefer the COURSE IDs be linked to Column A in Sheet 2 so tha
if a course ID is changed for some reason on Sheet 2, it will automaticall
update the COURS ID on Sheet 4. However, it is very tedious to copy & paste lin
the course ID from one sheet to the next. Plus, the person who will manage th
textbook site doesn't know much about Excel, so I can see
him not using this process consistently.
As a result, I figure the person could manually enter the COURSE ID (e.g., BU
280) and then use vlookup for the course title (since there is less likely to b
an error in entering information with the course ID than title). In doing so,
lose the linking, which means I'd have to manually change any course ID on Shee
4 if one is changed on Sheet 2. I really wanted Sheet 2 to be the only shee
that is manually edited/updated with regards to course information (ID, title
credits, prerequisites, etc.). Textbook information is only edited in Sheet 4.
QUESTION 1: Any suggestions to handle the linking issue---i.e., a mor
user-friendly way for a non Excel expert to paste links in the cell rather tha
typing them manually to avoid possible typing errors?
QUESTION 2: Its possible there could be more than one course assigned to
textbook. This makes linking the COURSE IDs more difficult cause I have to ad
&", "& between the course IDs (e.g., Sheet1A4&", "&Sheet1A8) otherwise more tha
one ID is not readable (e.g., ""BUS 280, MGT 240" rather than "BUS280MGT240")
It's not very "user friendly" to ask the person to manually add this additiona
&", "&. Is there another way to paste more than one link in a single cell?
QUESTION 3: If there is more than one course ID for a particular textbook, i
there a way that vlookup can still look up each course ID (separated with
comma and space as shown above) and display both (or more than two) cours
titles in Column AB? For example, if BUS 280 is entered in the cell in Colum
AB, its easy to show the single course title (e.g., "Case Development"). If BU
280, MGT 240 is entered in the cell in Column AB, I would want the cell i
Column AB to display both course titles "Case Development, Strategi
Management".
Thanks so much for any help!!!
courses with several columns, but two in particular (Column 1 is COURSE ID an
Column 2 is COURSE TITLE) and Sheet 4 is a list of textbooks that all course
use with two columns in particular (Column AA lists the COURSE IDs that use
particular textbook and Column AB that shows the COURSE TITLE using vlookup o
Sheet 2).
Sheet 2:
Column A: Course ID
Column B: Course TItle
Sheet 4:
Column AA: Course ID (entered manually or pasted link from Sheet 2 Column A)
Column AB: Course Title (auto displayed using vlookup---search for Column AA
in Sheet 4 in Column A in Sheet 2)
The person managing the textbook sheet will add new textbooks on each row an
enter the COURSE ID (either by pasting a link to Sheet 2 or manually typing th
course ID) in Column AA. If the course ID is entered, the spreadshee
automatically shows the COURSE TITLE in Column AB using vlookup on Sheet 2.
This works fine if I enter one COURSE ID in a cell in Column AA.
Ideally, I would prefer the COURSE IDs be linked to Column A in Sheet 2 so tha
if a course ID is changed for some reason on Sheet 2, it will automaticall
update the COURS ID on Sheet 4. However, it is very tedious to copy & paste lin
the course ID from one sheet to the next. Plus, the person who will manage th
textbook site doesn't know much about Excel, so I can see
him not using this process consistently.
As a result, I figure the person could manually enter the COURSE ID (e.g., BU
280) and then use vlookup for the course title (since there is less likely to b
an error in entering information with the course ID than title). In doing so,
lose the linking, which means I'd have to manually change any course ID on Shee
4 if one is changed on Sheet 2. I really wanted Sheet 2 to be the only shee
that is manually edited/updated with regards to course information (ID, title
credits, prerequisites, etc.). Textbook information is only edited in Sheet 4.
QUESTION 1: Any suggestions to handle the linking issue---i.e., a mor
user-friendly way for a non Excel expert to paste links in the cell rather tha
typing them manually to avoid possible typing errors?
QUESTION 2: Its possible there could be more than one course assigned to
textbook. This makes linking the COURSE IDs more difficult cause I have to ad
&", "& between the course IDs (e.g., Sheet1A4&", "&Sheet1A8) otherwise more tha
one ID is not readable (e.g., ""BUS 280, MGT 240" rather than "BUS280MGT240")
It's not very "user friendly" to ask the person to manually add this additiona
&", "&. Is there another way to paste more than one link in a single cell?
QUESTION 3: If there is more than one course ID for a particular textbook, i
there a way that vlookup can still look up each course ID (separated with
comma and space as shown above) and display both (or more than two) cours
titles in Column AB? For example, if BUS 280 is entered in the cell in Colum
AB, its easy to show the single course title (e.g., "Case Development"). If BU
280, MGT 240 is entered in the cell in Column AB, I would want the cell i
Column AB to display both course titles "Case Development, Strategi
Management".
Thanks so much for any help!!!