Tag: excel-formula

Excel – Is there a way to change direction of a formula result when result changes? Rather than it dropping out of the bottom of the cell for example?

When a cell returns a result the visual change always has the ‘old’ data dropping down and ‘new’ data in the cell coming from the top. Is there a way to change the direction of movement? It will look really good on the ‘mock-up’ I am building. Thanks

Filter excel data by most recent date

I have a list of IDs, dates and scores and I need to get the score corresponding to the latest date. The data looks like this: ID | Date | Score ___________________________ 123 | 03/15/2016 | 10 123 | 08/10/2016 | 4 456 | 09/01/2016 | 7 456 | 02/02/2016 | 10 678 | 07/01/2016 […]

How to multi criteria search in VBA?

I currently need to match 3 different criteria within a table then return the value from a specific column within that table into a table in another sheet. The table I need the values to be placed in is full of data and I only need BLANK cells within 1 specific column to be fill […]

Conditional Formatting Applying to a range instead of the individual cell

The following code is intended to format the cell if the value entered is No and If the value of the cell in same row and some columns before is listed in the sshDevices array Function sshProblem(rng As Range) As Boolean Dim portStatus As String portStatus = rng.Value Dim deviceType As String deviceType = Cells(ActiveCell.Row, […]

excel formula creation using nested if statements

I need help creating a formula in excel. I am essentially looking for estimated revenue per listing. I am assuming each booking always has 2 guests – unless it accommodates only 1. The booking is always for the minimun number of days, and the extra person charge is per night. I need to create a […]

Copy formula through columns

Good afternoon in my timezone. I am using the row() function in excel cell like this way = ROW() First question : Why this does not evaluate to the real row number and the cell maintain the “= ROW()” text Second question : If i try to copy the cell content(in this case the “=ROW()”) […]

Repetitive copy/pasting with a change in a only a few fields

There is a worksheet that I have to create on occasion that I need to constantly copy/paste (default info) and then only change 2/3 fields (with info from another worksheet) in order to populate the worksheet. Because of the repetitiveness I feel like there could be a macro/formula which could greatly improve the speed of […]

Excel regex match and return multiple references in formula

I’ve created a function that will return the Nth reference which includes a sheetname (if it’s there), however it’s not working for all instances. The regex string I’m using is ‘[\w ]+[‘]!([$]{0,1})([A-Z]{1,2})([$]{0,1})(\d{1,5}) I’m finding though it won’t find the first reference in either of the below examples: =’Biscuits Raw Data’!G783/’Biscuits Raw Data’!E783 =IF(‘Biscuits Raw Data’!G705=””,””,’Biscuits […]

Join rows based on unique ID

I have 32.000 rows with data. Some data are in a different place and I want to join them with something that I can apply to all rows and not manually. Each “group” have the same ID, in this example is “XPTO” I have something like this now (but with more columns): I want it […]

Concatenate and Justify in Excel

I have the data below: I would like to locate the maximum length of the string of texts in the first column, and from there count the number of spaces to position the second concatenated item to align. My attempt to concatenate has the below formula =A1&REPT(” “,40-LEN(A1))&TEXT(B1,”0.00%”) and only got this so far as […]

MS Excel Spreadsheet is the best Office Software, Excel VBA and Excel Formulas make Spreadsheet work faster.