Another formula question.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Robert N - 21 Sep 2007 21:18 GMT

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

Reply to this Message

JW - 21 Sep 2007 21:30 GMT

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.

> 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
[quoted text clipped - 6 lines]
> Thanks
> Rob

Reply to this Message

Robert N - 21 Sep 2007 22:22 GMT

Thanks I thought it might be kind of easy.

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,
[quoted text clipped - 14 lines]
> > Thanks
> > Rob

Reply to this Message

Balan - 22 Sep 2007 14:04 GMT

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

> Thanks I thought it might be kind of easy.
>
[quoted text clipped - 18 lines]
> > > Thanks
> > > Rob

Reply to this Message

Robert N - 24 Sep 2007 16:30 GMT

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

> Mr Robert,
> It's not clear why you want the same data in the same sort order on two
[quoted text clipped - 44 lines]
> > > > Thanks
> > > > Rob

Reply to this Message

Sandy Mann - 24 Sep 2007 18:40 GMT

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.

Signature

HTH

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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Here is the problem that i am facing. On sheet one I have all my
> employees
[quoted text clipped - 77 lines]
>> > > > Thanks
>> > > > Rob

Reply to this Message