Excel Linking Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am attempting to create a database from an excel spreadsheet. The existing spreadsheet contains some equipment titles as well as some numbers, descriptions, and some calculation fields. The calculation fields utilize if statements, and put the value in its proper field. For instance, for a certain piece of equipment, it is either rated by HP, KW, or KVA. The user puts in the piece of equipment then its rated HP, KW, or KVA value, as well as its load factor, which is a mulitplier for the given value the user has put in. The spreadsheet then takes this value and places it in the proper column farther down the spreadsheet, and multiplies this value by the load factor, the layout is shown below:

Equip. No. HP KVA KW Load Factor HP KVA KW
4545 100 .9 90
4546 40 1 40

The user input the equip no. the first set of HP, KVA, or KW, and the load factor, and the formulas fill in the last 3 columns. Basically, I have attempted to link this to access in order to create a database, but am running into a problem with the linking. I know access is tempermental about formulas, so I linked this entire spreadsheet to one with just values, i.e. Sheet1A1=Sheet2A1, where sheet1 is where the calculations are occuring and sheet2 is simply a dummy sheet with just values and no formulas to make the linking easier with access. The problem I am running into is when I have values in the KVA or KW column. When I have values in these columns, hence no value in the HP column, access shows a #num reference not only for the value of HP, which should be blank, but also in the KVA or KW column that should have a value. I am out of ideas and any help on this problem would be greatly appreciated. Also, if there is a way just to link access to the original spreadsheet instead of the dummy one, without alot of problems, I would greatly appreciate some help on that as well. Thanks in advance.

Brian
 
Hi Brian,

If you're trying to create a database to replace the Excel workbook,
take the opportunity to restructure things. I'd probably just have 4
fields in the table
EquipNo (as now)
NominalPower - Double
Units - Text (HP, kva or kw)
LoadFactor - Double
(NB: life is simpler if you avoid spaces and special characters in field
names)

Then whenever I wanted the actual power or whatever you call it, just
use a query containing a calculated field that multiplies the
NominalPower by the LoadFactor (if you want, you can also have the query
check the Units and convert accordingly to kw or BTU per week or
whatever).

Whether or not you do this, forget about linking, which is often
problematic. Instead, import the Excel data to a temporary table and
then use one or more append queries to move it to the data structure
you've chosen. This way, you can adjust field types and make any other
necessary corrections.




I am attempting to create a database from an excel spreadsheet. The existing spreadsheet contains some equipment titles as well as some numbers, descriptions, and some calculation fields. The calculation fields utilize if statements, and put the value in its proper field. For instance, for a certain piece of equipment, it is either rated by HP, KW, or KVA. The user puts in the piece of equipment then its rated HP, KW, or KVA value, as well as its load factor, which is a mulitplier for the given value the user has put in. The spreadsheet then takes this value and places it in the proper column farther down the spreadsheet, and multiplies this value by the load factor, the layout is shown below:

Equip. No. HP KVA KW Load Factor HP KVA KW
4545 100 .9 90
4546 40 1 40

The user input the equip no. the first set of HP, KVA, or KW, and the
Click to expand...
load factor, and the formulas fill in the last 3 columns. Basically, I
have attempted to link this to access in order to create a database, but
am running into a problem with the linking. I know access is
tempermental about formulas, so I linked this entire spreadsheet to one
with just values, i.e. Sheet1A1=Sheet2A1, where sheet1 is where the
calculations are occuring and sheet2 is simply a dummy sheet with just
values and no formulas to make the linking easier with access. The
problem I am running into is when I have values in the KVA or KW column.
When I have values in these columns, hence no value in the HP column,
access shows a #num reference not only for the value of HP, which should
be blank, but also in the KVA or KW column that should have a value. I
am out of ideas and any help on this problem would be greatly
appreciated. Also, if there is a way just to link access to the original
spreadsheet instead of
 
Back
Top