# indirect

1. ### Help Needed Combining Index/Match with Sum across Different Sheets!

Hi All, this is my first time posting here. I have done a lot of google searching but i haven't found exactly the problem to my issue. I have multiple sheets which i want to add across. the rows and columns are not in the same order, therefore, a simple sum across sheets wont work. I want to...
2. ### Indirect function on a Table Column name

I have a drop down list in cell A1 with years (2018, 2019, 2020). I want to make a formula dynamic based on cell A1, how do I use INDIRECT function on the column part of a table nomenclature where it says 2019? FiscalYear[2019 Actual]
3. ### Improving my Formula

Hi All ive created this formula and it works perfect, but as your see in a sec its rather big. Can anyone think of a smarter way to get the same result. The below checks to see if there is a date in the BOLDED cell and if there is it moved onto the next till it cant find a date, when it cant...
4. ### INDIRECT formula with Relative Column References

I have a formula using the INDIRECT function to reference the name of the tab indicated in cell A11. This formula works and pulls in the indexed value in column G on that page. What I'd like to do is copy this formula across several columns and have the referenced index column change to H:H...
5. ### Particular Indirect and Match formula

Hi Guys, I need help in order to change the indirect match formula, I have these data to Match. <tbody> #FCE4D6[/URL] "]Values #FCE4D6[/URL] "]Roma Sub 1 #FCE4D6[/URL] , align: right"]85000 #FCE4D6[/URL] "]Pisa Sub 98 - 287 #FCE4D6[/URL] , align: right"]176700 #FCE4D6[/URL] "]Pisa...
6. ### Indirect formula with conditions

Hi Guys, I Need help for a formula, I need an indirect formula, (because the data are stored on another excel worksheet) in order to take it, without blank cells data. Example: the data are something like this: <colgroup><col...
7. ### Indirect within sumproduct

I have the following formula but want to use INDIRECT function on the column name within the table GANNT so becomes easier to update =IF(F\$7<\$C\$2,0,SUMPRODUCT(--(GANTT[WK40_2]=\$B\$11))*E\$8) Have tried the following but get a #REF...
8. ### INDIRECT in Data Validation

I've got three tables TableA: <tbody> List of Tables Other Info TableB Info1 TableC Info2 </tbody> TableB: <tbody> Column A Column B stuff morestuff otherstuff extrastuff </tbody> TableC: <tbody> Column A Column B onestuff redstuff twostuffs bluestuff </tbody> I ultimately...
9. ### Sum with Indirect

Good morning! I would like to modify this formula in a macro so that it always looks at a specific column reference (column K) ActiveCell = "=SUM('Review Tab'!K:K)" I've changed it to this: ActiveCell = "=SUM(INDIRECT(" 'Review Tab'!K:K"))" However, VBA now thinks that everything after...
10. ### INDIRECT function doesnt work

Hi, I have a column B which contains numbers. These numbers correspond to worksheets in the workbook. I need to create a macro so that when activated will open the relevent worksheet in column B. I have hundreds and would take a long time to set a hyperlink to each one when the worksheet is...
11. ### Vlookup Indirect Match function error

Hi all, I am in a major pickle at the moment :eeek: Help! I would like the formula to find the pricing across multiple tabs and the problem is the pricing column. The pricing column is situated on a different column letter and row number on each tab: Please see the link below for the...
12. ### Return cell value based of row Named Range and Column Named Range

Hi All I want to return the cross reference of 2 named ranges. There is a sheet called Front Page where i want to return the result and the information is on a sheet called Commission Row named range = Partner Column named range = Finance Both of the named ranges will be referenced in as...
13. ### Loop & R1C1 Formula: variable reference problem

Hello everyone, I need to fill a tables with certain formulas, and so far I used the following approach: Range("E6").FormulaR1C1 = _ "=FORECAST.LINEAR(RC[-2], OFFSET(INDIRECT(""[Flight_A.xlsx]""&R5C5&""!\$N\$2:\$N\$70""), MATCH(RC[-2],INDIRECT(""[Flight_A.xlsx]""&R5C5&""!\$AL\$2:\$AL\$70"")...
14. ### Reference an outside workbook and cell location

Hello, I saw similar functions with INDIRECT but could not get any to fit my needs (even combining with concatenate) In A1 through A20 I have file names. In A35 I have the file location. So I use concatenate(A35,A1,”.xlsx”) to get the file and location and location in the file (sheet1 C9) I...
15. ### ISNA VLOOKUP INDIRECT combination does not work

Hi all, I'm having trouble with the following formula below: =IF(ISNA(VLOOKUP(\$C\$3,INDIRECT("'"&"*"&\$B4&"*"&"'!\$A:\$A"),1,0)),"NO","YES")) The indirect function does not seem to work with the vlookup or I am doing it wrong. Please see below for the excel file...
16. ### Indirect Function Alternative

I started using the INDIRECT function to create a super dynamic Sumif that references a named range based on criteria. However, I noticed that my calculation time has increased significantly since taking this approach. I read online that this is a very volatile function that performs slowly...
17. ### INDIRECT in COUNTIFS and SUMPRODUCT returing error, despite working when they are the only arguments

I have the name of the start of a range in Q1, name of the end of the range in Q2 (for example: Q1 = "Data!\$Y\$2", Q2 = "Data!\$Y\$2000"). I have a criteria in F2 (for example: "Canada"). I have some existing COUNTIFS and SUMPRODUCTS set up like below (using named ranges/cells)...
18. ### Using Indirect to reference another WORKBOOK

Hello All, I have a large accounting file with parallel construction which I need to pull into each of my assets Profit and Loss Statments. Each asset has a separate PnL page, and in my accounting worksheet, I have each asset named seperately. I'd like to create a formula where I type in the...
19. ### Formula Request: Sum across different sheet tabs with dynamic range, without INDIRECT

Hello, I have a fund with 46 assets. All of the asset pages are built exactly the same. To roll them up, i use a rollup page, with each line of the rollup exactly the same as each asset tab To roll them up I sum Sum(BEG:END!E257) for December and Sum(BEG:END!F257) for January, and so on so...
20. ### Text Reference in Cell

Is it possible to reference a cell for it's text? The problem I am trying to fix is the following: =SUMPRODUCT(COUNTIF(January!E36:E36,{"Completely Satisfied","Very Satisfied"})) I am not in the January sheet. However, the cell E36 in the sheet I am in contains "AI", which is actually the cell...