1. J

    Looking to replace Indirect function to pull dynamic ranges

    I've created a dynamic range formula which shows as below: =CONCATENATE("AC SalesRawData!",SUBSTITUTE(ADDRESS(1,C1,4),"1",""),":",SUBSTITUTE(ADDRESS(1,C2,4),"1","")) Which gives me the range output "AC SalesRawData!CQ:DS" I'm then referencing this range in an vlookup using Indirect as shown...
  2. D

    Using 'Indirect' on a result from filter

    Hi, I have used filter to bring back one text name from a range = LET( fv, FILTER(Sheet1!$G$5:$G$10,Sheet1!$H$5:$H$10="Y"),fv) I would like to feed this into 'Indirect' , but it does not seem to work? if I refer to the cell which the above is in this does. so...
  3. W

    Combining Filter function with Indirect/Concatenate to look up from another worksheet

    Hi, I've created a formula that works fine if it is within the sheet where the data originates, if is filtering the routes our vans will be taking and excludes any blank routes (if a van is not scheduled out that day). But I want to have the summary in another sheet dynamically changing based on...
  4. D

    INDIRECT() and named functions

    Hey, I have a couple of named functions (e.g., functions I entered the name manager, gave them names and can not be invoked by entering the names). E.g., two of the named functions are: "exr_average" and "exr_latest" and they return either the latest exchange rate of a selected currency or its...
  5. H

    Avoiding INDIRECT when referencing and comparing ranges in different workbooks

    I work in Finance and have a QoL-issue with excelling daily. I use INDIRECT and SUMIFS constantly when comparing figures. Oftentimes it's about comparing latest estimates with previous forecasts or budgets in an earlier instances of the same file (although with a different name)...
  6. B

    Excel 2021 conditional formatting that changes with the drop down menu

    I have an excel "database" that does various calculations, data collecting and reports for an event that occurs every year. Various reports are generated from specific columns using the indirect function. My question, with the below example, is when I chose 2023, the company names Target and...
  7. T


    Hi, I have a SUMPRODUCT and IF formula which works fine together but I am trying to merge this with an INDIRECT formula: =SUMPRODUCT(IF('No. 1'!$A$1:$A$500=E$4,'No.1'$E$1:$E$500*'No.1'$F$1:$F$500)) No.1 should be the contents of Cell A8, A9 etc. I've tried...
  8. J

    Converting Text String into a Formula using INDIRECT

    Hello. I am trying to point a cell towards another cell in a different file, but make this dynamic using CONCATANATE to build up the file path & then INDIRECT to complete the task. The formula I have is . . . ="=('"&N3&"/["&O3&"]Dashboard'!"&P1&M3&")" This formulas produces the below text...
  9. M

    Formula referencing cell value to read sheet name to populate value.

    I am trying to create a more robust budget and invoice tracking sheet. I track contract values, changes and budgets on worksheet that are named by the financial accounting node they are associated with. I have a column in my project budget sheet that needs to reflect the total value of each...
  10. W

    Dynamic worksheet look up - index match 2-way look up

    Hi there, I have an index match match 2-way look up, which returns data based on another tab. (The index match does a vertical look up and then a horizontal one). The formula works fine, but I would like to introduce a variable into the formula to so it looks up the 'correct' tab, corresponding...
  11. S

    INDIRECT Vertical Auto-Fill

    Hi, I am trying to consolidate data from the same column of multiple spreadsheets. =INDIRECT("'"&A$1&"'!"&"I14") A$1 is the first worksheet name with other worksheet names in the subsequent columns to the right. I14 is the first cell where the data begins in each worksheet. My formula above...
  12. D

    Sumproduct versus sum with indirect

    Hi, I have asked this question on MS website ; Redirecting If I have a an array from indirect ; INDIRECT({"Sheet1","Sheet2","Sheet3"}&"!"&"A1",TRUE) , which returns a spill of "#VALUES" but if F9'd will show ; = {2,3,4} , which is...
  13. k3yn0t3

    How to update Indirect formula so that cell reference is based on a hardcoded cell value?

    Hi all. Quick question: Does anyone know how to update the formula in I22 to make it be fed by the cell reference i hardcoded into A19? I tried replacing the H60 in the Indirect formula with "text(A19))" but that doesn't work. The reason I would like to do this is so that I can copy and...
  14. k3yn0t3


    Hi there. Can anyone help me with an indirect with nested index match formula that's dynamic enough to allow me to get values from other sheets which change depending on the Factory and Metric I need them for? My hope is to have one formula that's dynamic to copy it across and down as the file...
  15. E

    Write formula to compare planned amounts by vendor in one sheet to approved amounts by vendor in another tab based on project ID

    I have an invoice reconciliation workbook where the first sheet (Data) is a list of the Planned Amounts by Vendor and Project ID. All of the other sheets in the workbook are broken out and named by the Project ID. I'm trying to compare the Planned Amounts by Vendor on the Data sheet to the Total...
  16. D

    Excel Indirect function ; refer to call rather than "hard code" reference

    Hi, In the following formula I have type in the "A2:A6"; SUMIF(INDIRECT("'"&Sheets&"'!"&"A2:A6"),"a",INDIRECT("'"&Sheets&"'!"&"B2:B6")) Sheet = named range is there a way to have the reference to A2 to A6 in other cells so a cell A1 = "A1", A2 = "A6" and then just select those two...
  17. G

    Indirect for autofill

    Hi Guys i have the following formula which I need to autofill down to 3800 rows A1 ON PORTAL SHEET has a code which referes to a tab sheet name A2 refer to the cell from that particular sheet I wish to retrieve the data. the cell the first formula is in is X1, there the formula will autofill...
  18. Long Nose

    sheetlist sumifs with multiple criteria - shortened?

    Is there a way I can shorten this formula. Works - This adds "US", "VI", "PR", and excludes "CA"...
  19. D

    INDIRECT with condition or FILTER formula

    Hi all, I'm struggling to make some formula work, unsure if i am not applying the formula correctly. for context: I have a table that goes from C1 to GX. (x is dynamic since rows will be added over time) First row is the headers of the table, so data goes from C2 until GX. Then i have a...
  20. L

    vlookup to other workbooks with variable sheet names

    Hello all, First time posting so please bear with me. I have gone through all the threads that I can find as well as google and YouTube, but to no prevail. I have a vlookup with ISNA formula which is working fine, however the monthly maintenance is quite time consuming and open to human...

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