formulas

  1. X

    On Excel, how can I tell which other worksheets dependents are on?

    When I click "Trace Dependents" for a cell on a given worksheet and the little arrow picture shows that it has dependents on other worksheets in the workbook, how can I find out exactly which other cells of which other worksheets are dependents of the original cell?
  2. Y

    Sum all cells in column B for all the cells that have the same text in column A

    Hi, I need a formula that will add the values in column B for each member (Member names are sorted alphabetically): Column A - Members Column B Sum (Required result) Member 1 4 10 Member 1 6 Member 2 4 4 Member 3 3 5 Member 3 1 Member 3 1
  3. F

    Select only rows close to specific date

    Hi, I have a database in Excel, where Column A have 100 specific values, that are updated each month, and column B have a price, and Column C have the date which the values have been updated. But some month not all values are updated may only 60 or so. I need a way to get the values closest...
  4. J

    VBA - How to make formulas dynamic when inserting new rows?

    Hello everyone, I have a macro that insert new blank rows, then copy and paste some rows, based on a input userbox. Those last rows contain formulas but, once pasting them, they become incorrect (problem of references). I'd like to make the formulas dynamic in the macro code (via R1C1) or via...
  5. G

    formula to match next value if value is in row above.

    I have this formula, I am running into an issue when I want the nth value, but it matches the nth value for another column, then does a match to get what value in this column. Visual is below. Column B matches the index of Column C to return a value based on the value. I want it to show what the...
  6. G

    Find match for nth value with multiple criteria

    I have this formula, I feel I am making a very small mistake, but I keep receiving the #N/A error. I've been working on trying to find a fix to it for about 2 days now, any help would be greatly appreciated. 1 2...
  7. A

    VBA for total and subtotal

    Hello, I have a spreadsheet with column I where there is a lot of data. I have the 1000 which is the sum of the 2x500 (bold) The 2x500 are the sum of the data (first one is 200 =300 and second one is sum of 5x100) I would need a VBA that would populate the formulas taking in consideration the...
  8. L

    Make =Sumproduct ignore blank cells instead of returning "#VALUE!"

    DEFGHIJKLMN21 varm1 varm1 varm1 varm1 kold1 varm#VALUE!#VALUE!#VALUE!31 varm1 varm1 varm1 varm00040260,00 kr.42 kold2 kold2 kold0001 varm16455,00 kr.51 varm1 varm1 varm1 varm1 varm1 varm1 varm70455,00...
  9. W

    Help! Formulas Disappearing After Every Save

    Hello, I have a problem that I can't find any information on online. I hope someone here can help me. I work with excel workbooks that have several spreadsheets (tabs). On some of the workbooks (no rhyme or reason I can find), any formula that is pulling data from another spreadsheet within...
  10. L

    Excel Inventory List

    So I have a master list of inventory that I have accumulated over the last 8 years (Over 7000 items). I am looking to actually inventory my 'on hand' items (approx 1000 items) into an excel spread sheet. 'Parts Costs' pic has the exact set up of how my inventory list has been accumulated over 8...
  11. M

    Finding Duplicates across Multiple Tabs

    I'm trying to label rows as "Duplicate" in Column Q if i have a duplicate phone number in Column G across multiple tabs. I'm struggling with the formula. So far, I have: =IF(COUNTIFS($G$8:$G$2020, G8, 'FW 4'!$G$8:$G$2020, G8),>1,"Duplicated","") But it's not recognizing the second Criteria...
  12. D

    Copy/Paste Range issue with VBA when cells are empty (but with formulas inside)

    Hello, Currently having some issues when copy/pasting some range of rows. There are some formulas in there that would be populating depending of another table. So in the end there would be some rows populating from such table, and eventually remaining rows without populated values would remain...
  13. M

    Help to with nesting formulas.

    Hi, I have two formulas that I would like to combine into one if it’s possible. Formula 1. =IF('ROTA 1'!$C3="LAPSED","",RIGHT(IF(ISNUMBER(SEARCH("RTO",'ROTA 1'!E3)),"",'ROTA 1'!E3),9)) If cell E3 = (*)0655-1425 it will return 0655-1425 If cell E3 contains RTO it will gave a blank...
  14. V

    ISO WEEK NUMBER FORMULA

    Hi all, I have a date which is 31/12/2019 and when i use this formula : =WEEKNUM(E8435,21) its returning week 1 2019 My question is why doesnt it show week 52 2019 Any help is appreciated.
  15. L

    Data Cleaning and calculations

    Hi, I have been trying to create a calculator which looks at returns from a stock if you average in over time (average in is when you put $2000 into a stock every month or two months or so). Using data from yahoo finance such as...
  16. A

    VBA Code for Summing Data Above total until a blank row

    Hi All, I'm working on a project to automate a manual process currently in place and am struggling with one piece of the macro puzzle. I have a spreadsheet with data across several columns and rows, separated by a blank row with totals underneath each data category. The totals are generated by...
  17. X

    Use Formula to Insert Fortnightly Payments in Table

    Hello, This is my first post and I'm hoping to get a bit of help with creating a formula that I can use on a finance table that I've created. I am attempting to forecast my personal finances and want to insert fortnightly payments into a column automatically. I've managed to do this for my...
  18. B

    Simple Formula, but i can't get it right?

    Good afternoon all, I have 4 dates across a table, I want to return the smallest date so have used the below, however if that cell is blank I want the cell displaying the date to be blank? This is what I've tried =SMALL(D2:K2,1,D2,"")
  19. S

    Help with Pivot Tables

    I have spreadsheet with the performance data of 50 search engine search terms across multiple ad campaigns in Google and Bing. For each of the data columns below, I would like to create a formula I would use to aggregate the data in a pivot table: 1) Impressions 2) Clicks 3) Click-Through Rate...
  20. J

    Removing duplicate values in multiple columns

    I'm wondering if theres a way that I can remove duplicates or apply conditional formatting to columns where the values in two columns are identical to those in another row. For example, I would want the formulat to be able to identify that A1 and B1 were identical to B1 and B2. However, it...

Some videos you may like

This Week's Hot Topics

Top