problem with linked formula (1 Viewer)

[Fuga]

Hi,

I´ve linked an excel sheet to a table in my db. The sheet contains some statistical functions. However, the value of those functions do not link properly.

in stead I get a #num! in those columns.

How do I link the value of those formulas?

Thanks

Fuga.

 

[Fifty2One]

i have had similar letdowns with xls and havent figured a way around it - probably it would be better to do the calculations in access anyway and not try to pull from cells with formulas.

 

[boblarson]

Instead of linking to the cells with the formulas, put another column in the Excel workbook next to the one with the formula (in fact you could hide the one with the formula) and you can set the reference in the cell to =G4, etc. and then it should work.

 

[Moniker]

If you need to use the function in Access for whatever reason (since recreating statistical formulas can be goofy), take a look here, starting at post #11. I found this when someone else was trying to do exactly what you're trying to do, and it should provide the answer for you.

http://www.access-programmers.co.uk/forums/showthread.php?t=121156&highlight=ATP+2.0

 

[Fuga]

thanks everyone!

I'll have to think about how I will go about it.

But for the record: access can´t import that kind of formulas?

 

[Moniker]

Did you read the post I linked? The reference ATP v2.0 will need to be referenced and set as a new instance, and you can then get to those formulas through that reference's object.

 

[Fuga]

Yes, I read it.

I´m not sure I understood it though.

The thing is I use the stdeva function on the previuos 1000 rows in excel.

My experience is that whenever you calculate something on previous rows(posts) it takes a lot longer in access than in excel.

Do you think I should try the ATP?

 

[Fuga]

Hmmm.....

I can´t find the reference to atp.

 

[Moniker]

You don't have this in your reference list?

 

[Fuga]

No, I don´t.

I have been messing around with different versions of office. perhaps the file is somewhere else.

What is the name of the file?

 

[Fuga]

ok, so now I tried boblarsson´s suggestion, and I still get the same error.

What am I missing?

Fuga

 

[Moniker]

ATP is an add-in pack (the "Analysis Tool Pack" to be specific) and may not be installed. When you install Office or any other large suite of software, the program often given you "Typical" or "Custom" install options, and I don't think the "Typical" one would include ATP. Put your Office CD in the computer, tell it to modify the installation, and then look both in Excel and Access for Add-Ins with that name. My guess is that it never got installed in the first place.

 

[Darrkelf]

I had a simular issue with a direct link between excel and access.

Access does not like importing information from some forumlas.

I found a way around it by creating a new worksheet in the spreadsheet titled IMPORT DATA and then just set up =A2 (or the cell reference of the information in the sheet that I needed) in this new worksheet. After setting up I then formatted all the data into exactly the format that I wanted it to be in in Access.

After doing this, switch to Access and create your direct link to the IMPORT DATA worksheet of your spreadsheet.

 

[Fuga]

Hi again,

First, I solved the problem temporarily by selelcting all, copying and paste in as values. Then excel saves a copy of the workbook. The workbook with the formulas stays intact. Not pretty, but then again this should have worked in the first place.

I like microsoft´s programs, but I think that as soon as you go a little bit advanced, the bugs become more and more frequent. I think this is one of them.

Moniker: I´ll try reinstalling.

Thanks everyone!

Fuga.

 

[Fuga]

I thought I´d share my latest experience.

The formulas I had problems with started quite "late" in the workbook, meaning that the first 1000 rows were empty in the formula-columns.

I tried putting a number in the first row, and it seems that access now links correctly.

This would indicate that the linking function in access maybe defaults to something that "later" destroys the formulas.

Now I´m probably wrong, and I know it. But perhaps someone is helped by it.

Fuga.

 

[boblarson]

Thanks for posting back with your success.