Automatic Updates between worksheets

[Rob]

I am in urgent need of some advice.

I have a worksheet that contains information in priority
order, one part contains numeric info (money!), I want to
add a related sheet that keeps some fields as per sheet 1
(name, subject, etc) but then has descriptions (text).

My problem is ensuring that if a priority changes, how can
you make sure both sheets are updated automatically?

For example: Lines 1 - 5 are in priority order. If Line 4
then becomes higher priority than line 2 how can I switch
their places ON BOTH SHEETS?

HELP! This is costing me hair!!

 

[Frank Kabel]

Hi
so you want an automatic sort if you change the priority?.
If yes this would require VBA on the first sheet. If you
could outline your data structure a little bit more you
could also use some array formulas on a second sheet to
automatically sort the data according to their priority
using INDEX, and SMALL for example)

 

[Guest]

My sheets are laid out at the moment as:

Sheet 1:
Column 1 to 3: Text
Column 3 to 6: Currency
Column 7: Text
Column 8: Numeric
9 to 26: Currency

Sheet 2:
Column 1 to 3: Text
Column 4: Currency
Column 5 to 17: Text

I am trying to work it that any changes to Sheet 1 (entire
rows) adjust the Sheet 2 rows, but only columns 1 to 4.

I hope this makes sense, and thanks for your help!

 

[Frank Kabel]

Hi Rob
then just use these formul I posted. e.g. goto cell A1 on
sheet2 and enter
='sheet1'!A1
copy this formula down and to the right

 

[Rob]

I tried that but if I move a row up in Sheet 1 (i.e.,
reprioritise the list) it doesn't copy the new position to
Sheet 2 also.

Example:

Sheet 1: (in priority order)

Scotland
Ireland
England
Wales
Germany

Sheet 2:

Scotland
Ireland
England
Wales
Germany

If you change the priority of Sheet 1:

Germany
England
Scotland
Ireland
Wales

then I want Sheet 2 to change also - without me having to
move it manually.

Thanks again!

Rob

 

[Frank Kabel]

Hi
how do you change the priority. e.g. if you just sort
sheet1 sheet2 should reflect the new order

 

[Rob]

I can't use sort as the detail in columns 1 to 3 are in
order according to my business, and not alphabetically or
numerically.

I suppose one solution would be to include a hidden column
numbering them - but then if I repriortised I'd have to
amend all the numbers?

It is a shame I can't send you the file - commercial in
confidence, etc.

Rob

 

[Rob]

Thanks for the post, I think if you check the other thread
of this subject it may shed some more light on my problem.

Thanks again!

Rob

-----Original Message-----
If you really need two sheets rather than two reports

from one sheet, the answer may be to have a third sheet
with the numerical data and the descriptions in rows
linked to the two reporting sheets.

 

[Guest]

So why don't you have all the data on one sheet? You have a one-to-one relationship between the data on each sheet, so its crying out to be a single list. Then, if you need to report the different fields separately you can created different reports with cross links, or a pivot table, or using the report manager add-in.

 

[Frank Kabel]

Hi
please explain how do you resort your data (that is changing the order
of your data?)

 

[Rob]

I insert a row where it is needed and then select the row
to move, and drag and drop it. (The data contained in the
columns is saved as named ranges.)

I don't understand VBA or PivotTables at all. I have
already exceeded the limit of my knowledge.

Thanks for the continued assistance.

Rob

 

[Frank Kabel]

Hi Rob
I would suggest you do this differently
- use only ONE single sheet
- use 'Data - Filter' and 'Data - sort' for showing only the relevant
part of your information

 

[Rob]

I'll give it a try!

Thanks for all your help - invaluable.

As the saying goes: "I'll be back!"

Enjoy your weekend!