The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

    20140508-Delphi-2007--Project-Options--Cannot-Edit-Application-Title-HelpFile-Icon-Theming20140430-Fiddler-Filter-Actions-Button-Run-Filterset-now20140424-VMware-Fusion-6.0.3.-no-reclaimable
    More Photos

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,869 other followers

Archive for the ‘Office’ Category

« Previous Entries

Display the relationships between formulas and cells – Excel

Posted by jpluimers on 2020/03/18

The “Formulas” tab has to buttons that help to Display the relationships between formulas and cells – Excel [WayBack]:

  • Precedent cells —   cells that are referred to by a formula in another cell. For example, if cell D10 contains the formula =B5, then cell B5 is a precedent to cell D10.
  • Dependent cells —    these cells contain formulas that refer to other cells. For example, if cell D10 contains the formula =B5, cell D10 is a dependent of cell B5.

To assist you in checking your formulas, you can use the Trace Precedents and Trace Dependentscommands to graphically display and trace the relationships between these cells and formulas with tracer arrows, as shown in this figure.

Worksheet with tracer arrows

Related:

–jeroen

Posted in Development, Excel, Office, Power User, Software Development | Leave a Comment »

worksheet function – How to add or subtract to, or increment, column letters in Excel? – Super User

Posted by jpluimers on 2020/03/13

[WayBack] worksheet function – How to add or subtract to, or increment, column letters in Excel? – Super User:

Here’s the best I’ve found so far: =SUBSTITUTE(ADDRESS(1,( COLUMN() + 1 ),4),1,"")The part in the middle marked in bold is the only part that changes. In this example, it’s taking the current column and adding 1, so returning B if it’s in column A and AA if it’s in column Z.

It is related to the question and answer [WayBack] Formula to return just the Column Letter in excel – Super User:

FYI on your original formula you don’t actually need to call the CELL formula to get row and column you can use:

=ADDRESS(ROW(),COLUMN())

Then as an extension of that you can use MID & SEARCH to find the $ and trim down the output so you are just left with the letter:

=MID(ADDRESS(ROW(),COLUMN()),SEARCH("$",ADDRESS(ROW(),COLUMN()))+1,SEARCH("$",ADDRESS(ROW(),COLUMN()),SEARCH("$",ADDRESS(ROW(),COLUMN()))+1)-2)

edit You can even simplify this further:

=MID(ADDRESS(ROW(),COLUMN()),2,SEARCH("$",ADDRESS(ROW(),COLUMN()),2)-2)

And it is part of a much more elaborate answer

Read the rest of this entry »

Posted in Development, Excel, Office, Power User, Software Development | Leave a Comment »

Error “30029-1001” – Google Search; when installing Office 365 language packs

Posted by jpluimers on 2020/01/31

On my list of things to figure out: Error “30029-1001” – Google Search.

This happens when installing language packs for Office 365.

Related:

–jeroen

Posted in Office, Power User | Leave a Comment »

Removing “Workbook Contains Macros” Prompt – Free Excel\VBA Help Forum

Posted by jpluimers on 2019/11/22

This indeed was an Excel 2011 for Mac thing.

Even without macros or VBA modules, Excel 2011 for Mac shows this dialog when opening a .xls file.

The solution was simple: save as .xlsx.

–jeroen

via [WayBack] Removing “Workbook Contains Macros” Prompt – Free Excel\VBA Help Forum

Posted in Excel, Office, Office 2011 for Mac, Power User | Leave a Comment »

Return empty cell from formula in Excel – Stack Overflow

Posted by jpluimers on 2019/11/19

I never thought you could do it, but you can: [Archive.is] Return empty cell from formula in Excel – Stack Overflow.

You have to crate:

  • a VBA function
  • a reference to a range that evaluates the function so it returns blank
  • a cell with a function that blanks the cell using the range

Convoluted, but clever!

–jeroen

 

Posted in Development, Excel, Office, Office VBA, Power User, Software Development | Leave a Comment »

« Previous Entries
 
%d bloggers like this: