Another formula question.

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

Guest

I have another formula question for excel users. What i am doing is making a
spread sheet. On the first sheet i have the names of people on staff with
their sales. i copied those names and sales over to the next sheet. But on
the first sheet i want to put those names and sales in ascending order
according to sales, which i understand how to do. I want name and sales on
sheet 2 to adjust in acending order when i ascend sheet one. I hope i
explained this well enough for people to understand.

Thanks
Rob
 
So you want the same sort on each sheet, correct? Simply hold down
the Ctrl key, select both sheet tabs, perform your sort on sheet 1,
right click on the selected sheets and select Ungroup Sheets. Just be
certain that you ungroup the sheet or any changes that you make in one
sheet will be automatically made (possibly overwtiring data) in the
other sheet. Or, you can replace the data in sheet 2 with reference
formulas to sheet1. But you may find yourself adding formulas when
the data in sheet1 grows.
 
Thanks I thought it might be kind of easy.

Rob

JW said:
So you want the same sort on each sheet, correct? Simply hold down
the Ctrl key, select both sheet tabs, perform your sort on sheet 1,
right click on the selected sheets and select Ungroup Sheets. Just be
certain that you ungroup the sheet or any changes that you make in one
sheet will be automatically made (possibly overwtiring data) in the
other sheet. Or, you can replace the data in sheet 2 with reference
formulas to sheet1. But you may find yourself adding formulas when
the data in sheet1 grows.
Click to expand...
 
Mr Robert,
It's not clear why you want the same data in the same sort order on two
sheets . If that is how you want it, I believe you want the changes as and
when you do on sheet 1, it should automatically appear on sheet 2 in the same
way. If my understanding is correct, you can try the following:
i) go to one cell of your choice on sheet 2
ii) type " ="
iii) go to sheet 1 and select the first cell of your table
iv) Press enter
In the cell selected by you in sheet 2 , you will find the value of the
first cell of your data table on sheet 1
v) Now copy the contents of cell in sheet 2 to as many cells to the right as
well as down as you have data on sheet 1 (or you may have in more rows if you
choose to, if you feel the data on sheet 1 will increase over time)

As and when you make changes in sheet 1 ( including data sorting ), the
changes will be reflected in sheet 2 as it they are on sheet 1.

If you want to process the data on sheet 2 differently after the changes in
sheet 1, you may have to copy the entire range of data on sheet 2 and using
paste special convert them into mere values. The link with sheet 1 will
however get snapped by this process.
Hope this is useful.

Best Wishes
 
Here is the problem that i am facing. On sheet one I have all my employees
and thier sales on it with other things on it. I want it in ascending order
from the highest to losest. I have the last columns set for a 3 week
average. then on sheet 2 i have their names and sales copied from sheet
one.But when i go to set it from acsending on sheet one it doesnt change on
sheet two. I want to a acsend it from sheet one and make the same changes
one sheet two, but to follow a different column.
the things i want are as follows
name sales a b c d e f g h (a-g are different things) i is 3 week average.
column i is what i want to acsend from highest to lowest and change
following same thing automatically on sheet from the names and sales column.

I think this might help a little more.
Thanks Robert
 
Instead of building a formula in Sheet2 as Balan suggested, make them links
by copying the first cell in Sheet1 and then on the appropriate cell in
Sheet 2 Paste Special > Paste Link. Highlight the cell reference and press
the function key F4 until the reference is a relative reference with no $'s
then copy the cell using the fill handle to all the other cells.

The references will not change what the target cell moves.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top