indirect

Good Evening, I'm trying to drag/copy-paste an indirect formula where the row value in the reference changes. Then continue the pattern over hundreds of cells. This is the general idea: =INDIRECT("Sheet1!A3") =INDIRECT("Sheet1!C3") =INDIRECT("Sheet1!E3") =INDIRECT("Sheet1!H3")...

Hi, I have a collection of cashflows which I am looking to consolidate onto a master sheet (all within the same workbook). Each individual tab has the same account code and date references. I have a name range for the individual tabs but I cannot seem to find the correct syntax to pull the...

Hello, Workbook name: Report Sheet with pivot: Summary Sheets with data source: Source1 , Source2 (data in range A1:C14) In the above workbook, I defined the name PivotChange_Source with formula =INDIRECT(Summary!$A$1&"!A1:C14") to change the Pivot source within the Workbook based on A1 input...

I have an example table in worksheet (just as an example to demonstrate my query). The table is called Table1 and has 2 columns Col1 & Col2, Col1 containing different integer values. In a cell outside of the table (E2) I have this text: Table1[@[Col1]] Then in Col2 of the table I have this...

I am using the table tblSummary below to sum data from other tables in the workbook. All table names are in a named range called nrSheetParameters. Some of the tables have additional columns between each month so [sum range] criteria needs to be the same column name in each table as the column...

Dear Mr.Excel, I am struggling with OFFSET function when source row is deleted. Here below is basic example: 1) Sheet2.Range("A2:R50") is my table with data 2) Sheet1.Range("A5:R20") is my table with offset data + scrollbar. If I remove any row in table 1), I automatically get #REF in Offset...

ABCDEFGHIJKL1JanFebMarAprMayJunJulAugSepOctNovDec264,40036,40036,70035,00013,0008,50010,20023,60090,7007,50017,5008,500345Oct7,500=XLOOKUP(B5,A1:L1,A2:L2)6Feb#VALUE!D5D5=FORMULATEXT(C5)C5C5=XLOOKUP(B5,A1:L1,A2:L2)C6C6=XLOOKUP(B6,Months_RA,INDIRECT(B6))Apr='Example 2'!$D$2C5Aug='Example...

I am using Excel 2016. I would like to do a lookup function that also involved the indirect function. I have multiple worksheets which belong to a coach and are titled accordingly - 'Peter' 'Sally' 'Chris' etc. On my active worksheet I have a table that looks like below: (assume the below...

Have a different tab for each security setup. Trying to create a sumif formula with the criteria range pulling a tab name from a cell for that specific tab. So for instance have the tab name Acala in cell E38, want the sumif to pull critieria from Acala!C:C, is there a way to make the formula...

Hi Excel Guru Wizards' brains' trust. Can you spare a moment to help me with a formula. or suggestion an alt soln. Please. Ok so here is the background. In a folder, a new CSV file with the date gets added each day at the start of the day. e.g. The folder has heaps of csv files...

Hello, I have a working filter that looks like this: =filter('2022'!A:E,('2022'!D:D=B1),('2022'!C:C=C1)) I tried converting to also use indirect so that I could make a filter that I could change with a dropdown with matching years for tab names...

Dear best regards I am trying to get the values that are located in column B, to be reproduced in column C in the same order as they appear. The problem is that I have entered some formulas that do not allow me to obtain the values I require, and instead they always reproduce the same one...

I have a data set with unique IDs in column B and dates in row 2. I am trying to make a formula that looks at a date I've selected, finds that column in the data sheet, looks at at a range that includes the found column and the 11 before it (i.e., a whole year), then counts the number of rows...

Hello Can someone please help re-write the formula. Not sure what I'm doing wrong, I get #REF error. =SUMPRODUCT(INDIRECT("'"&C$1&"'!$c$5:$au$54")*(INDIRECT("'"&C$1&"'!$a$5:$a$47"=E2)*(INDIRECT("'"&C$1&"'!$c$1:$au$1"=E2)*(INDIRECT("'"&C$1&"'!$c$3:$au$3"="total")))) C$1 = Worksheet reference...

Hello, I am using the following formula and trying to drag horizontally without just copying the reference to the current cell. I am not very familiar with the indirect formula but this could help save me hours worth of manual linking.. =INDIRECT("'"&B2&"'!c37") B2 is the tab name and C37 is...

referring to a cell by OFFSET(INDIRECT(ADDRESS(ROW();COLUMN()));0;-4) is easy Although I want to replace "$H:$H" in a formula by an OFFSET function referring to the entire column 4 columns to the current cell (column) How can that be done? example: =TEXTJOIN(", ";TRUE;IF($H:$H = E5;$I:$I;""))...

Forgive me if this has been answered somewhere; I've looked and can't seem to find a solution on the board. I'm using dependent dropdowns in Sheet 1 [Main] via an indirect() in Data Validation from named ranges in Sheet 2 [DVS]. So, you choose the "Parent_Choice1" in Cell A...and then... Cell...

Hi all, Hoping someone can help me with this formula: {=SUMPRODUCT(INDIRECT("'"&"2010_Sold.xlsx"&"'!"&{"January","February","March"}&"["&"Current Price"&"]"))} Which is returning 0 Or, =SUMPRODUCT(SUM(INDIRECT("'"&"2010_Sold.xlsx"&"'!"&{"January","February","March"}&"["&"Current Price"&"]")))...

i have no clue to doing this man, i expect the output is to be the right chart, but on the left chart is what i’ve done i want the cell to still remain the same number even the row is deleted and if i add new no. it still going +1 i need your help guys

Just started using Excel Formulas and I'm using this to get sums across 3 sheets. In the following Section, B3 and L3 are not updating to B4 and L4 when dragging down a row Any help would be greatly appreciated.