1. L

    Consolidate data from multiple workbooks, using list of filenames

    Hi Guys I have read a lot of threads on this, but it seems to be one thing which is a mammoth struggle in Excel. The challenge: I need to produce a MASTER Resource Plan, which will consolidate individual team members planning into one overview. The source workbooks are all consistently...
  2. R

    Dynamic Table References for Data Validation Lists

    I've been tinkering for a while, but just can't seem to get this to work as desired. I have multiple tables in a workbook: TblFuel TblVehicles TblPlant_Machinery TblMaterials TblLabour I'm creating a quote page where the initial selection is for one of these item types(Fuel, Vehicle...
  3. A

    Lookup & Indirect to Find Last Non Blank

    Hello, I have data like this: Color Shape One Circle Two Red Three Square Two Three What I'm doing is returning the value in column A in the last non blank row in column B regardless of the value. Currently, in cell B5, I am using...
  4. A

    Find first and last row that includes specific text

    In the column named Example it should show the time different between every status change and when the when status is CLOSE I want to do a time different between status NEW and status CLOSE. Hope you guys have some great answer to this question. :) Best Regards Ahlis
  5. T

    Google Sheets: Referencing Data from 6 Sheets to One master

    Hi, Not super familiar with the platform, but trying my best. This instance is referring to google sheets, but excel and sheets seem to be pretty similar. I am trying to reference data from multiple sheets into a master summary sheet. I started with just referencing the sheets and the cell...
  6. P

    INDEX range and INDIRECT not working inside LET functions

    Hello, I'm calculating Split Multiplier column from Split Ratio column by multiplying the rows from the current row towards the end of the column. It works nicely within normal excel table with PRODUCT($B3:INDEX(B:B; ROWS($B$3#)+ROW($B$3)-1) ) where the formula is copied down, but if I try to...
  7. M

    changing sheet reference to INDIRECT (3 references in formula)

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

    Display image using Name Manager on a dynamic spilled range

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


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

    Can this formula be transformed to only count unique values within each sheet?

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

    Add sheet reference in CELL() content

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

    Check if cells are on the same row

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


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

    Finding match using indirect to find workbook and tab name

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

    Changing sheet references without the use of Indirect

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

    SumIfs Using Indirect based on 2 Criteria

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

    How to reference a formula as text in one cell in a different formula

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

    Selecting Tab with VBA that has a Dynamic Tab Name

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

    New sheet with MAX cell values from other sheets

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


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

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back