copying text across sheets

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

Guest

Hi,

Excel XP (version 10)

Could anyone tell me if this is possible, and if so, is it going to be
easy or a pain in the neck.

I am a teacher trying to make life a little easier for myself. I am
trying to make a seating plan a little more automatic.

I have a workbook with 2 sheets in it.

The first sheet has a list of pupils names.

The second sheet has a been 'designed' using boarders to represent the
desk layout in my classroom. In each desk I have a number (from 1 to
30).

At the moment I copy and past the pupils' names from sheet 1 to the
appropriate place in sheet 2 - where I want them to sit.

It would be a lot easier if I could add a column next to the pupil's
name in sheet 1 where I could just type the seat number (1 to 30) and
the pupil's name could be copied to the appropriate cell in sheet2.

Any ideas would be most appreciated.

Thanks
 
What you're looking for should be easy, not a pain in the neck. I'd suggest
that you use a function such as VLOOKUP on Sheet2. Suppose that on Sheet1
you have the number 1 in cell A1 and the name Tony in cell B1, the number 2
in cell A2 and the name Margaret in cell B2, and so on down through row 30.
(If you prefer to have names in column A and numbers in column B, it's a
trifle more complicated and you'd want to combine MATCH with OFFSET.)

A problem arises with respect to how you have designed Sheet2. It's unclear
what you mean by "In each desk I have a number (from 1 to 30)." If one cell
represents one desk, that one cell can hold either a number that identifies
the desk, or a student name -- but not both without turning into a pain in
the neck. So I'll assume that a desk on Sheet2 is represented by at least
two cells: say, A1 and A2. If the desk number is in cell A1, you could use
this formula in cell A2:

=VLOOKUP(A1,Sheet1!$A$1:$B$30,2,FALSE)

This will look for whatever number is in cell A1 of Sheet2 (argument #1). It
will look for it in column A of Sheet1 (argument #2). If and when it finds
that number, it will get the value in column B of Sheet1 (arguments #2 and
#3 together). And it will demand an exact match between the value in
Sheet2's A1 and the values in Sheet1's column A (argument #4; FALSE means
"give me an exact match"), so you'll get an #N/A error value if Sheet2's
cell A1 contains 1.5 and there is no 1.5 in Sheet1's column A.

I used dollar signs in $A$1:$B$30 because they "anchor" the lookup range,
which allows you to copy and paste the formula into other cells of Sheet2
without inadvertently changing the lookup range's address.

Just FYI, what you asked about is not "copying," which is the act of taking
a value or formula in one location and, usually, placing it in another by
pasting. If you want a cell to respond automatically to a change in another
cell, a formula and/or function is best, and it looks like that's what you
asked about.

C^2
Conrad Carlberg
 
Back
Top