Look up values from list

[PhantomOak]

I have inherited several terribly put together workbooks. They "track" costs for construction across several sheets (maybe in the hundreds) and within those sheets the original format (ie Cells A1:A5 containing amount of a transaction) has been bastardized to fit more information, making almost every sheet uniquely set up.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
<o></o>
Then I have a crystal report from MAS90 that shows what has actually been paid. (roughly 8,000 transactions across three projects)<o></o>
<o></o>
<o></o>
<o></o>
What I need to do:<o></o>
<o></o>
Reconcile the MAS90 report with the Committed Cost Report without just trashing the CCR, and not taking up a few weeks of my time. <o></o>
<o></o>
<o></o>
What I would like to do:<o></o>
<o></o>
Search the entire workbook by invoice number and (and/or) $amount and return a true/false/error in the adjacent cell for all 8,000 records.<o></o>
<o></o>
<o></o>
<o></o>
<o></o>
<o></o>
Is this possible? The amounts are generally held in the same columns on every sheet (F, G, and O), and the invoice numbers are generally kept in columns C and L.<o></o>
<o></o>
I could provide both workbooks, if that helps, but I dont know how.<o></o>

 

[PhantomOak]

So, not possible?

 

[tweedle]

It's possible;

I think we need to know more about MAS90
Is it detailed, as in not grouped by Project/invoice so that that information is available on all rows?
What columns hold which critical fields of MAS90?


Are all the CCR sheets in the same directory/folder?
Possibly a sample of A1-A5 posted would be helpfull.

Excel Jeanie can help you post to the forum
http://www.excel-jeanie-html.de/index.php?f=1

 

[PhantomOak]

Mas90 crystal report is an excel file that draws from the Mas90 (accounting) database.

The fields are all the same in MAS90.

ie the amount of the transaction will always be in column "J"



So far, today, I spent 4 hours taking about 2000 transactions out of the CCR via copy/paste just to have them in a usable form...

Here is an example of a CCR page and the issue:
The information is not kept in any reasonable format. ie some amounts are stored in merged columns "G:H" and others are kept in F.
Moreover, because we have hundreds of PG&E bills, among others, somehow, rows started to get inserted randomly. meaning the first "box" might be 10 rows long or 300.

There are roughly 150 sheets per workbook, with about 36,000 total rows. Many of the sheets are empty...




It is a perfect example of what happens when an organization tries to use a previously created workbook by adding things as they go... all I want to do is try and pull the data out so I can start fresh (but without going through 3 years of transactions)



-- removed inline image ---