dynamic formula

  1. Z

    Need Help Converting this Formula to Use CONCATENATE Instead of &

    I am stuck trying to convert this dynamic formula to use the CONCATENATE function instead of the &s. I am using an Excel add-in that does not accept "&" in formulas. I have tried several times to do this myself but cannot figure out what I am doing wrong. It doesn't seem to play nicely with the...
  2. B

    Using combination of LOOKUP and INDIRECT

    I have multiple sheets - 1 is an index and the rest are project sheets 1801, 1802, 1803.... The index has a column "B" that lists all the project sheet names. I am trying to determine the last populated date in each project sheet - also column B. I am able to determine the last populated date...
  3. P

    Dynamic Ranges (without range address unavailable) inside a Formula

    Hey Guys! I immensely need your help for the below scenario. I am trying to find a way to address range in a formula. The problem is that, the range is created dynamically. Please refer below image further. Here I have 2 tables say Table 1 & Table 2, these tables are snippets of my actual...
  4. K

    Nested IF and LEFT functions

    I'm not entirely sure how to nest a string of IF functions with a LEFT function. My IF function is as follows (located in cell W2): =IF(I2<>0,I2&", ", "")&IF(J2<>0,J2&", ", "")&IF(K2<>0,K2&", ", "")&IF(L2<>0,L2&", ", "")&IF(M2<>0,M2&", ", "")&IF(N2<>0,N2&", ", "")&IF(O2<>0,O2&", "...
  5. K

    Formula to show contents of multiple cells comma seperated in a single cell

    I am looking to use a dynamic formula to show contents of multiple cells comma separated in a single cell. For example, I have a range of 10 cells (horizontal) that may or may not contain a value. I would like the formula to simply show what values are in that range and update as data is...
  6. B

    CONCAT or INDIRECT not working for dynamic formula

    Hello, I have used the INDIRECT in a bunch of workbooks to dynamically create formulas based on 1 or multiple cells. Usually to reference a worksheet tab name based on date or function. A3 = 10012015 (cell formatted as text as I need leading zeros) E.g...
  7. B

    Help with a formula

    Hi, I am trying to create a formula that brings back a month number based on where a value is input. For example I have month 1 to 48 in column B from B1 to B40 and in column G1 to G40, I would place randomly the value 1000 aligned with Column B months for example months 1-13-19 etc. In...
  8. W

    Loop through a range populating with a dynamic formula to sum a row

    Hi All, I hope that I'm just missing something simple here -- I can't get the following code to work. Sub sumfornondddgrid() Dim lastrow As Integer Dim lastcolumn As Integer Sheets("02Grid by Dept and GLCode").Activate lastrow = Cells(Rows.Count, "B").End(xlUp).Row lastcolumn = Cells(1...
  9. S

    VBA: Find the average of a column with a dynamic range

    Greetings, I have data in the range P14:P28. I wrote a macro that appends a new data point at P14 every time the macro is used, which increases the range of the column to P14:P29, and also calculates the average of the new range (P14:P29) in cell P31. The problem is that I need the formula to...
  10. S

    Vba: Find a percent change using a dynamic range

    Greetings, I have a table in the range D14:N28. I wrote a macro that appends a new year of data at row 14, which increases the range of the table to D14:N29, and calculates the percent change for each column in row 31 (e.g. (D14-D29)/D29 ect.). The problem is that I need the formula to be...
  11. I

    Adding many dynamic formulas into Name Manager

    I have made a table with many dynamic formulas that I want to import into the Name Manager. It has the names in Column A and the "Refers to" formulas in Column B. Unfortunately, when highlighting the cells and using the "Create from Selection" feature of Excel 2010 it puts the Column B cell...
  12. A

    Excel Formula Using Defined Names

    Ok, so I'm probably just not using the correct search terms (wouldn't be the first time), but I'm on a bit of a time-crunch with this one: I have a list of numbers which match up to a defined cell name on a different sheet. I'm hoping to make the results variable. For example what I currently...
  13. B

    =GETPIVOTDATA dyanmic formula based on cells value

    Hi, I'm trying to get a =GETPIVOTDATA formula to update daily based upon todays date but am struggling. The data feeds from a SQL DataMart and the formula is: =GETPIVOTDATA("[Measures].[Case Completed -Invoiced - Count]",SBVSDATA!$A$6,"[Time].[Day]","[Time].[Day].&[25]","[Lender].[MI...
  14. C

    drag or copy formula as dynamic formula

    This is probably a simple question but I'm puzzled by the fact that this formula won't drag/copy as a dynamic formula should.. it remains stagnant as if i were useing $ on column and row refs. In this example, row 22 is the only specific reference...
  15. R

    Dynamic formula help

    Hello, I am trying to create a macro where formulas are dynamically inserted into cells. I get this error when I try to use this macro "Expected: end of statement". I currently have: intFormula = r + 6 Range("J" & intFormula).Select With Selection .formula =...

Some videos you may like

This Week's Hot Topics

Top