Good. I don't use Excel 2007 either. Yuck
. I'm not keen on it.
OK, here's the plan.
Fire up the Project Export Wizard. Export your table to a new Access mdb. Setup the export to "Add data to file" when the output file exists. For the Table options, select the "Overwrite existing table" option. Name the table Balances. Run the export.
Now start Access and open your new database, which has but a single table.
Go to the list of Queries and create a new query using the wizard. Select your key column and the dsinibal field, ignoring all others. You can just select Finish now to close the wizard. Now double click the new query to open it.
Here's where we go off the beaten path a little.
With your query results displayed, go to the View menu and select SQL view.
You'll see something like this:
Code:
SELECT Balances.Acct_Num, Balances.dsinibal
FROM Balances;
Edit this ever so slightly, making it read:
Code:
SELECT Balances.Acct_Num, Balances.dsinibal, Log(1/Balances.dsinibal)/Log(10) AS LogValue
FROM Balances;
Save the changes with Ctrl-S or by clicking the disk icon, and close the query window. You can now run the query to see what it does. So far so good?
Now close Access. Your new query is now part of the database.
Switch back to Monarch, and build a new external lookup in the Table.
Name it Logs, and use the mdb file as the source. Now in the list of datasets, you have not just your original exported table, but your new query as well. Select the query, and proceed with the wizard as you would normally, importing LogValue based on your key.
Save the model and the project files.
Now your process will be to first run the export, then refresh your external lookups, and you should have new log values every time.
If this works as intended, and it should, it's effectively automation without programming.
What do you think? Does it suit your needs?