G
Guest
okay, so I have a pretty complex problem here...so, I need some good thinkers
to help me out.
description: check out this mockup table:
(col A) (col B) (col C) (col D) (col E)
(col F) (col G) (col H)
prodNum | ProdType | Category | IsleNum | Size | Wt. | Name |
Color
----------------------------------------------------------------------------------------------
0 couch lving 15 lg
200 leather blk
1 couch bdrm 15 sm
75 futon blk
2 couch den 14 lg
230 leisure brwn
3 chair kitch 3 sm
10 wood oak
4 chair lving 7 md
22 leather blue
5 chair lving 7 lg
200 leather2 blk
6 chair outside 1 sm
7 plastic wht
7 chair kitch 4 md
15 leather3 brwn
Okay, i think that is enough to get the drift.....
NOTICE: there can be multiple ProdTypes, but each ProdType has its own
Name. If there are two ProdTypes with thesame name, a number is appended to
the end of the name (see chair->leather and chair->leather2 and
chair->leather3).
Suppose there are two identical forms....an "old" and a "new" form. If
something gets added to the inventory, the new form updates the information
(adding it and sorting it into its correct place). however, the old form is
not notified of this update, so i need to make some equations that will
search for the updates when the forms are together in the same project. So,
i will have 2 worksheets: OldData and NewData. I want to record 2 things:
when i get a new ProdType into NewData, a new ProdName into NewData, or
update info (size, islenum, wt, etc... any one or combination of them) for a
ProdType. I want the equations to be put in a "Change Records" datasheet
that will compare the NewData sheet to the OldData sheet. The Change Records
sheet will have the same layout as the two other sheets. When a new
ProdType is added, all of the info for that ProdType (size, wt, isle, name,
color, etc) in the will be inserted into the ChangeRecords. When a new
new ProdType: i figure use a VLOOKUP for the ProdType..... suppose in this
case, it finds a new ProdType named Dresser. There are no dressers in the
ProdType currently. I figure that ChangeRecords sheet will do a VLOOKUP()
and find that the Dresser is a new entry and will populate itself with the
necessary info...this part to me will be the easiest.
new Name: same thing as the new ProdType...i can populate this fine. the
problem is with the update:
update ProdType: suppose a ProdType moves from isle 13 to 15. i will have
to first find the ProdType, then find the Name....then when i find that both
of those are the same, i check that same entry with the OldData, and if the
isle number is different, I put a "15" in for Isle in ChangeRecords along
with any updated info (such as a change of color if it changed from brown to
green)....any unchanged info is omitted.
so to check to make sure that olddata is same as newdata, i need to first
find the prodtype then find the corresponding name. i check that prodtype
and name (using a nested VLOOKUP???) against the olddata, and if anythign
changes i record it.
so, what would be the equation to do this (and no, i dont want to use a
macro...this must be an equation)
THANKS!
to help me out.
description: check out this mockup table:
(col A) (col B) (col C) (col D) (col E)
(col F) (col G) (col H)
prodNum | ProdType | Category | IsleNum | Size | Wt. | Name |
Color
----------------------------------------------------------------------------------------------
0 couch lving 15 lg
200 leather blk
1 couch bdrm 15 sm
75 futon blk
2 couch den 14 lg
230 leisure brwn
3 chair kitch 3 sm
10 wood oak
4 chair lving 7 md
22 leather blue
5 chair lving 7 lg
200 leather2 blk
6 chair outside 1 sm
7 plastic wht
7 chair kitch 4 md
15 leather3 brwn
Okay, i think that is enough to get the drift.....
NOTICE: there can be multiple ProdTypes, but each ProdType has its own
Name. If there are two ProdTypes with thesame name, a number is appended to
the end of the name (see chair->leather and chair->leather2 and
chair->leather3).
Suppose there are two identical forms....an "old" and a "new" form. If
something gets added to the inventory, the new form updates the information
(adding it and sorting it into its correct place). however, the old form is
not notified of this update, so i need to make some equations that will
search for the updates when the forms are together in the same project. So,
i will have 2 worksheets: OldData and NewData. I want to record 2 things:
when i get a new ProdType into NewData, a new ProdName into NewData, or
update info (size, islenum, wt, etc... any one or combination of them) for a
ProdType. I want the equations to be put in a "Change Records" datasheet
that will compare the NewData sheet to the OldData sheet. The Change Records
sheet will have the same layout as the two other sheets. When a new
ProdType is added, all of the info for that ProdType (size, wt, isle, name,
color, etc) in the will be inserted into the ChangeRecords. When a new
new ProdType: i figure use a VLOOKUP for the ProdType..... suppose in this
case, it finds a new ProdType named Dresser. There are no dressers in the
ProdType currently. I figure that ChangeRecords sheet will do a VLOOKUP()
and find that the Dresser is a new entry and will populate itself with the
necessary info...this part to me will be the easiest.
new Name: same thing as the new ProdType...i can populate this fine. the
problem is with the update:
update ProdType: suppose a ProdType moves from isle 13 to 15. i will have
to first find the ProdType, then find the Name....then when i find that both
of those are the same, i check that same entry with the OldData, and if the
isle number is different, I put a "15" in for Isle in ChangeRecords along
with any updated info (such as a change of color if it changed from brown to
green)....any unchanged info is omitted.
so to check to make sure that olddata is same as newdata, i need to first
find the prodtype then find the corresponding name. i check that prodtype
and name (using a nested VLOOKUP???) against the olddata, and if anythign
changes i record it.
so, what would be the equation to do this (and no, i dont want to use a
macro...this must be an equation)
THANKS!