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?)

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?)

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?)

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?)

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?)

no thats fine, I am just a visitor here as well, sometimes a mvp microsoft
professional steps in to help answer these. I just have some info wouldn't
get otherwise, but I have been having a problem with this that directly
applies too.

was going to repost to ask again about finding data in another sheet. I can
find out more and get back to you. but it goes something like example given:
VLOOKUP()
to compare an exact value (name or symbol) to compare between the two sheets.

then give me the data from the other sheet, into this sheet. you might
already have some of that for an example. what I needed it for / would do is
a copy-paste-special the needed data to the column it belongs in my main /
new sheet.
this was for data recovery purposes. you may need a more static / permanent
column that keeps that data. would be simple once you have the start I was
saying I didn't quite have now.. I am guessing you already have that start.

what else... lets see: sorting would only happen in one sheet.. you select
the whole set of rows by hiliting them on the left side, you then sort with:
DATA, SORT, select columns ascending / descending (you only have 3 columns
can choose) with my formula shows 13 columns I sort on "Z", plus there's 2
more in the sort utilty.

NOTE: Your problem I see is you only selected certain cells to sort. you
can't do that if you want to keep the rest of the columns with in there rows
/ same records.
select the whole row(s) like I said. I don't know about sorting from one
sheet to sort another. don't know if you can do that. without more info
from someone else here, I would say you either cannot do that, or want to be
careful.

"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?)

thanks so much for your help... i've been looking at how to do a vlookup and
i think i may have figured out a way to look up the data i need and just
display it on a different sheet
you've been great! thanks!~

"nastech" wrote:

no thats fine, I am just a visitor here as well, sometimes a mvp microsoft
professional steps in to help answer these. I just have some info wouldn't
get otherwise, but I have been having a problem with this that directly
applies too.

was going to repost to ask again about finding data in another sheet. I can
find out more and get back to you. but it goes something like example given:
VLOOKUP()
to compare an exact value (name or symbol) to compare between the two sheets.

then give me the data from the other sheet, into this sheet. you might
already have some of that for an example. what I needed it for / would do is
a copy-paste-special the needed data to the column it belongs in my main /
new sheet.
this was for data recovery purposes. you may need a more static / permanent
column that keeps that data. would be simple once you have the start I was
saying I didn't quite have now.. I am guessing you already have that start.

what else... lets see: sorting would only happen in one sheet.. you select
the whole set of rows by hiliting them on the left side, you then sort with:
DATA, SORT, select columns ascending / descending (you only have 3 columns
can choose) with my formula shows 13 columns I sort on "Z", plus there's 2
more in the sort utilty.

NOTE: Your problem I see is you only selected certain cells to sort. you
can't do that if you want to keep the rest of the columns with in there rows
/ same records.
select the whole row(s) like I said. I don't know about sorting from one
sheet to sort another. don't know if you can do that. without more info
from someone else here, I would say you either cannot do that, or want to be
careful.

"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?)

don't be afraid to repost ever, do it now if you want,, i just posted 2
items ahead of yours, got no answer because there were 2 entries.. what-ever.
someone else can give you a VLOOKUP answer tonight.

"Liz" wrote:

thanks so much for your help... i've been looking at how to do a vlookup and
i think i may have figured out a way to look up the data i need and just
display it on a different sheet
you've been great! thanks!~

"nastech" wrote:

no thats fine, I am just a visitor here as well, sometimes a mvp microsoft
professional steps in to help answer these. I just have some info wouldn't
get otherwise, but I have been having a problem with this that directly
applies too.

was going to repost to ask again about finding data in another sheet. I can
find out more and get back to you. but it goes something like example given:
VLOOKUP()
to compare an exact value (name or symbol) to compare between the two sheets.

then give me the data from the other sheet, into this sheet. you might
already have some of that for an example. what I needed it for / would do is
a copy-paste-special the needed data to the column it belongs in my main /
new sheet.
this was for data recovery purposes. you may need a more static / permanent
column that keeps that data. would be simple once you have the start I was
saying I didn't quite have now.. I am guessing you already have that start.

what else... lets see: sorting would only happen in one sheet.. you select
the whole set of rows by hiliting them on the left side, you then sort with:
DATA, SORT, select columns ascending / descending (you only have 3 columns
can choose) with my formula shows 13 columns I sort on "Z", plus there's 2
more in the sort utilty.

NOTE: Your problem I see is you only selected certain cells to sort. you
can't do that if you want to keep the rest of the columns with in there rows
/ same records.
select the whole row(s) like I said. I don't know about sorting from one
sheet to sort another. don't know if you can do that. without more info
from someone else here, I would say you either cannot do that, or want to be
careful.

"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?)

hi, don't know if you're still there but found answer for doing data recovery
using the VLOOKUP function. For your problem, think you may have
inappropiately sorted / mis-aligned data. (i.e., do not use AZ sort button
to sort selected cells, select whole rows & consider using the actual sort
tool only, skip the button).

for this formula know difference for which sheet you're in, to label columns
correctly (assumes same reference in both sheets is col A)

=IF(OR(A8={"","."},ISNA(VLOOKUP(A8,[file.xls]sheet!$A$1:$B$3355,(COLUMN(B1)-COLUMN(A1))+1,FALSE))),"",VLOOKUP(A8,[file.xls]sheet!$A$1:$B$3355,(COLUMN(B1)-COLUMN(A1))+1,FALSE))


both files are in same directory, use Ctrl-Shift-Enter to commit an array
formula.

"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?)

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?)

hi, still there? sorry for the repeat answers, but if recovering data was
what you were trying to do, I have seen how difficult using my formula was a
2nd time.
to copy / replace data without errors.. and "trying" to make it as easy as
possible, to not have to count columns with the VLOOKUP I came up with a
better formula / instructions, I put in $ signs where needed to make these
directions work:

Copy, leave space in front of ='sign'/ do not commit, change
file/sheet-name; find-replace: 1. $A with col letter of ref col (eg:
$AQ), 2. $B with col of lost data 3. $AQ1 with row you place this in/
col ref'd (eg: $AQ100),


=IF(OR($A1={"","."},ISNA(VLOOKUP($A1,[file.xls]sheet!$A$1:$B$3355,(COLUMN($B$1)-COLUMN($A$1))+1,FALSE))),"",VLOOKUP($A1,[file.xls]sheet!$A$1:$B$3355,(COLUMN($B$1)-COLUMN($A$1))+1,FALSE))


hope this helps, that should do it

"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?)