indirect

The formula I'm having trouble with on sheet1 It does the following... 1. Pulls the year from cell A1 2. Row 6 (column headers) has each month listed representing each month (M6:X6) 3. Column I represents sheet names. In the example below, the sheet name in I7 is "sheet2" Sheet2 is an...

Hi all. I am new here, so apologies if I miss any conventions with making this post. I'm using Office 365, on Windows. Here is a link to a screen-recording of the spreadsheet/dashboard. (Unfortunately the screen-recording does not capture the drop-down list, for some reason, but you can clearly...

Cant get my sumproduct indirect to work =SUMPRODUCT((INDIRECT($A$6&'[Metering Services 3 Productivity 2020 NEW.xlsx]"!$R$2:$R$100")),--MONTH((INDIRECT($A$6&'[Metering Services 3 Productivity 2020 NEW.xlsx]"!$B$2:$B$100")))=$AC$3) A6 to Name of tab B2:B100 is dates R2:R100 is the values for...

Hello! I have learned a new formula today =SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetNames&"'!"&"w2:w500"),F2)) Formula source: How To Count Items Across Multiple Worksheets In Excel I want the formula to only count the criteria (F2) once in each cell. I saw this but I don't know how to combine...

Hello, I've been trying to insert a Sheet reference inside CELL() formula because I want to reffer to another Sheet, not in current Sheet, where I write given formula. Cell reference that is bold AH$1 and AH$19000 should refer to Sheet named "AAA". Right now I am getting #REF in every case I've...

Hi all Need to check if both matches are on the same row =row(A6)=row(B6) which works, but i need it nested within my other formula =IF(AND(COUNTIF(INDIRECT("'["&$F$1&"]"&$E$1&"'!$A:$M"),A6),COUNTIF(INDIRECT("'["&$F$1&"]"&$E$1&"'!$A:$M"),B6)),"Match","No Match")+ Cant work it out, could...

Hi All im getting #Ref for this formula, but i cant see the issue with it =IF(COUNTIF(INDIRECT("'["&$F$1&"]"&$E$1&"'!"),A2),"Match","No Match") F1 is the workbook name E1 is the tab name When im evaluating it, its saying "'["&$F$1&"]"&$E$1&"'!" is the issue Can someone spot where ive gone...

Hi All bit of a complicated one to explain, so bare with me. There are two workbook lets call them Book1 and Book2, i need to find if there are match's(multiple criteria) Each Book has a tab called Tab1, Book1 has more columns on then Book2, but the columns never change place. On Book2 i need...

Hi, I have a workbook for each month of the year (9 to date!). Each workbook has a tab for 17 European countries, named in their 2 digit ISO code, e.g. DE for Germany, FR for France and ES for Spain etc. plus a Europe tab which totals up the individual tabs. In each country tab, there is a...

Hi! I have been struggling for 5 days trying to get this formula to work. Basically, I need it to search across a few worksheets which I have named "JON" and sum a corresponding cell if it meets two conditions. The formula returns a "Value" error...

Hi, I have a very long formula using nested IF formulas. It is basically checking different columns showing management chain levels and returning a value if a certain manager is in it, and if not, continuing down the IF chain. There are a few pieces that are repeated many times that I would like...

Hi, I have looked for a solution to this and am unable to find one. I have a series of tab with a number code and month such as "12345 - April", and for subsequent months it will be "12345 - May" with the month changing each time for each of the number code tabs. I am referencing to the tabs...

Hello my dear friends, 1st post here.. I have a spreadsheet with several sheets where sheet name is the person's name (1st and last name). I need create a new sheet with consolidated data, where column A already have the person's name, and I need the MAX cell value from columns A-C at each...

I have 12 sheets that needs to be summarises based on months and withdrawals and item codes. so i created a range name and named it Donors I then proceeded to use this formulae. however, the answer ends up being #ref! I8:I200 has the withdrawals, D8:D200 has the months, Table13[[#Headers],[Jan]]...

Hi, I have a list of 20 names in column B. Everyday I need to select 6-8 of these names and have them appear in col J without skipping cells, without duplicates and without using a formula in J. If cells get skipped it will break the formula on another sheet that is the purpose of the workbook...

Good Day, I am trying to solve this issue, but I didn't find any mistake in the formula. Could someone please help me check or correct the formula? Thank you You can check the file in this link: TEST FORMULA.xlsx ???

Hello to all So Im trying to accomplish 2 things, I have a table that was poorly formatted were I need to get information 50+ times a day I was able to get the information but there are comments on SOME cells, This is what I got so far 1st DropDown ( Main Category )...

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