1. F

    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. L

    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. C

    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. pliskers

    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. R

    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. R

    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. S

    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. U

    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. M

    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. G

    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. C

    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. C

    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. 0

    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. M

    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. C

    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. L

    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. H

    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. W

    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. W

    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. G

    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...

Some videos you may like

This Week's Hot Topics