indirect function

  1. J

    Google Sheets - problem with INDIRECT formula with data validation

    Hi guys, I am trying to use INDIRECT with dependant drop downs. I have one worksheet, column R is drop down with two choices (Customer, Courier), Column S should display a drop down of choices varying - depending on the choice made in Column R. So I created dependent drop downs: in another tab...
  2. J

    Excel count if sheet name matches with Column E with cell value

    I am using the formula B1=COUNTIF(INDIRECT(A1&"!E:E"),"<>")-1 But when there is space in between in Column A then I am getting error as #REF!, for example when I have cell value as "REDORANGE" its showing value but when I am using "RED ORANGE" its showing #REF! and my sheet name is "RED...
  3. X

    INDIRECT doesnt work properly

    I dont know if i just dont understand things, but as for my understandings this formular just doesnt work for me in this scenario. I want to refer to an already existing name with INDIRECT(). I want this range to end up as my dropdownlist. The name i want to refer to is called "DropDown1"...
  4. V

    Indirect function with relative movement

    Hello everyone, I'm currently using the indirect function with the IF function to identify if O111 is equal to N112. The problem that I have is if I delete or add any rows the indirect function won't move to follow the last value on the O column. Do you guys have any ideas on how I should...
  5. N

    Dynamic Pictures based one someones name

    Hi all, Wondering if you could help me here. I am trying to create some profiles for some players in which I change the name and their picture changes. I have created a table with their picture next to their name and followed some instructions online to make dynamic pictures however I keep...
  6. H

    References to external workbooks not working unless they are open

    Quick question regarding some references I have to external workbooks. The formula in question is: =VLOOKUP("*"&B5,INDIRECT('Links to Workbooks'!$I$3),'Links to Workbooks'!$C$4,FALSE) Where the lookup value B5 contains "Total Revenue" and is what I am hoping to match using VLOOKUP in other...
  7. J

    Alternatives for INDIRECT in cascading (dependent) dropdown lists causing slowdown and headaches

    Hi, Calling all Excel geniuses, I’m working on a project for a friend, I’m already way beyond my comfort zone in Excel, and am have an issue that I can’t get around. My project involves making a sheet that acts like a form with each row being a series of dropdown menu’s (to limit the users...
  8. R

    Nesting Formulae with INDIRECT Function, Across Dynamic Sheets

    I'm trying to build a formula (in column G) that basically says, "If the worksheet that is named with the value of C2 on this worksheet has "Late" in the 2nd to Last Row, return Late. Otherwise, return the value of the Last Row." My challenges are: The sheets that I'd be searching on are...
  9. B

    Creating dependent list

    Hello all, I have a spreadsheet with a number of companies, each one with 3 specific values that I would like to be able to put in and out of spreadsheets. When I select one of my companies using data validation list I would then like the 3 corresponding values to automatically fill in the...
  10. B

    Multiple Indirect functions in same equation

    So here is what I am trying to do: =IFNA(IF(ISNONTEXT(U7),"",RANK.EQ(W7,INDIRECT(M7&N7):INDIRECT(O7&P7)+COUNTIF($W$7:W7,W7)-1),"") I am trying to establish a ranking on different items down a list so there are as many as 50 items and 20 subsets of each item, I put them all in the same list for...
  11. M

    Getting Data From Last Years Accounts Workbook

    Hello all, I have had some great tips etc. from you Excel Experts and am hoping for more. You recently solved my 'INDIRECT' question which works well but now I am looking to copy 'cars in stock' data from a sheet in last years workbook into a stock book sheet in this years workbook. I would...
  12. V

    Running Formula from a concatenated string.

    This is the string that produces a working formula. =CONCATENATE("=SUMIF(Table_Data[Date],TODAY()-",$B15,",Table_Data[",C$26,"])") C26 - is the header of the table B15 - is the number of days current or before <tbody> The result is a formula that I can paste into notepad, then re-copy into...
  13. K

    Unique Data Validation List with indirect function

    Dear Excel Experts, I have some details like this, Customer . . . Style Name Max . . . . . . FB06 Max . . . . . . 701XT OVS . . . . . . MARK Strd . . . . . . TRO002 I want data validation list of unique customer and in other column i want...
  14. P


    In 2016 trying to substitute Indirect for manually entered sheet name (bold) in following: =IFERROR(INDEX('FYE2015'!$D$4:$D$1322,MATCH('3DRev'!I$6,'FYE2015'!$B$4:$B$1322,0)),0) I have a range of sheet names in column B (FYE2006 to FYE2017)
  15. M


    I have a VLOOKUP formula that I am currently using =VLOOKUP(B1986,'QC T4 BOD'!$A$373:$D$517,4,0) What I am trying to do is avoid having to manually change the $373: $517 part of the formula every time I drag the formula down The start and end refers to the cell address, I would like to use...
  16. F

    Alternatives to INDIRECT Function

    I am trying to find an alternative to INDIRECT. I started modifying a spreadsheet using INDIRECT, but I am worried about the future implications considering INDIRECT is a volatile function. The spreadsheet is not huge, but it is on going. There is a new tab for every month, going back to 2002. I...
  17. J

    Trouble Creating an Autofill from a linked cell every 9 lines

    Hello all I am having difficulty creating an autofill formula. The first tab of my file is called "By Room" and I would like to link exactly every 9th cell down the sheet (starting with B7 and going forward, B16, B25, B34, B43 etc.) to the second tab, called "Ceilings" into every cell without...
  18. O

    Excel INDIRECT with references to changing cell.

    Hi guys, I'm trying to use the INDIRECT function to auto-update a formula based on a pull-down menu from cell B9 for a given table name. This table is a list of TAB-NAMES . key:: B9 [Pull down menu] = staff name A table of "names" on a separate sheet 'Main' from Column A4 - A99 C3 = Year [2016]...
  19. D

    Sum Indirect #REF

    Help! I've been trying for hours to get this formula to work and at a loss. I need to get a sum from a range of numbers in Column F on a tab named "R3A". I know the starting cell is always "R3A!F4" but the end of the range will change each month. I'm calculating the end of the range and it's...
  20. F

    Indirect & substitute

    HI, I have sheet1(called "Timesheet") and sheet2("Legend"). Into Legend I have: <tbody> Category (columnA) Non Game (columnB) Training (columnC) Sport Value1 Training1 Non Game Value2 Training2 Training Value3 Training3 Project Value4 ... Admin ... .. </tbody> Into Manager Name I...
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back