1. X

    Reference to table in different workbook is inconsistent?

    So... basicly what the title says. I have a formula which lets me filter values from a huge table as a database. the formula is as follows: =LET(Advanced, IFS(F2<>"",SEARCH(F2,'Verbrauchsmaterialien Datenbank (work in...
  2. E

    Dragging formula along columns with changing reference cells

    Hi, I wrote the following formulas in Cell B2, C2 and D2 of Sheet 2: B2: =IF(AND(Sheet1!T2<20160000;Sheet1!U2>20150000);Sheet1!V2;"No") C2: =IF(AND(Sheet1!X2<20160000;Sheet1!Y2>20150000);Sheet1!Z2;"No") D2: =IF(AND(Sheet1!AB2<20160000;Sheet1!AC2>20150000);Sheet1!AD2;"No") I want to drag this...
  3. S

    Bulk Import Named Ranges

    Hi, I have a list of names and range references to load into name manager. Do you know if there is a way to import them in bulk. For reference I've tried create from selection but it can only store values not the range reference. Example of name to import Name_1...
  4. K

    Finding end date with some criteria

    Hi everyone, I trying to find the end date of production for a given production order. As in the table, I have the start date, of the production, however, the production may take up to 3 days (from 15/01 - 17/01) and officially ends in 18/01. But I cannot find an appropriate formula to find the...
  5. 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...
  6. T

    Update cells in a table using the text in column A as the reference to find the correct row

    Hi everyone, I have a worksheet called DATA TABLE which has some 30+ columns. Located in column A is the specific site name. I have another worksheet called DATA AMEND which I am able to populate specific cells with information from DATA TABLE. I wish to then alter any of the cells in DATA...
  7. A

    How to tweak a reference so that when it's copied one column over, the reference jumps by five columns?

    Let's say Cell A1 in "WorksheetA" references Cell A5 in "WorksheetB". When you copy Cell A1 to Cell B1, its formula would of course typically adjust to reference Cell B5. However, let's say I'd instead like its reference to adjust by five columns, not one column. (That is, without changing...
  8. dageci

    Refreshing References on Excel Workbook Start

    Hello, I have created an ActiveX server control that I'm using from within VBA. But when I create a new version of this control on the user machine I need to go to References uncheck the reference click on OK and then again I need to go to the references and add it to get the fresh version. The...
  9. S

    Referencing a big number of ToggleButtons

    I have a table which has a ToggleButton next to each line. I would like to write a script that does something for each line of the table, but only if the ToggleButton on that line is pressed. Is there a way to define the if condition in an elegant way? I would imagine something like: For i =...
  10. L

    Mirror column from another workbook that moves

    In one workbook, I have a column A which A1 contains "Cities". In another workbook there is a "Cities" column, however, it's not in a static position. Is there a formula that I can use to look across Row 1, find "Cities", and list all the not empty cells in that column on my local workbook...
  11. W

    Countifs not referencing same cells and getting 0

    I am trying to use CountIfs and every thread I come across all reference when someone is trying to reference the same cells for different criteria, which I understand why that does not work. What I am trying to do is reference three columns all for different data, but I am getting zeros...
  12. A

    to get the values from data file to format file, based on sheet name, reference column and date

    I have two excel files, one is file named data and other one is flile named format. The data file has multiple sheet named as "north" ,"south" and "west", each of these sheets have a reference column containing values a, b,c,d,e as values of first column and dates from Nov 4 to Nov 10 as values...
  13. G

    Reference the sheet name in formula as a number

    I'm trying to write the following formula: =MAX('02'!C5:C18) But instead of referencing the sheet named "02" I either want to reference a cell that outputs the name or number of the sheet, or have it reference the current sheet (because there are 200 sheets just like this one and they are all...
  14. D

    Enable Microsoft Scripting Runtime with vba within a Public Sub with Public Declarations

    I have a Public Sub that has Public declarations as Public sdProcess As New Scripting.Dictionary Public sdHeaders As New Scripting.Dictionary I would like to automate the activation of Microsoft Scripting Runtime using this code or something similar...
  15. M

    Sum with Indirect

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

    offset cell reference

    Hi! Wondering what the best way to sum a number of columns based on a particular cell which is dynamic. <tbody> I can use this formula when E3 is static, but wondering how to get E3 to be dynamic based off a cell value <tbody> =SUM(OFFSET(E3,0,-3,1,3)) </tbody> </tbody> Example below...
  17. T

    IF condition for another sheet and whole column

    The goal here it to create a summary sheet. In that summary sheet I need to reference sheet 1 that holds all the products. Sheet 1 has all the products in one column, but some of them do repeat. My thought is to create IF statement that could call out one product in sheet 1 for the entire...
  18. D

    Creating dynamic reference from another workbook

    I want a drop down list in one workbook that references a dynamic list in another workbook ie it may have entries added to it so I can't hard code the reference? If I select data validation for a cell, I set it to allow a list but what do I put in the source for the list to be from another...
  19. C

    How to include a string character with wildcard reference to cell - Countif

    I need to include a Comma plus 1 blank space to come before a wildcard reference to a cell content with countif/countifs Here is how the formal looks without the comma and blank ", " added =COUNTIF(ref_name,"*"&$A1&"*") Is this possible?
  20. R

    How do I copy a formula to multiple cells and only change one reference cell but not others?

    I am trying to copy the following formula that has multiple If Statements referencing multiple other cells for the desired results and only the first cell referenced but not the follow 3 cells referenced in the desired results. =IF(A2=1,'Team 1'!B13,IF(A2=2,'Team 2'!B13,IF(A2=3,'Team 3'!B13...

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