Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing another WB's cells, but not formulas/values.
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing another WB's cells, but not formulas/values.
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) -- Regards, Fred "anna" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing another WB's cells, but not formulas/values.
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. "Fred Smith" wrote: 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) -- Regards, Fred "anna" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing another WB's cells, but not formulas/values.
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? -- Regards, Fred "anna" wrote in message ... 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. "Fred Smith" wrote: 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) -- Regards, Fred "anna" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
Lookup Function Referencing cells, not text values | Excel Worksheet Functions | |||
How do I avoid referencing hidden values in formulas like OFFSET? | Excel Worksheet Functions | |||
formulas referencing pivot table cells | Excel Worksheet Functions | |||
How do I view formulas by values not by which cells they use to c. | Excel Discussion (Misc queries) |