I can't believe this is happening!

In one worksheet ("MIXES") I have recipes that draw their individual
ingredient cost prices from a large product range in another worksheet
("PRICELOOKUP) in the same workbook. I applied range names to the relevant
unit cost fields in PRICELOOKUP, and referenced those in the formulae in
MIXES. After new lines are added, PRICELOOKUP is sorted alphabetically by
Supplier and then Product. The result: All the range names behave
"absolute", failing to move to the relevant new (row) position, thus
referencing an entirely spurious product after the sort.

I tried abandoning range names and just referencing the cell. Same result.
This has never happened to me after sorting within the same worksheet, and I
never suspected that it would be any different referencing between
worksheets.

Has anyone found a workaround for this problem?

Hi

range names are always absolute.

however, if i'm understanding you correctly on one sheet ("Mixes") you have
recipies
i.e.
A1 = Flour
B1 = 1cup
C1 = ?desired unit cost

and then on the worksheet "Pricelookup" you have, for example
A1 = Flour
B1 = 1
(being $1 for a cup of flour)

if this is the case i would use a VLOOKUP formula in C1 to put in the unit
cost, this then would work even if the Pricelookup sheet is sorted
e.g.
=VLOOKUP(A1,Pricelookup!$A$1:$B$1000,2,0)
which says, look up the value in A1, in the pricelookup worksheet in the
range A1:A1000 and return the associated information from the 2nd column of
the lookup table, where there is an exact match.

Does this help?
Cheers
JulieD

"Kevryl" wrote in message
...
I can't believe this is happening!

In one worksheet ("MIXES") I have recipes that draw their individual
ingredient cost prices from a large product range in another worksheet
("PRICELOOKUP) in the same workbook. I applied range names to the relevant
unit cost fields in PRICELOOKUP, and referenced those in the formulae in
MIXES. After new lines are added, PRICELOOKUP is sorted alphabetically by
Supplier and then Product. The result: All the range names behave
"absolute", failing to move to the relevant new (row) position, thus
referencing an entirely spurious product after the sort.

I tried abandoning range names and just referencing the cell. Same result.
This has never happened to me after sorting within the same worksheet, and
I
never suspected that it would be any different referencing between
worksheets.

Has anyone found a workaround for this problem?

Hi Julie,

Thanks for your time here.

To use VLookup now would require a huge restructure of a large spreadsheet.
There was a stage when I used Vlookup for a different purpose in this
spreadsheet system , but found that even when its need for meticulously
sorted data was satisfied, it was still too unreliable to stay with. It also
made the entire spreadsheet run inordinately slow once there were more than a
few hundred products in it..

I now have about 2,000 different products in "LOOKUP", a line to each.
"Supplier, Supplier's code, Description, OP, Latest Invoice date, OP cost,
GST idicator, etc etc etc run along the row. The calculated Unit Cost is at
column O, and this is the cell I have tried to reference from "RECIPES"

Some of these products are combinations of others, being mixed in the shop
by ourselves - such as nibble mixes with up to a dozen fruit and nut style
ingredients.
It is these recipes that are compiled in "RECIPES". It should be a simple
matter to have "RECIPES" reference the unit cost cell in "LOOKUP", and flag a
warning message when the G.P. on a recipe falls below a certain level as a
result of price rises on individual ingredients. I was surprised that applied
range names would not properly 'migrate' after a data-sort to their new row
positions, but even more surprised that simple relative cell references would
also not migrate only when referenced from another worksheet within the same
work-book.

The whole system is really overly complex for a spreadsheet system and
should more properly be a database application. However that would require
far more development time than I have available, and seeing I've been working
with spreadsheets (Lotus, Enable and excel) since 1985 I'm going to patch
this system up until we implement an automated POS system. In most ways, the
spreadsheet system I've developed is very efficient and does what I want it
to.

I shall work around this one, perhaps by dedicating some rows at the top of
"LOOKUP" to duplicate cost data on products that are ingredients, so that
they are unaffected by a data sort. This way, "RECIPES" will reference data
never sorted. Clumsy, but it'll get me out of a problem for the time I need
it to.

Hey, thanks again.
Kind regards



"JulieD" wrote:

Hi

range names are always absolute.

however, if i'm understanding you correctly on one sheet ("Mixes") you have
recipies
i.e.
A1 = Flour
B1 = 1cup
C1 = ?desired unit cost

and then on the worksheet "Pricelookup" you have, for example
A1 = Flour
B1 = 1
(being $1 for a cup of flour)

if this is the case i would use a VLOOKUP formula in C1 to put in the unit
cost, this then would work even if the Pricelookup sheet is sorted
e.g.
=VLOOKUP(A1,Pricelookup!$A$1:$B$1000,2,0)
which says, look up the value in A1, in the pricelookup worksheet in the
range A1:A1000 and return the associated information from the 2nd column of
the lookup table, where there is an exact match.

Does this help?
Cheers
JulieD

"Kevryl" wrote in message
...
I can't believe this is happening!

In one worksheet ("MIXES") I have recipes that draw their individual
ingredient cost prices from a large product range in another worksheet
("PRICELOOKUP) in the same workbook. I applied range names to the relevant
unit cost fields in PRICELOOKUP, and referenced those in the formulae in
MIXES. After new lines are added, PRICELOOKUP is sorted alphabetically by
Supplier and then Product. The result: All the range names behave
"absolute", failing to move to the relevant new (row) position, thus
referencing an entirely spurious product after the sort.

I tried abandoning range names and just referencing the cell. Same result.
This has never happened to me after sorting within the same worksheet, and
I
never suspected that it would be any different referencing between
worksheets.

Has anyone found a workaround for this problem?

Oooops! When I said "RECIPES" I was referring to the "MIXES" - sorry if I
confused :-(

"Kevryl" wrote:

Hi Julie,

Thanks for your time here.

To use VLookup now would require a huge restructure of a large spreadsheet.
There was a stage when I used Vlookup for a different purpose in this
spreadsheet system , but found that even when its need for meticulously
sorted data was satisfied, it was still too unreliable to stay with. It also
made the entire spreadsheet run inordinately slow once there were more than a
few hundred products in it..

I now have about 2,000 different products in "LOOKUP", a line to each.
"Supplier, Supplier's code, Description, OP, Latest Invoice date, OP cost,
GST idicator, etc etc etc run along the row. The calculated Unit Cost is at
column O, and this is the cell I have tried to reference from "RECIPES"

Some of these products are combinations of others, being mixed in the shop
by ourselves - such as nibble mixes with up to a dozen fruit and nut style
ingredients.
It is these recipes that are compiled in "RECIPES". It should be a simple
matter to have "RECIPES" reference the unit cost cell in "LOOKUP", and flag a
warning message when the G.P. on a recipe falls below a certain level as a
result of price rises on individual ingredients. I was surprised that applied
range names would not properly 'migrate' after a data-sort to their new row
positions, but even more surprised that simple relative cell references would
also not migrate only when referenced from another worksheet within the same
work-book.

The whole system is really overly complex for a spreadsheet system and
should more properly be a database application. However that would require
far more development time than I have available, and seeing I've been working
with spreadsheets (Lotus, Enable and excel) since 1985 I'm going to patch
this system up until we implement an automated POS system. In most ways, the
spreadsheet system I've developed is very efficient and does what I want it
to.

I shall work around this one, perhaps by dedicating some rows at the top of
"LOOKUP" to duplicate cost data on products that are ingredients, so that
they are unaffected by a data sort. This way, "RECIPES" will reference data
never sorted. Clumsy, but it'll get me out of a problem for the time I need
it to.

Hey, thanks again.
Kind regards



"JulieD" wrote:

Hi

range names are always absolute.

however, if i'm understanding you correctly on one sheet ("Mixes") you have
recipies
i.e.
A1 = Flour
B1 = 1cup
C1 = ?desired unit cost

and then on the worksheet "Pricelookup" you have, for example
A1 = Flour
B1 = 1
(being $1 for a cup of flour)

if this is the case i would use a VLOOKUP formula in C1 to put in the unit
cost, this then would work even if the Pricelookup sheet is sorted
e.g.
=VLOOKUP(A1,Pricelookup!$A$1:$B$1000,2,0)
which says, look up the value in A1, in the pricelookup worksheet in the
range A1:A1000 and return the associated information from the 2nd column of
the lookup table, where there is an exact match.

Does this help?
Cheers
JulieD

"Kevryl" wrote in message
...
I can't believe this is happening!

In one worksheet ("MIXES") I have recipes that draw their individual
ingredient cost prices from a large product range in another worksheet
("PRICELOOKUP) in the same workbook. I applied range names to the relevant
unit cost fields in PRICELOOKUP, and referenced those in the formulae in
MIXES. After new lines are added, PRICELOOKUP is sorted alphabetically by
Supplier and then Product. The result: All the range names behave
"absolute", failing to move to the relevant new (row) position, thus
referencing an entirely spurious product after the sort.

I tried abandoning range names and just referencing the cell. Same result.
This has never happened to me after sorting within the same worksheet, and
I
never suspected that it would be any different referencing between
worksheets.

Has anyone found a workaround for this problem?

Actually Julie, thinking about that thats not strictly true. Try this:

Select A1:B10 and call it "Julie"

drop down to row 5 or so and insert a few rows.

Now hit F5 and type "Julie" and enter, and ...
Voila, your range has expanded to incorporate the new rows.

Regards,
Keith

"JulieD" wrote:

Hi

range names are always absolute.
[...]

Hi Kevryl

i've not had the same experience with VLOOKUP being unreliable, however,
yes, it does slow things down (a lot!) ...

index & match are another alternative that might work for you ... have you
tried these?

Cheers
JulieD



"Kevryl" wrote in message
...
Hi Julie,

Thanks for your time here.

To use VLookup now would require a huge restructure of a large
spreadsheet.
There was a stage when I used Vlookup for a different purpose in this
spreadsheet system , but found that even when its need for meticulously
sorted data was satisfied, it was still too unreliable to stay with. It
also
made the entire spreadsheet run inordinately slow once there were more
than a
few hundred products in it..

I now have about 2,000 different products in "LOOKUP", a line to each.
"Supplier, Supplier's code, Description, OP, Latest Invoice date, OP cost,
GST idicator, etc etc etc run along the row. The calculated Unit Cost is
at
column O, and this is the cell I have tried to reference from "RECIPES"

Some of these products are combinations of others, being mixed in the shop
by ourselves - such as nibble mixes with up to a dozen fruit and nut style
ingredients.
It is these recipes that are compiled in "RECIPES". It should be a simple
matter to have "RECIPES" reference the unit cost cell in "LOOKUP", and
flag a
warning message when the G.P. on a recipe falls below a certain level as a
result of price rises on individual ingredients. I was surprised that
applied
range names would not properly 'migrate' after a data-sort to their new
row
positions, but even more surprised that simple relative cell references
would
also not migrate only when referenced from another worksheet within the
same
work-book.

The whole system is really overly complex for a spreadsheet system and
should more properly be a database application. However that would require
far more development time than I have available, and seeing I've been
working
with spreadsheets (Lotus, Enable and excel) since 1985 I'm going to patch
this system up until we implement an automated POS system. In most ways,
the
spreadsheet system I've developed is very efficient and does what I want
it
to.

I shall work around this one, perhaps by dedicating some rows at the top
of
"LOOKUP" to duplicate cost data on products that are ingredients, so that
they are unaffected by a data sort. This way, "RECIPES" will reference
data
never sorted. Clumsy, but it'll get me out of a problem for the time I
need
it to.

Hey, thanks again.
Kind regards



"JulieD" wrote:

Hi

range names are always absolute.

however, if i'm understanding you correctly on one sheet ("Mixes") you
have
recipies
i.e.
A1 = Flour
B1 = 1cup
C1 = ?desired unit cost

and then on the worksheet "Pricelookup" you have, for example
A1 = Flour
B1 = 1
(being $1 for a cup of flour)

if this is the case i would use a VLOOKUP formula in C1 to put in the
unit
cost, this then would work even if the Pricelookup sheet is sorted
e.g.
=VLOOKUP(A1,Pricelookup!$A$1:$B$1000,2,0)
which says, look up the value in A1, in the pricelookup worksheet in the
range A1:A1000 and return the associated information from the 2nd column
of
the lookup table, where there is an exact match.

Does this help?
Cheers
JulieD

"Kevryl" wrote in message
...
I can't believe this is happening!

In one worksheet ("MIXES") I have recipes that draw their individual
ingredient cost prices from a large product range in another worksheet
("PRICELOOKUP) in the same workbook. I applied range names to the
relevant
unit cost fields in PRICELOOKUP, and referenced those in the formulae
in
MIXES. After new lines are added, PRICELOOKUP is sorted alphabetically
by
Supplier and then Product. The result: All the range names behave
"absolute", failing to move to the relevant new (row) position, thus
referencing an entirely spurious product after the sort.

I tried abandoning range names and just referencing the cell. Same
result.
This has never happened to me after sorting within the same worksheet,
and
I
never suspected that it would be any different referencing between
worksheets.

Has anyone found a workaround for this problem?

Hi Kevryl

then neither are absolute ranges e.g.

if you have =SUM($A$1:$A$10)
and then insert a couple of rows in the middle of the range the "absolute"
formula will change too

Cheers
JulieD

"Kevryl" wrote in message
...
Actually Julie, thinking about that thats not strictly true. Try this:

Select A1:B10 and call it "Julie"

drop down to row 5 or so and insert a few rows.

Now hit F5 and type "Julie" and enter, and ...
Voila, your range has expanded to incorporate the new rows.

Regards,
Keith

"JulieD" wrote:

Hi

range names are always absolute.
[...]