Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • Searching table in 2 dimensions. Formulas - combined functions
  • Sort list dynamically (functions) COLUMN
  • New function IFERROR, finally IFERROR
  • Get 3rd Wednesday of month Basic Math
  • Count Unique COUNTIF
  • UDF – Convert Number into text (English and Arabic) User-Defined f(x)s = UDF
  • Hello world! Uncategorized
  • Dynamic selection list ActiveX controls

Category: INDIRECT

Search across sheets in function

Posted on July 22, 2022 By ANmar
Search across sheets in function

Just when you need to search for a thing across sheets in a workbook.

I am bored, so I am adding articles from my old archive.

Read More “Search across sheets in function” »

ADDRESS, COUNTIF, Formulas - combined functions, INDIRECT, Lookup and References, OFFSET, Standard functions, Statistical

Links between two workbooks – dynamically using functions

Posted on September 26, 2016 By ANmar
Links between two workbooks – dynamically using functions

I don’t believe I don’t have this here yet. This is one of my oldest tricks

When we have to do links between two workbooks (One cell in Workbook1 to have value updated from a cell in Workbook2), we usually just do links like this…

Go to workbook1, select a cell, type in ‘=’ then go to Workbook2 and select a cell, then ‘ENTER’

To get something like this

link1

Which will look like this if try1.xlsx is closed

link2

However, if you open try2.xlsx alone before you open try1.xlsx, you get this message

Read More “Links between two workbooks – dynamically using functions” »

INDIRECT, Lookup and References, Standard functions

Why I ran from =INDIRECT() function

Posted on April 6, 2015 By ANmar No Comments on Why I ran from =INDIRECT() function
Why I ran from =INDIRECT() function

I think if there is a single function that would be a huge threat to VBA and macros from user perspective, it would be INDIRECT()

Yes, it wasn’t long enough before I got introduced to OFFSET() back in 1998, and was using it a lot and in my classes, yet once I found INDIRECT, things changed

Along with ADDRESS, ROW and COLUMN and some text tricks like SEARCH, LEFT, LEN, RIGHT, etc, you can do a lot.

You see INDIRECT does a simple job

Converts a text that represent a reference, into reference

So…

If you do

=INDIRECT("G4")

It gives you the value in G4

Notice that “G4” has the double-quotations, means

=INDIRECT("G"&SUM(S1:S4))

Read More “Why I ran from =INDIRECT() function” »

Formulas - combined functions, INDIRECT, Lookup and References, OFFSET, Standard functions

iframe in Excel (XLiFrame)

Posted on February 19, 2012 By ANmar No Comments on iframe in Excel (XLiFrame)
iframe in Excel (XLiFrame)

This is the iframe in Excel, if you are familiar with the HTML concept of iframe, you will understand this one right away, it is basically the same project as HoScrollArea but with vertical scroll too.

Formulas used to bring actual data is:

=IF(OFFSET(INDIRECT("'"&$F$2&"'!$A$1"),$D7-1,$D$6-6+COLUMN())="","",OFFSET(INDIRECT("'"&$F$2&"'!$A$1"),$D7-1,$D$6-6+COLUMN()))

While formula used to bring header labels:

Read More “iframe in Excel (XLiFrame)” »

ADDRESS, COLUMN, Formulas - combined functions, IF, INDIRECT, LEFT, Lookup and References, OFFSET, Standard functions, Texts and Strings

Rotate table using 1 formula

Posted on February 6, 2012 By ANmar No Comments on Rotate table using 1 formula
Rotate table using 1 formula

Rotating table (Transpose) using functions so that the table is updated once the source table is updated. Also can be used as automatically rotating tables with variable number of rows and columns Sheet name can be used inside formula so that you can have multiple tables from different sheets to be rotate it into one…

Read More “Rotate table using 1 formula” »

ADDRESS, COLUMN, Formulas - combined functions, INDIRECT, Lookup and References, ROW, Standard functions

Insert Blank rows into table using functions

Posted on February 5, 2012 By ANmar No Comments on Insert Blank rows into table using functions
Insert Blank rows into table using functions

Here you will see how to work with functions to insert a blank row every certain number of rows in a table using a blank sheet to copy all values of that table into the new sheet Then using Copy > Paste Special to make them constants The new thing is that this is all…

Read More “Insert Blank rows into table using functions” »

CHAR, COLUMN, Formulas - combined functions, IF, INDIRECT, INT, Lookup and References, ROW, Standard functions

Recent Posts

  • Stale values (Scratched functions)
  • Middle name + 1st name
  • LastSunday, LastSaturday
  • Paste Special Percentage
  • Jan8 date format

Archives

Categories

  • Array formula (1)
  • Formulas – combined functions (58)
  • Google Sheets (2)
  • Non-functions (36)
    • ActiveX controls (2)
    • Conditional Formatting (7)
    • Data Validation (7)
    • Excel User tips (19)
    • Format Cells (6)
    • Graphics (4)
    • Names (3)
  • Standard functions (82)
    • Basic Math (18)
    • Date and Time (16)
      • DATE (9)
      • Hour (1)
      • MONTH (7)
      • NETWORKDAYS (2)
      • TODAY (7)
      • WEEKDAY (8)
      • WEEKNUM (1)
      • YEAR (8)
    • Engineering (1)
      • CONVERT (1)
    • Information (6)
      • CELL (4)
      • ISERROR (1)
      • N (1)
    • Logical (30)
      • AND (5)
      • IF (24)
      • IFERROR (6)
      • IFNA (1)
      • IFS (1)
      • ISERROR (1)
      • OR (2)
    • Lookup and References (36)
      • ADDRESS (4)
      • CHOOSE (3)
      • COLUMN (7)
      • FORMULATEXT (1)
      • HLOOKUP (1)
      • HYPERLINK (2)
      • INDEX (5)
      • INDIRECT (6)
      • MATCH (12)
      • OFFSET (15)
      • ROW (11)
      • VLOOKUP (5)
    • Math and Trig (24)
      • ABS (1)
      • CEILING (1)
      • COUNT (1)
      • COUNTA (4)
      • INT (9)
      • LARGE (2)
      • MAX (1)
      • Min (2)
      • SMALL (3)
      • SUM (3)
      • SUMIF (1)
      • SUMIFS (2)
      • SUMPRODUCT (1)
    • Statistical (5)
      • COUNTIF (4)
      • COUNTIFS (1)
    • Texts and Strings (24)
      • CHAR (5)
      • CONCATINATE (1)
      • FIND (1)
      • LEFT (8)
      • Len (4)
      • MID (9)
      • REPT (3)
      • RIGHT (2)
      • SEARCH (9)
      • STRING (1)
      • SUBSTITUTE (2)
      • TEXT (4)
      • TRIM (2)
      • VALUE (2)
  • Uncategorized (2)
  • User-Defined f(x)s = UDF (3)
    • HyperlinkOf (1)
  • Worksheet (16)
  • XLfxs (2)

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
  • CTRL+Y a mini macro Excel User tips
  • Change Calculator – calculate change in multiple bills/coins Formulas - combined functions
  • Shapes with dynamic output Excel User tips
  • Excel sessions – 2013 VS 2010 Excel User tips
  • Blank (isempty) VS “” (null string) Excel User tips
  • Search across sheets in function ADDRESS
  • 6174 Kaprekar’s constant Basic Math
  • Shades of gray Graphics

Copyright © 2025 Excel Functions.

Powered by PressBook News Dark theme