Referencing another WB's cells, but not formulas/values.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

anna - 29 Mar 2007 18:22 GMT

I have an attendance worksheet (A) and need to create an addition spreadsheet
(B) that will reference only the names from the first worksheet.

My issue is that WS A is constantly changing (names added/deleted) and WS B
needs to keep the rows matched up with the correct people (see below):

WS A

Jan 1 Jan 2
Jon Doe Absent
Jack Bauer Absent
Zoe Zoe

WS B

Jon Doe FTO
Jack Bauer FTO
Zoe Zoe

.... SO, Jon Doe was deleted... WS B would look like this:

Jack Bauer FTO
Zoe Zoe FTO

Which is incorrect... any suggestions?

Reply to this Message

Fred Smith - 30 Mar 2007 05:32 GMT

Use Match to find the row number the student is on in WS A, and then Index to
access a particular cell on that row. Then copy or formulate the result in each
column (depending on how you get 'FTO' from 'Absent'). Something like:

B2: =index(WSA!A:Z,match(a2,WSA!A:A,0),2)

Signature

Regards,
Fred

>I have an attendance worksheet (A) and need to create an addition spreadsheet
> (B) that will reference only the names from the first worksheet.
[quoted text clipped - 21 lines]
>
> Which is incorrect... any suggestions?

Reply to this Message

anna - 30 Mar 2007 17:54 GMT

Thanks Fred - My example isn't entirely accurate, as I really do not want to
pull any values off WSA, EXCEPT the names... WSB values will be entered
separately, by hand.

Sorry if this makes no sense... basically, I want to keep the names on WSB
current by pulling them off of WSA, but not pulling any other values off of
it.

> Use Match to find the row number the student is on in WS A, and then Index to
> access a particular cell on that row. Then copy or formulate the result in each
[quoted text clipped - 27 lines]
> >
> > Which is incorrect... any suggestions?

Reply to this Message

Fred Smith - 31 Mar 2007 15:36 GMT

You're right, it doesn't make any sense to me. If you're not referencing WSA for
anything, then why does it cause problems on WSB?

Let's deal just with WSB for now. What are the formulas? Where do the names come
from? And where does FTO come from?

When you delete, for example, Jon Doe, how do you do it? Do you delete the row,
or do you delete just the cell?

Signature

Regards,
Fred

> Thanks Fred - My example isn't entirely accurate, as I really do not want to
> pull any values off WSA, EXCEPT the names... WSB values will be entered
[quoted text clipped - 37 lines]
>> >
>> > Which is incorrect... any suggestions?

Reply to this Message