Serving the Quantitative Finance Community

  • 1
  • 4
  • 5
  • 6
  • 7
  • 8
 
User avatar
tagoma
Posts: 18475
Joined: February 21st, 2010, 12:58 pm

Excel tricks

February 10th, 2014, 3:42 pm

QuoteOriginally posted by: edouardi have fornight data (2 data for each month) to put in a spreadsheet.are there fancy things Excel is able of when it comes to deal with dates such as "1H Jan-14"???OH!
 
User avatar
tagoma
Posts: 18475
Joined: February 21st, 2010, 12:58 pm

Excel tricks

February 10th, 2014, 3:44 pm

Hi all.Is there a built in function or a simple trick to delete the content of all cells (say in a selection) that are NOT formulas?(something less ugly than a macro deleting the content of cells that do no contain the '=' character)
Last edited by tagoma on February 9th, 2014, 11:00 pm, edited 1 time in total.
 
User avatar
bearish
Posts: 6313
Joined: February 3rd, 2011, 2:19 pm

Excel tricks

February 11th, 2014, 2:41 am

The Excel VBA Range object has a property HasFormula, which is either true or false. By looping through each cell in your selection, you should be able to selectively delete the ones that come up false on this test.
 
User avatar
MHill
Posts: 489
Joined: February 26th, 2010, 11:32 pm

Excel tricks

February 11th, 2014, 10:47 am

QuoteOriginally posted by: edouardHi all.Is there a built in function or a simple trick to delete the content of all cells (say in a selection) that are NOT formulas?(something less ugly than a macro deleting the content of cells that do no contain the '=' character)Select your cellsCtrl + G (Go To command)Choose 'Special'Choose 'Formulas''OK'Delete Edit: Doh! re-read the question - choose 'Constants', not 'Formulas'
Last edited by MHill on February 10th, 2014, 11:00 pm, edited 1 time in total.
 
User avatar
bearish
Posts: 6313
Joined: February 3rd, 2011, 2:19 pm

Excel tricks

February 11th, 2014, 11:07 pm

QuoteOriginally posted by: MHillQuoteOriginally posted by: edouardHi all.Is there a built in function or a simple trick to delete the content of all cells (say in a selection) that are NOT formulas?(something less ugly than a macro deleting the content of cells that do no contain the '=' character)Select your cellsCtrl + G (Go To command)Choose 'Special'Choose 'Formulas''OK'Delete Edit: Doh! re-read the question - choose 'Constants', not 'Formulas'Nice! It actually came in handy today in tracking down cells that suffered from conditional formatting (a potential source of many headaches).
 
User avatar
tagoma
Posts: 18475
Joined: February 21st, 2010, 12:58 pm

Re: Excel tricks

February 9th, 2021, 8:13 pm

Ok. So I inherited of an old spreadsheet that was passed from hand to hand over time.
At opening this spreadsheet seems to try connecting to another spreadsheet as it shows "Linking: [filename.xls] ..." at the bottom of Excel. As you guessed, I have no clue what that other spreadsheet is.
I have found different "solutions" to remove this link (inspection of all types of objects in the spreadsheet, find/recplace, ...) on the internet but none has worked for me.
But maybe you guys have some day had the same issue and actually found a solution?
Many thanks.  
 
User avatar
tagoma
Posts: 18475
Joined: February 21st, 2010, 12:58 pm

Re: Excel tricks

February 11th, 2021, 5:15 pm

Ok. So I inherited of an old spreadsheet that was passed from hand to hand over time.
At opening this spreadsheet seems to try connecting to another spreadsheet as it shows "Linking: [filename.xls] ..." at the bottom of Excel. As you guessed, I have no clue what that other spreadsheet is.
I have found different "solutions" to remove this link (inspection of all types of objects in the spreadsheet, find/recplace, ...) on the internet but none has worked for me.
But maybe you guys have some day had the same issue and actually found a solution?
Many thanks.  

Ok, so I finally found a solution. Actually, some Excel objects of type Name had their propery Visible set to False, hence this named Excel objects were not shown in the Name Manager. From there, loop over all Name objects and set them to visible. Voilà!
ABOUT WILMOTT

Wilmott

Wilmott.com has been "Serving the Quantitative Finance Community" since 2001. Continued...


Twitter LinkedIn Instagram

JOBS BOARD

JOBS BOARD

Looking for a quant job, risk, algo trading,...? Browse jobs here...