Jump to content


Indirect Function/Data Table

Entering more columns?

  • Please log in to reply
1 reply to this topic

#1 JPK1066


    Advanced Member

  • Members
  • PipPipPip
  • 99 posts
  • LocationHilton Head SC

Posted 02 November 2017 - 02:52 PM

I will enter a small WS with data on 2 NFL teams TAM or Tampa Bay and NOR or New Orleans.  (columns A and Z)

There is a small data table underneath that begins on row 16.  I would like to know if I were to insert the Indirect Function in front of my formulas in the data table if it would then allow me to add and subtract columns to the data above and maintain the integrity of the data table below.


I  was somewhat confused by the use of the Indirect Function in the video based on mileage between cities in England and how the Indirect Function was the resolution to that table recognizing the correct cities. I'm thinking that the Indirect Function will allow me to move cells and still have Excel be able to direct the formulas to the correct cells.


Will this be the case or am I missing the use of the Indirect Function?


My second question is will the movement of the data table. or any function for that matter, cause Excel to change the range that the formulas are acting on. I've found that the row number can inexplicably change when I move the data tables around on the WS, usually shrinking the target range say from (A3: A50) to A3:A49) or (A3:A50) to A4:A50)





Attached Files

#2 Jonathan


    Forums Administrator

  • Administrators
  • 880 posts

Posted 02 November 2017 - 07:19 PM



The INDIRECT function is used to translate text into cell references and formulas. For example, if you placed the text "A1" into cell C1, you could get the value in cell A1 by using =INDIRECT(C1). Simply using =C1 would only extract the text and would not treat it as a cell reference.


In Expert Skills Lesson 4-5: Use intersection range names and the INDIRECT function, this is used to refer to range names.


If you need to create formulas that always refer to the entire contents of a table or column, the solution is to use tables and structured table references, as shown in: Expert Skills Lesson 1-17 Name a table and create an automatic structured table reference.


If you just need to refer to a range of cells that isn't going to move or change size or shape, you can use range names. Range names are covered in: Expert Skills Lesson 4-1 Automatically create single-cell range names.


From your description, it sounds like converting your ranges into tables might be the solution you are looking for. You can then refer to the table by its name and create formulas that will continue to work even if the table is moved or rows are added or removed.


As usual, please feel free to reply if you need any more help with this.

Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users