A
Art
I have two sheets in my Excel 2007 spreadsheet---Sheet 2 is a list of all
courses with several columns, but two in particular (Column 1 is COURSE ID
and Column 2 is COURSE TITLE) and Sheet 4 is a list of textbooks that all
courses use with two columns in particular (Column AA lists the COURSE IDs
that use a particular textbook and Column AB that shows the COURSE TITLE
using vlookup on 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
and enter the COURSE ID (either by pasting a link to Sheet 2 or manually
typing the course ID) in Column AA. If the course ID is entered, the
spreadsheet 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
that if a course ID is changed for some reason on Sheet 2, it will
automatically update the COURS ID on Sheet 4. However, it is very tedious to
copy & paste link the course ID from one sheet to the next. Plus, the person
who will manage the 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.,
BUS 280) and then use vlookup for the course title (since there is less
likely to be an error in entering information with the course ID than title).
In doing so, I lose the linking, which means I'd have to manually change any
course ID on Sheet 4 if one is changed on Sheet 2. I really wanted Sheet 2 to
be the only sheet 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 more
user-friendly way for a non Excel expert to paste links in the cell rather
than typing them manually to avoid possible typing errors?
QUESTION 2: Its possible there could be more than one course assigned to a
textbook. This makes linking the COURSE IDs more difficult cause I have to
add &", "& between the course IDs (e.g., Sheet1A4&", "&Sheet1A8) otherwise
more than 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 additional &", "&. 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,
is there a way that vlookup can still look up each course ID (separated with
a comma and space as shown above) and display both (or more than two) course
titles in Column AB? For example, if BUS 280 is entered in the cell in Column
AB, its easy to show the single course title (e.g., "Case Development"). If
BUS 280, MGT 240 is entered in the cell in Column AB, I would want the cell
in Column AB to display both course titles "Case Development, Strategic
Management".
Thanks so much for any help!!!
courses with several columns, but two in particular (Column 1 is COURSE ID
and Column 2 is COURSE TITLE) and Sheet 4 is a list of textbooks that all
courses use with two columns in particular (Column AA lists the COURSE IDs
that use a particular textbook and Column AB that shows the COURSE TITLE
using vlookup on 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
and enter the COURSE ID (either by pasting a link to Sheet 2 or manually
typing the course ID) in Column AA. If the course ID is entered, the
spreadsheet 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
that if a course ID is changed for some reason on Sheet 2, it will
automatically update the COURS ID on Sheet 4. However, it is very tedious to
copy & paste link the course ID from one sheet to the next. Plus, the person
who will manage the 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.,
BUS 280) and then use vlookup for the course title (since there is less
likely to be an error in entering information with the course ID than title).
In doing so, I lose the linking, which means I'd have to manually change any
course ID on Sheet 4 if one is changed on Sheet 2. I really wanted Sheet 2 to
be the only sheet 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 more
user-friendly way for a non Excel expert to paste links in the cell rather
than typing them manually to avoid possible typing errors?
QUESTION 2: Its possible there could be more than one course assigned to a
textbook. This makes linking the COURSE IDs more difficult cause I have to
add &", "& between the course IDs (e.g., Sheet1A4&", "&Sheet1A8) otherwise
more than 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 additional &", "&. 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,
is there a way that vlookup can still look up each course ID (separated with
a comma and space as shown above) and display both (or more than two) course
titles in Column AB? For example, if BUS 280 is entered in the cell in Column
AB, its easy to show the single course title (e.g., "Case Development"). If
BUS 280, MGT 240 is entered in the cell in Column AB, I would want the cell
in Column AB to display both course titles "Case Development, Strategic
Management".
Thanks so much for any help!!!