indirect

Hello, I am struggling with the syntax to combine a COUNTIF function with an INDIRECT function embedded within (or something similiar). Here's my current formula in cell B1: =IF(A1 = "all",COUNTIFS(Sheet1!Q:Q , Sheet2!M4)) (If A1 in the current sheet = "all", then run a countif to see how many...

Hey everyone, Looking for some help again. I have a formula..... =IFERROR(IF($G$8="Primary",VLOOKUP(I$8,INDIRECT("PrimaryDailyRange"),2,0),IF($G$8="Secondary",VLOOKUP(I$8,INDIRECT("SecondaryDailyRange"),2,0))),0), that works exactly how I need it to in one worksheet. The next worksheet is...

Apologies if this looks/sounds messy... In the middle of my (probably unnecessarily complex/large) formula, I have this function: (OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),-ROW()+2,0)) As it stands, this will pull the value of the cell 2 rows from the top, in the same column as the current...

I'm using Excel 365 with Windows 10. I want to create links inside my excel file and am having a lot of trouble combining different formulas inside one cell to make a link. I have one workbook named "Trade Log" with two worksheets named "Trade Log" and "Screenshots." In the Trade Log worksheet...

I have a workbook with an index, that returns all of my tab names in column B (=INDEX(listsheets,A6). This is then used to search specific data (row 3) within each tab using the formula =VLOOKUP($E$3,INDIRECT("'"&B6&"'!"&"b3:F500"),5,0). The data on row 3 of my index tab, is searched for in...

Hi A column contains a header which is a named Cell. I want to return number of Items in the entire worksheet column (the column containing the named Cell). I'm using the count to create dynamic ranges, it's all automated and needs to be reliable... CountA is not returning correct answers...

Hi The below formula return #VALUE and i'm not sure why =SUMIFS(INDIRECT("'"&$B$1&"'!$b4:$n17"),INDIRECT("'"&$B$1&"'!$a4:$A17"),$A7,INDIRECT("'"&$B$1&"'!$b2:$n2"),">="&$B2,INDIRECT("'"&$B$1&"'!$b2:$n2"),"<="&$B3) I have tried the below alternative and get #REF...

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

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]

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

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

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

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

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

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

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

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

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

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

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