indirect

  1. D

    Indirect Formula

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

    using OFFSET to refere to entire column (eg. $H:$H)

    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;""))...
  3. TheWaterDog

    Defined Name as Cell Reference

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

    Sumproduct & Indirect returning 0 or incorrect

    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"&"]")))...
  5. G

    reference cell even the row is deleted

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

    Indirect cell reference not updating row when dragging down

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

    Multiple INDIRECT within a COUNTIFS

    Hello and thank you for looking at this. I have just started using INDIRECT and am finding it very challenging. I have a couple of use cases. One I have solved, this new one is killing me on syntax. I am using a dropdown to change a year. When it changes my formula updates to reference a new...
  8. C

    Lookup within multiple columns

    I have a supplier who decided to populate data in a complex way thus making life a bit difficult in working with a form. Item are placed like the below table.. this is just 1/8 of contents. I need to lookup in this sheet find the supplier code which is in either column A, C, E or G then return...
  9. R

    Explanation of a formula

    Hi All, I am currently working on a large data set. I took the 10 largest values within this data set using the large formula. After this I wanted the area these values came from and found this formula online, =INDIRECT(ADDRESS(1,SUMPRODUCT((B1:M9=A13)*COLUMN(B1:M1)))). It seemed to work but...
  10. B

    Summing across multiple sheets using indirect

    Hi all, trying to sum up across multiple sheets. Caveat: the cells being added up are all the same cell address as the active cell, just on different sheets. My troubles: I'd like to be able to insert rows or columns across all tabs and have the formula adjust to always be summing the equivalent...
  11. K

    INDIRECT and SUBSTITUTION creating#REF Error

    I have a column of sheet tab names in Column A. I would like Column B to reference cell G44 in each corresponding sheet tab. This is the code I am using. However, it is giving me a reference error. Does anyone have any idea what I am doing wrong? =INDIRECT((SUBSTITUTE(TRIM(A6),"...
  12. V

    How to recognize two result in one cell with indirect

    Column F: i have used drop-down list and below code to get two values in one cell, >>>>>>> Private Sub Worksheet_Change(ByVal Target As Range) 'Updated by Extendoffice 2019/11/13 Dim xRng As Range Dim xValue1 As String Dim xValue2 As String If Target.Count > 1 Then Exit Sub...
  13. X

    Pulling Specific Cell Value from Multiple Workbooks

    Hi guyz, Please excuse me if this question has already been asked multiple times. I am very bad with the VBA Scripting and would appreciate any and all help with my query here. So, what do I need help with? I have a folder in my C:\ Drive with the name "Acc". In this folder I have Multiple...
  14. T

    COUNTIFS and INDIRECT

    Hello, I am trying to use COUNTIFS and INDIRECT but my formula is not working. I have different sheets, and each sheet represents a store location. In each sheet, there is a Sales Person name and the sales amount generated for each store item (e.g., shoes, tops, dresses). In my summary sheet...
  15. N

    Using INDIRECT Function in Google Sheets conditional formatting

    Hi guys! I have a column that I need to color with conditional formatting based on two different criteria. I am using the following formula in the conditional formatting pain and it does work: =AND(AC2="Yes", AK2=INDIRECT("Partners!B6")) The issue is I need the range of Partners!B6:B not just...
  16. L

    sumifs with multiple tables

    Hello, I have a question and i hope someone can help me out. I want to use the indirect function. I already used it in F1. I did =VLOOKUP(E1,INDIRECT(D1),2,0) Now I want to do it the same way in F3 but now with sumifs or maybe another function? I actually want a value of 18 in F3. How can I do...
  17. F

    SUMPRODUCT(COUNTIFS(INDIRECT

    Hi, I am attempting to use two countif conditions across multiple sheets (which I have named "Tabs" in name manager) to count how many times a job is raised for an item with a certain reference number in a particular year of its ownership. I have used a no. of forum posts to get me to where I...
  18. M

    "Floating" Index Match

    Hello all! I have spent many hours attempting to solve this problem and hope one of the geniuses on this forum could please help! I think I am doing something simple wrong, and it might just take a quick lookover. I have a monthly task where I need to take my employer's budget from their...
  19. U

    Swap values in the rows and column based on given value that are extracted from a parent child data sheet

    I have a Excel table in the form below that was extracted from a separate sheet: Level1 Level1-Level2 Level2 Level2-Level3 Level3 Level3-Level4 Level4 a b c d f i j b a d e g g k b e c h d i The Level1 is parent records, Level2 is child records, Level3 is grandchild...
  20. U

    Want to fill and swap values in the cells and column

    I have a excel table in the form below: Level1 Level1- Level2 Level2 Level2- Level3 Level3 Level3- Level4 Level4 a b c d f i j b a d e g g k b e c h d i The Level1 is parent records, Level2 is child records, Level3 is grandchild records and so on... Level1-Level2 is...
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

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 MrExcel.com.
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 "mrexcel.com".
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
Top