B
Baba
Hey All,
Here is the deal.. am on a project for a client for 1 more week. and i
want to leave an impression on the client.. The project is quite
difficult due to so many requirements to make the app. robust and to
do all this in Excel only (maybe access if needed). so I want to leave
the project in a position that they would be impressed enough to bring
me back one day. So I am cutting the fat and giving them a tight,
skinny version of what was requested of me.
Anyway, I am trying to bring in a worksheet with some data. To better
explain it.. the data is a Trial Balance (Act #, Act. Descrip, Debit
Amt and Credit Amt). The Debit and Credit Amts change.. but most of
the time the struct is the same. unless there are new accounts or
deleted accounts due to reorg or new regs.
I want to be able to bring in the TB from the other workbook.. put it
in "Data" worksheet.. location would probably start at "A1" with
header row in first row... but not guaranteed. Then look up line for
line comparison to the TEMPLATE TB already in the file (from previous
use) - as the TB also has some mapping for data and formulas
attached.. If all match up .. All accounts and account count in New
DATA TB (NDTB) = Template TB (TTB).. then proceed forward.. else mark
of the item that is new/deleted in the New Data TB.
Maybe a check can be done first through checking count of records in
used range.. and then make sure the accounts match.
If Template has more accounts.. hightlight them... if NDTB has more
accounts, the highlight them in NDTB.
So for example say NDTB has accts 1,2,3,4,5 and TBB has 1,2,3,4,5,6,7
... .then I would like the row/cell for Accts. 6 and 7 change color.
in TTB. and a msgbox popping up and saying "New Accounts have been
created since template generated" and the opposite if accounts are
missing from the NDTB. And the ideal would be to line up the areas
where the missing items are.. so if NDTB has 200 items (A1:A200)...
and TTB has 210.. then NDTB should have 10 blank colored rows. if the
situation is reverse, then either highlight the 10 that aren't in the
TTB or move them to bottom/top and consolidate the list. If the user
updates the TBB (through accepting a msgbox "Do you want to update the
template?" ) then run process again.. If all rows (accct #) are the
same .. then "IMPORT" is complete!
As for points, if I get a solution, I am willing to purchase and
increase the points to more.. but until then, I am going to just keep
increasing the points on a daily basis as I acquire them.. But if the
solution is helpful - I could give 250 points if it is exactly what i
want... I am willing to give 500.
It is not worth purchasing more points if I am only going to be here
for 1 more week.
I look forward to your creative solutions... Oh.. btw - EXCEL 97
now... Excel 2000 for few but definitely in the future (so go for 97
solution first if there are more than one due to version changes) and
VBA/Macros is fine.. but if it can be done in EXCEL (some how??!) that
is great..
PLEASE.. again.. I have less than 1 week to get this to a spot that i
can turn it over!!
Thanks.
Alibabas
Here is the deal.. am on a project for a client for 1 more week. and i
want to leave an impression on the client.. The project is quite
difficult due to so many requirements to make the app. robust and to
do all this in Excel only (maybe access if needed). so I want to leave
the project in a position that they would be impressed enough to bring
me back one day. So I am cutting the fat and giving them a tight,
skinny version of what was requested of me.
Anyway, I am trying to bring in a worksheet with some data. To better
explain it.. the data is a Trial Balance (Act #, Act. Descrip, Debit
Amt and Credit Amt). The Debit and Credit Amts change.. but most of
the time the struct is the same. unless there are new accounts or
deleted accounts due to reorg or new regs.
I want to be able to bring in the TB from the other workbook.. put it
in "Data" worksheet.. location would probably start at "A1" with
header row in first row... but not guaranteed. Then look up line for
line comparison to the TEMPLATE TB already in the file (from previous
use) - as the TB also has some mapping for data and formulas
attached.. If all match up .. All accounts and account count in New
DATA TB (NDTB) = Template TB (TTB).. then proceed forward.. else mark
of the item that is new/deleted in the New Data TB.
Maybe a check can be done first through checking count of records in
used range.. and then make sure the accounts match.
If Template has more accounts.. hightlight them... if NDTB has more
accounts, the highlight them in NDTB.
So for example say NDTB has accts 1,2,3,4,5 and TBB has 1,2,3,4,5,6,7
... .then I would like the row/cell for Accts. 6 and 7 change color.
in TTB. and a msgbox popping up and saying "New Accounts have been
created since template generated" and the opposite if accounts are
missing from the NDTB. And the ideal would be to line up the areas
where the missing items are.. so if NDTB has 200 items (A1:A200)...
and TTB has 210.. then NDTB should have 10 blank colored rows. if the
situation is reverse, then either highlight the 10 that aren't in the
TTB or move them to bottom/top and consolidate the list. If the user
updates the TBB (through accepting a msgbox "Do you want to update the
template?" ) then run process again.. If all rows (accct #) are the
same .. then "IMPORT" is complete!
As for points, if I get a solution, I am willing to purchase and
increase the points to more.. but until then, I am going to just keep
increasing the points on a daily basis as I acquire them.. But if the
solution is helpful - I could give 250 points if it is exactly what i
want... I am willing to give 500.
It is not worth purchasing more points if I am only going to be here
for 1 more week.
I look forward to your creative solutions... Oh.. btw - EXCEL 97
now... Excel 2000 for few but definitely in the future (so go for 97
solution first if there are more than one due to version changes) and
VBA/Macros is fine.. but if it can be done in EXCEL (some how??!) that
is great..
PLEASE.. again.. I have less than 1 week to get this to a spot that i
can turn it over!!
Thanks.
Alibabas