Search results

I’m addicted to utilizing dynamic array formulas now that they are easier to deploy but they play havoc when attempting to integrate with my use of tables (which use has historically been limited). I’m just wondering how you guys are using sequence, filter, etc. in conjunction with tables.

Thanks but this doesn't create the table i am looking for. I probably should have provided a more detailed example. I just want a single column of dates with the closing prices in the columns to the right based on the ticker symbol: Date NNN EPRT 1/1/2021 $50 $60 1/7/2021 $54 $63...

I'd like a to create a dynamic table of historic stock prices based on a list of dates and ticker symbols. The STOCKHISTORY function creates a dynamic output that includes a date column so I cant create a table like the one below and the stock function in the data tab is very cumbersome. Is...

I am looking to calculate the balance of a revolving loan at any point within a schedule of loan advances and payments. For Example: The schedule in the attached model includes 3 advances during calendar year 2022. I would like to be able to see the loan balance on a specific date (eg: at the...

Thanks for chiming in. 1. I’d like the destination hyperlink to be persistent. I am only contemplating this for use within the active workbook (I could see how linking to other locations would be VERY cumbersome). 2. The "Back" button does work (I put it on my QAT) but I'd like a more...

I'd like to automatically the creation of "return" hyperlinks within a workbook. For example if I create "Hyperlink 1" linking Cell A1 in Sheet 1 to Cell Z1 in Sheet 2, I'd like to automatically have "Hyperlink 2" created in Cell Z1 of Sheet 2 that links BACK to Cell A1 of Sheet 1.

Follow-Up Question: In your SCAN formula, the escMonth sets up the model to escalate the rent during a certain month of the year. How would I modify it to deal with a case where there is an adjustment every 60 months (rather than on a specific month annually)?

This is perfect! I'm curious as to why your formula spills and mine didn't? What structure makes the magic work?

See attached. I'd like the formula in the outlined row to spill like the date row that it refers to so they are all dynamic.

Thank You!!!!

The formula below effectively takes a "starting" rental rate (C5) and increases it at a specific rate (C7) one time annually on a specific month (C6). =IF(MONTH(D$3)<>$C$6,C5,C5*(1+$C$7)) I tried tweaking the formula to allow for the addition of a start (C14) and end date (C18) during which...

Solved copying down part with code below. Sub PasteRange() Dim Last_Row As Long Last_Row = Range("N" & Rows.Count).End(xlUp).Row Range("p6:q6").Copy Range("p6:q" & Last_Row) End Sub I've been unable to modify for copying ACROSS - Anyone?

I'd like to create a macro that (on a button click or other event) will copy the formulaic contents of a cell(s) down a column (or across a row) until a condition is met (generally based on the contents of a neighboring cell). The attached workbook illustrates what I am seeking.

I get a runtime 1004 error "Unable to set the Hidden property of the Range class" . When i debug it is tripping at .Columns.EntireColumn.Hidden = True Not sure if this is the problem but since this worksheet has a number of different operative areas between which i navigate, there will...

I'm trying to optimize the code I use to navigate within areas of a worksheet based on button clicks. I currently accomplish this with a macro that unhides the columns and rows representing the destination area, then hides the columns & rows outside such destination which is tedious, especially...

This is a great solution (even if the code is over my head)! The only things i would change / add at the moment are: 1. I'd like for the total to be a few lines below the bottom of the results (not over the date in row 9). I can see where the code is .Cells(30, x) = .Cells(9, x) + i) but I...

Now i see how i misunderstood the "2 columns" question. In reality, I need 1 column of rent numbers that line up with an unrelated row (or column) of dates in a header as i outline above in my response to @GraH - Guido. I am trying to replicate what shown in my example but i wasn't clear...

Spectacular! Almost exactly what i was looking for. As I will always have an array of dates as a header row (as shown in my original post), can the formula be tweaked to use the header row as a lookup, placing the monthly rent from the lease in line with the corresponding period in the header...

This is very close (and awesome)! I'm not sure what you mean by "two columns match" but the only modifications I need would be: 1. No blanks between the rent steps and, 2. The option to have if output horizontally (dates on top, numbers below). I really thought this would have been possible...

I'm analyzing a series of leases and would like to generate a single row that sums up the multiple rent steps in each without having to build a big matrix as shown in the screenshot. Any help would be appreciated!

Thank you!!!!!!! This is going to take me some time to unpack as my knowledge of the LET function is limited.

It works (thanks)! That said, I feel like it shouldn't be that complicated to have a counter array that "starts" once a neighboring array reaches a certain value. Ideally, the cells in the Holding Dates array would have zeros until it reached the matching Master Date as illustrated on the...

Alan - I generally don't use PQ and @GraH - Guido's solution was easier for me to incorporate.

I've been experimenting with permutations of this formula and came across another wrinkle: Is it possible to have the SEQUENCE function triggered by embedded in an "If-Then" statement? Basically, I have a list of dates and I'd like to I'd like to start counting (from 1) once a certain date is...

Spectacular!