Relative goes absolute between worksheets in data sort!

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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
> > news:[email protected]...
> > >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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>>[...]