Use index to sort the records on the other page and keep them in the
appropriate row.

For example, if A2 is a link returning the employee from A2 on the master
sheet, and in B2 you want to return their address (stored on the sheet
'Master' under column B in a table named EE_Data), then B2
=VLOOKUP(INDEX(A:A,ROW()),EE_Data,2,FALSE)

--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Liz" wrote:

the absolute thing makes sense... and probably the entire workbook should be
set up in a different way altogether; I'd just like for the rest of the
record (the other columns in the worksheets) to sort with the referenced
column... for example

sheet 1 contains

staff names (columnA) contract status/active or inactive (columnB)

sheet 2 contains

staff names (columnA, referenced from sheet1) and staff street address
(columnB) and city, state, zip (columnC)

the problem occurs when i sort sheet one by contract status to place
"active" staff at the top of the list, sheet2 sorts the staff names ONLY and
not their addresses, so all the addresses are mixed up

is this even possible to do? i really appreciate all of your help... you
must be very patient to deal with tech dummies like me!

"nastech" wrote:

hi, sorry, will help best I can, first example is just to show naming
convention to another workbook (have to put workbook name infront of cells
you want to address/ make equation out of. you might need more help there /
put some expirementation to get data from 1 workbook to show up in another.
start with a small step first like that, once you see the data, you can start
to manipulate it.

the example with the TEXT() function was neccessary to sort multiple items /
columns;

0000 0001 00000
0001 supposed to be 2nd column, or:
0000 1000 would fail if the 1 was supposed to be the 4th character
0000 12 same idea

example would show how sort would fall apart if did not have preceding zero's.
if 1 falls to front for sorting


below A0 is a name column, i would see an error there if some name have
different lengths. it is not here because it is so far up front.

the items here can all be turned on / off separately with use of fixed cells
(excel calls them "absolute" cells: use a $ in front of cell names, e.g.:
$A$1
placing a 1 in those cells, as this formula is set up, allows sorting of
that column.

might be other things you need to know, can't guesse them all here.
formatting / I chose numbers because they take less space then a "letter"
response would need to be surrounded by quotation marks -&- you might need
to right click on pertinent cells to change the formatting as needed,
General Format lets formulas's work, but may need to choose between number or
text (general covers both).

- placing items in the order you want to sort by, if they are all selected,
will be important.

- formula's are set up in a: =IF(this,TRUE,FALSE) or IF(A1="","",B1+B2)
which reads if A1 equals nothing, then nothing, else add b1 & b2.

"Liz" wrote:

i really appreciate your help but i am not at all fluent in excel, just
enough to get by, so that formula has blown me away... is it really that
complicated to sort the records in sheet 1 and have the referenced records
sort in the other sheets as well? i can get the names to sort in the other
sheets but the rest of the record in the other sheets does not move with the
name... i hope i'm making sense!

"nastech" wrote:

hi, I excpect this example would only partially help. identifying items in
other books / sheets will require some naming convetions such as:
new! would be a different book name (different spreadsheet)

=IF(ISNA(VLOOKUP(A1,new!$A$1:$A$5000,1,0)),"unique ","duplicate")

I developed a sorting formula that works well. reciprocol (1/x...) is used
for reverse sorting a number column; the TEXT() function allows preceding
0's for accurate sorting: (putting formula in cell with line returns still
lets formula work)
hope this helps :)

=IF($DI$2=2,DR9,99)&
IF(AND($DI$2=1,D9=0,DI90),TEXT(1/(DI9*10^-9),"0000000"),9995555)&
IF($AN$2=1,AO9,9)&
IF($BX$2=1,TEXT(BX9,"00"),99)&
IF(AND($BT$2=1,BT9<""),TEXT(ROUND(BT9,0),"00"),99 )&
IF(AND($BW$2=1,BW9<{"-",""}),0,9)&
IF(AND($BS$2=1,BS9<{"-",""}),0,9)&
IF(AND($BO$2=1,BO9<{"-",""}),0,9)&
IF($BH$2=1,BH9,9)&
IF(AND($AA$2=1,F9=1),"xx",IF($AA$2=1,AA9,9))&
IF($BB$2=1,BB9,9)&
IF($BE$2=1,BE9,9)&
IF(BZ9="",9,BZ9)




"Liz" wrote:

i have a workbook containing multiple (about 10) worksheets with a reference
to sheet 1 for staff names, how can i sort the record on sheet 1 by the
reference (staff name) and ensure that the records in the other sheet have
moved as well... (i.e., if a staff member leaves, and i make that person
"inactive," how can i sort that staff member out of the other worksheets as
well?)