Easy explanation of Excel’s INDIRECT Function – How to use it and when to use it. Also what to watch out for when you’re using it.

INDIRECT can be very useful in Excel Dashboards when you need a dynamic cell reference instead of hard-coding the reference in a formula. For example, let’s say you have a drop down where the user can select for which year the revenue should be shown. Depending on the selection the formula with a SUMIF or SUMIFS function should sum up different ranges of data. Instead of writing a long formula with different conditions for each year that could be chosen, you can use INDIRECT.

Get the full Excel Dashboard course here:

Indirect can be a confusing function. It takes a little bit time to get the hang of it. What Indirect does, is it returns an address. So for example, if you type in =indirect(A1) and inside A1, you have written A10 – then your formula returns what is inside A10. Why would you need this? Watch the full video and download the workbook to practice along.

Download the workbook here:

★ My Online Excel Courses ►

✉ Subscribe & get my TOP 10 Excel formulas e-book for free

Get Office 365:
Microsoft Surface:

GEAR
Screen recorder:
Main Camera:
Backup Camera:
Main Lens:
Zoom Lens:
Audio Recorder:
Microphone:
Lights:

More resources on my Amazon page:

Let’s connect on social:
Instagram:
Twitter:
LinkedIn:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#MsExcel

Nguồn: https://phanrangninhthuan.info/

Xem thêm bài viết khác: https://phanrangninhthuan.info/tong-hop/

50 Comments

TreeHugger Joe

June 29, 2020

Can you make a little more confusing.

Reply

PRASANNA PRASANNA

June 29, 2020

Thanks. Your way of teaching solved every bit of my confusions regarding INDIRECT function

Reply

AMAN JAJOO

June 29, 2020

Thank you very much

Reply

CA.LOKENDER Chauhan

June 29, 2020

Pls make another video on indirect function with more examples. It is difficult formula.

Reply

Burak K.

June 29, 2020

I just did not understand the note at @11:03

Reply

maddy maddy

June 29, 2020

Hi Leila , Thank you for providing Knowledge transfer to worldwide . Would you please share the tutorial of Index and Match function using VBA from one sheet 1 to sheet 2 .

Reply

bikash ram

June 29, 2020

Hi I need a help with one formula to solve my ranking problem following various conditions. Ranking basis country wise.country 1 gets inportance over country 2. Rank basis Delivery charge offered by country 1 supplier than country 2 supplier. It there is tie between supplier than ranking basis lowest delay charge cost. If there is tie at delay charge than rank basis no of years in business.

Reply

mohit pal singh

June 29, 2020

Been following your videos for a very long time now n they are amazing and very informative, can you please make one video on how to use consolidate function to collate data in one sheet.

Reply

Chris Kirk

June 29, 2020

Bit of a newbie, is this the only way to pull from other sheet tabs with a helper cell? You mentioned Choose at 11:26, can this be used? Do you have an overview of that function?
Am I just looking too deep, am I missing the basics here, is there an easier solution for my formula (=VLOOKUP((A1,'Sheet2'!$A:$G,5,FALSE)) can I not have Sheet2 in a helper cell on my main sheet and have excel use it to know where to look without other bedded functions?

Reply

AMIT KUMAR SHARMA

June 29, 2020

VERY NICE MAM
THANKS
OM SAI RAM

Reply

Mohammed Aldighaithir

June 29, 2020

Hello Leila,
Could you help me regarding this issue? I tried using the indirect formula for different workbooks. I have multiple file with only the month name different. Like jan report. Feb report. The indirect formula referencing to the text name of the file works. But i need the file to be open. Can you work around that and manage to fetch the data when the file is closed?

Reply

ABDULLAH SIDDIQI

June 29, 2020

When i was using indirect function after vlookup it was giving error

Reply

Krilunius

June 29, 2020

por fin me sirvió esta fórmula!!!!Thank u

Reply

Chani Shah

June 29, 2020

Hello,
Can I make a drop down list to take data from another excel file using indirect function?

Reply

Tolga Abbasoglu

June 29, 2020

Dear Leila, thank you for your great tutorial, I have a question. Is there any way to use date in indirect function as date instead of number? Whenever I use indirect formula combine with date, it indicates the date as number like 43922 but my sheet name is 01.04.2020 and I can not create a correlation. What I mean is when I wrote a formula in a cell =INDIRECT("A94") I get a result as 01.04.2020 Thats ok but when I wrote the formula as ="'"&INDIRECT("A94")&"'" I get the result as '43922' instead of '01.03.2020'. Is there any way to get the result as '01.04.2020'. Thank you in advance.

Reply

Shakira Asfoor

June 29, 2020

احسنتي

Reply

Greg Bernard

June 29, 2020

That is super slick.

Reply

Satish Sharma

June 29, 2020

Explained in very detail, impressive… just wondering if you have to rate yourself on scale of 1 to 10 on excel skills where do u keep yourself …maybe 15? or 20? 🙂

Reply

Giovanni Girelli

June 29, 2020

Does anyone (Leila 😀 ) know if it possible to somehow wrap this with an IFNA() ?

Example: {=VLOOKUP(A1, '\SBS2011RedirectedFoldersSOME_NAMEMy DocumentsSOME_FOLDERPO_SHEETS[FILE_LOOKUP.xlsx]Sheet1'!$A$1:$D$500, {2,3,4}, FALSE)}

Thanks

Reply

Sanjay Barge

June 29, 2020

Integration between excel file to google sheet changes in any one file update any where between google sheet and excel file

Reply

Sanjay Barge

June 29, 2020

Please give me solution. Like sync sheet excel add in

Reply

Kossivi AGLEE

June 29, 2020

You cannot imagine how helpful are your tutorials for me. Thanks so much! May GOD bless you anf give back to you more than you freely give.

Reply

Robert Long

June 29, 2020

Imagine she got even 1% of our earnings for all she teaches the world how to do our job?
I just got a job with a workbook with INDIRECT – and i'm like "WHAT?"

Reply

Dave Goodmanson

June 29, 2020

Thanks Leila! I had no idea about the Indirect function, but after watching your video I have some idea. It messes with my head, which means to me that it’s worth learning. Thanks for this introduction.

Reply

Verky

June 29, 2020

Hello I want the same concept but instead of getting on cell. I want the formula to reference the whole row. How can I achieve this?

Reply

Mark Sibert

June 29, 2020

Hi. I use Mac's Numbers. It doesn't have a name manager. Do you have a work-around for this.

Reply

eduardo sy

June 29, 2020

if you are working on hundred sheets, better not to name the ranges. Just use the original range name (Column:column, or ro:row), as lon as your sheets are of same format

Reply

TharAllah EbnThareh

June 29, 2020

Hi dear Leila, what is the best combination of AVERAGEIFS in case of using multiple rows and columns criteria together??, please help and advice,

Reply

Hoàng Lâm Lê

June 29, 2020

A little thing annoys me with using this function is excel always ask for save when close file even though I didn't change anything after open.

Reply

Rajarshi Basu

June 29, 2020

Suppose I Have about 50 sheets with different names each having unique item number under a particular date within it.
Queries:
1) I want all those names to appear in the summary sheet without having to type those manually one by one.
2) I want the data on those multiple sheets to sync with the summary sheet as well .And I want them to be arranged datewise and itemwise in the summary sheet as well.

Reply

Loida Asar

June 29, 2020

Leila, can you kindly share a video where i can find the last value in a dynamic column and row across multiple sheets?

Reply

Loida Asar

June 29, 2020

Very useful and informative makes my work a lot easier

Reply

L VR

June 29, 2020

This video needs to be updated. It could've been explained better. You shouldn't need the helper table in H15 with Indirect.

Reply

samuel kodjoe

June 29, 2020

I love this!!!

Reply

Rohit Khopkar

June 29, 2020

Hi Leila – I am using INDIRECT to fetch a cell values from another workbook/s. It works fine while the other workbook is open, but turns to #REF when the workbook is closed. Pls suggest a workaround to this to keep the values in my workbook updated real time referencing the other workbooks

Reply

Anand Gujarani

June 29, 2020

It's a fantastic function..I used.it with Name Manager
…linked around 700 sheets and made simple dashboard..

Reply

Jagtar Singh

June 29, 2020

superb, 👍👏

Reply

Abdul Aziz Yaqubi

June 29, 2020

Thank you for correcting it, useful topic

Reply

Abdul Aziz Yaqubi

June 29, 2020

Bad video, it is not visible

Reply

Marcel Reijerink

June 29, 2020

Hi Leila
When you use indirect to get a value from another workbook, the workbook has to be open, is there a way that the indirect function gets the value without the other workbooks need to be open, or is there another solution.

Reply

Luciana Ruschel

June 29, 2020

I couldn't do your way, but for me this formula worked =indirect(concatenate(cell where the sheet name is going to be),"[name of column you want to get results]"))
Thanks for your videos! They help me a lot!

Reply

Thillaraj Sagathevan

June 29, 2020

Hi Leila,
I have just became a big fan of your videos. Thanks a lot.
In this example it's pretty simple if we use : =IF($C$14=2016;SUMIF(Data_2016!$B$2:$B$16;Report1!B15;Data_2016!$D$2:$D$16);SUMIF(Data_2017!$B$2:$B$16;Report1!B15;Data_2017!$D$2:$D$16))
why do we have to use indirect ? in other words,can you say a situation where without "Indirect" we can't get easily the result?

Reply

Sudhir Hiwale

June 29, 2020

Thank you very much Leila.

Reply

19761999

June 29, 2020

This is the first time I found one of your tutorials hard to follow and understand. No big deal, I was able grasp the indirect function by looking at other YouTube videos. You're still my favorite Excel teacher by far. Thanks for all you do.

Reply

Cathy B

June 29, 2020

Thank you for this. I was able to get to the correct spreadsheet I need, but I need a second lookup for the column in the resulting sheet.  I've used both vlookup and match, and I do get the correct column.  Is it possible to combine the two forumlae?  So far, I haven't been successful.  But I'm thrilled to get as far as I have.

Reply

Wojtek Ziolkowski

June 29, 2020

Hey Leila, thank you for explaining this function on name of the range basis.

Reply

Elmer Guevara

June 29, 2020

How do you prevent 'negative result ' that comes from a formula? You should only allow a positive number.

Reply

Stephen Phan

June 29, 2020

Good lesson, your voice is very impression. By the way, which tools are you using : screen cast and micro phone would be an advance to your quality. thanks

Reply

Sami Piyash

June 29, 2020

Great

Reply

Jennifer Mitchell

June 29, 2020

I really appreciate the fast pace of your videos and that they are targeted to users with Excel experience. I feel like I'm getting a lot out of the 10 minutes spent on your videos instead of other videos showing baby steps that take loads of time to explain something simple. Loving your channel, thanks!

Reply

Leave a Reply