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.

  • 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 '15 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 '15 at 18:41
  • Are you open to a macro that would turn all the formulas into active hyperlinks? – datatoo Oct 24 '15 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 '15 at 23:58
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

  • Can you elaborate on why this solves the problem? – fixer1234 Jan 14 '17 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 '17 at 8:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.