Forum Discussion

Andrew_Hinson's avatar
Andrew_Hinson
Brass Contributor
Apr 08, 2023

Formula in filtered column

Hi everyone,

 

I wonder if anyone is able to help me. I have a large selection of financial data which comprises various columns which I use to input transactions, line by line. In column C, I have dates; in column L, I have a drop-down list to select a type for that particular transaction, and in column M is where you can put the value of that transaction. So it would work as follows:

 

     Date                          Type                    Value

08/04/2023            Motor expenses        250.00

 

When I filter the type, so it is only showing all the transactions relating to that particular type (there are about 20 of them), I want to then put a formula in the date column that says =cell above+14. This is because with that particular 'Type', a transaction will occur every two weeks. I want to be able to filter the cell, copy this formula down to all cells, and then when I take the filter off those transactions will just be in the list but only on the dates that my formula is creating. The issue I have is that the formula of course works when the column is filtered, but when the filter is taken off, the cell above the particular date cell with the formula is different, because all of the data is then present, and therefore the formula is taking whatever cell is then above it and adding 14.

 

Can anyone advise how I can create a formula in the filtered column, copy it down (ignoring hidden cells), and then when I remove the filter so the formulas stay relevant to cells that were present when it was filtered?

 

I am first of all having trouble copying the formula down, and cannot drag it down like to would normally autofill, because there are hidden columns. I have tried copying to visible cells only and other tricks I have found online but nothing has worked yet! I fear I may be in the position that I have to individually put the formula into every single cell!

 

Thank you in advance 🙂

 

 

Formulas and Functions
  • mathetes's avatar
    mathetes
    Silver Contributor
    Apr 08, 2023

    Andrew_Hinson 

     

    Assuming I am following you, what you're doing and what you want to do, I have a couple of thoughts. First, let me say that I track my finances in a somewhat similar way. And I've learned--assuming you have all these rows and rows of transactions as a legitimate Excel Table--...I've learned that putting formulas into columns in Tables can be tricky unless you are wanting that same formula to be applicable to every row. So be careful, whatever you do.

     

    That said, for this particular need, I would recommend trying what we often call a "helper column," which is to say, do not try to have this added 14 days applied in the same column as the dates for all the other transactions. Instead, have a separate column that tracks what might be called "AdjustedDates" and put the formula there that finds the last date used for that category and then adds 14 to it. You could make that formula particularly smart and have it just copy all other dates as they are, and then that AdjustedDates column becomes the one you use in a Pivot Table, say, if you use that to create monthly reports.

     

    I have a formula I've created that does more or less what I'm suggesting, finding the last date when the category appeared. (You might need to play around to make sense of this; I'm assuming you have that ability.) The XMATCH portion is key, and the -1 there at the end is the key to the key: it is what finds the last match in the column, as opposed to the first, which is what we generally use MATCH and XMATCH to do.

    =INDEX([DatesColumn],XMATCH("Category",CategoryColRefs,0,-1))

    • Andrew_Hinson's avatar
      Andrew_Hinson
      Brass Contributor
      Apr 09, 2023
      Hi mathetes
      Thank you for such a detailed response! To be honest, I am better than average with Excel but by no means an expert. I am aware of some of the things mentioned in your response, but I may need to do a big of Googling for the others. I am sure I'll work it out!

      So truly thank you 🙂
      • mathetes's avatar
        mathetes
        Silver Contributor
        Apr 09, 2023

        Andrew_Hinson 

         

        You're welcome.  I recommend ExcelJet as a great place for that research. Also YouTube has a wealth of instructional videos.

         

        Feel free to come back and post a copy of your worksheet (perhaps a mock-up rather than the real data) if you'd like a more hands-on example.

Resources