0

I have now read about 40 tips on this subject and it does not appear anybody has a solution other than "click inside the cell and hit enter".

I have to create many spreadsheets with thousands of rows of excel formulas that excel is not recognizing until I click inside the cell and hit enter.

Here are the relevant details:

  • I am exporting from Toad to create Excel spreadsheet
  • I have Toad settings to Only Use General Formatting and the column in question is general formatted
  • Example formula: =HYPERLINK("THE_RELATIVE_PATH","FILE_NAME.pdf")
  • In excel Calculation Options are set to Automatic (and were at the time of creation)

Formulas just appear as text, even though they are general formatted and formulas. Clicking in Cell and hitting enter will calculate them.

The following things do not work:

  • Highlight either Cell or Column and click Calculate Now under Formulas -> Calculation
  • Changing Excel Options for Formulas to Manual and checking Recalculate Workbook Before Saving, then saving the file.
  • Any of the options from MSDN Here

I'm at a loss for how to accomplish this seemingly simple task. I've even found tips from microsoft that essentially say "If you import and it doesn't calculate, manually calculate by clicking the cell and hitting enter." That is not a solution unless you're importing a dozen rows.

4
  • What are the other options in Toad where you have "only use general formatting"? My guess right off is general is the problem.
    – Tyson
    Oct 24, 2015 at 18:33
  • The options are to check that box or not. There are other options, but not other formatting options. As I read it, this is a problem with importing as text formatting, which is default when using import from Excel. If general is not the appropriate formatting to calculate a hyperlink, what is? And why does general formatting work to calculate a hyperlink?
    – red6
    Oct 24, 2015 at 18:41
  • Are you open to a macro that would turn all the formulas into active hyperlinks?
    – datatoo
    Oct 24, 2015 at 22:11
  • At this point I'm almost certain Toad won't accomplish this with Excel. I actually started using the ORA_EXCEL package in Oracle to export directly from the database to excel file. Hilariously, the first pass resulted in the exact same thing after a couple hours of setting up configuration. That being said, I think if a response had a macro and a way of executing it outside of Excel, it could be a suitable answer to this (and most appreciated).
    – red6
    Oct 24, 2015 at 23:58

3 Answers 3

1

I found a (partial) solution to this problem, but it may not work in your specific situation. The Office documentation for the HYPERLINK function provides a clue.

In my testing, importing a file of plain URLs into an Excel column where another column is already filled with HYPERLINK formulas that refer to the URL column results in the column with the HYPERLINK formulas being calculated and clickable immediately. You can hide the URL column to avoid confusion.

0

I have found the solution.

For the concerned cells, do a find and replace of a part of the content by the same content (for example replace the letter "L" by the letter "L"). The find and replace will:

  • find
  • replace
  • and execute the cell
0

select whole sheet and use coma formatting Alt+H+K

enter image description here

2
  • Can you elaborate on why this solves the problem?
    – fixer1234
    Jan 14, 2017 at 8:26
  • when we export from the software the whole sheet is at general formatting so, i changed the general formatting to the number formatting so, a formula can be used, however u can change the formatting afterwards of which u are not required to be in numbered
    – Aurangzaib
    Jan 14, 2017 at 8:51

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .