offset

On mainSheet, I want to row by row show the values on otherSheet, which have a few blank rows between each. Seems like it should be simple enough, but getting the correct formula into the green cells of column B on mainSheet is proving difficult. I've tried too many combinations of...

I'm looking for a excel formula that will provide average sales before and after a date when a facility was closed. In column A I have the facility location #, and in the row going across I have weekly sales data from 2022 to 2024: During the time of 1/10/2023 to 2/10/2023 the facility was...

I need to insert new dates as I add new dates I need to calculate the 4 wk, 8 wk, & 13 wk. For example, if I inserted 2/10/2024, I would need the formula to take into account the new date and provide the 4 wk, 8 wk, & 13 wk average. I used this formula: =ROUND(AVERAGE(OFFSET(D4, 0...

For Excel VBA, I am trying to make a code which looks at the value in the column "case type" and let it paste a formula or clear a cell, based on said value in the cell of the column "case type". For Case type 1, Column 1 should contain be cleared of any values and Column 2 should contain...

Hi, I have a table of monthly performance data where each new month the previous month's performance is annotated. I'm looking to use a formula that calculates the 1, 3, 5 year returns automatically and allows for the fact that the table or performance data increases each month. This formula...

Hi, if I want to use Offset but want to do this based on a value , so I want the cell reference containing X as my starting point, I have tried ; CELL("address",INDEX($A$1:$A$13,MATCH(D1,$A$1:$A$13,0),1) )[ = $A$4 Which returns the correct cell reference but when I nest in Offset I get...

Hi Experts, I been thinking and try change current formula but not succesful to meet my need. Current Formula : Start from currrent cell GIJ1datatotalCount230.63240.150.860.3J3J3=IF(I3="","",COUNTIF(INDEX(G:G,ROW()+I3-1):G3,">0.5")) The best solution I have tried so far : but the result...

So I have I have a list with categories and base o the category that I select a Dynamic Range with subcategories is created with Filter,Unique and Order functions. In the following columns I have the sales from Jan to Dec which is connected to s spark lines. The thing is that not all categories...

Hi all, Hoping someone can help me with this query. I have a workbook I am working on that currently sums all the values in column "O" if the value on the same row in column "L" is "WP" or "BS" until an empty cell is reached. I would like to expand upon this formula so that it still sums like I...

Greetings experienced, With my limited knowledge of Excel, only know basics, I was thinking to use function "=IF(AND(N4=1,O3>0), , ) " & "(INDEX(O:O,ROW()-O3-1" ,in formula but do not know how to structure/complete it. When cell in Column N =1 and above row of it, cell in Column O >0 , assign...

I have a stream of cash flows with with two different Categories by which I would like to generate IRRs. I know you can create TRUE/FALSE ranges to be referenced by formulas and I think this is probably the way I want to do this? Basically I want to calculate separate IRRs for apple/banana/pear...

Good Evening, I'm trying to drag/copy-paste an indirect formula where the row value in the reference changes. Then continue the pattern over hundreds of cells. This is the general idea: =INDIRECT("Sheet1!A3") =INDIRECT("Sheet1!C3") =INDIRECT("Sheet1!E3") =INDIRECT("Sheet1!H3")...

Two parts to this query: 1) I've written the following code to address each row within range (F15:F44), aka table "WellList[ERP Group ID Number]". It works, but it's rather ridiculous and repetitive. I'm looking for something more concise/elegant which will still allow the user to drag the...

I have a output from an accounting product and I need to reformat the table. At the bottom starting at row 23 you can see an example of the new worksheet I am trying to create. Can I get help with a formula to help automate this? The original report has 742 more accounts like this I need to...

Attached is a screen cap of the imported dataset. Please note how the data is grouped with multiple labels and values in a single cell. I need to find the match JSN code which is located in a single cell along with miscellaneous data. The "JSN" text has (4) spaces after followed by the (5)...

I've found code that will copy select data from (Sheet1) to another (Sheet2) ; using only values found in columns "B:E" and "I:K". Columns "I:K" will always have data on each row pasted on to Sheet 2. Columns "B:E" may not have data on each row. Code from Sheet1...

Is there way to create a dynamic named range based on cell values from other columns? The cells in the named range may not be contiguous. For example, I have a list of holidays or other observed days. I want a dynamic named range of the Day column to reference only those marked as Type...

Dear Mr.Excel, I am struggling with OFFSET function when source row is deleted. Here below is basic example: 1) Sheet2.Range("A2:R50") is my table with data 2) Sheet1.Range("A5:R20") is my table with offset data + scrollbar. If I remove any row in table 1), I automatically get #REF in Offset...

These cells are conditionally formatted to be red if the cell directly above is less than it and green if it's greater than. Each cell contains a simple function of SUM the row to the left plus the cell directly above it. I'm pretty new to Excel so I've been testing functions out, and now I'm...

Hello: I am looking to create a macro that will be a ribbon button, that will copy values that have already been selected by the user, in either columns C,D or E and always paste them in column B. See table below for an example. The user selects cells 1, 2 and 3 of data in col C and runs the...