Formula to show a date range based on the amount shown in another cell

Attached is a excel file that has a working formula for tracking cashier variances. I edited out names etc.

I added a new cell called Track Back on the employee search sheet.

What I want to do is only show variances for the amount of days back selected in the Track Back cell.

For example if I select the last 30 days, only the last 30 days would show up below in the sheet.

I am not sure if this is even possible based on the forumla that is already on the sheet. I couldn't figure out a way of doing it. But there are a lot of people on here much better with excel than me

Thanks for any help with this, I really appreciate it.

Nick

I think you should be able to do this yes, by modifying your formula in row 102 on Variance Tracking sheet such that it also validates date header falls within acceptable bounds... by last 30 - you mean 30 days from today backwards ? To help further we need to understand these boundaries more fully.

On an aside your existing formula in row 102:

F2: =IF(INDIRECT(ADDRESS($B$101,COLUMN()))<>"",N(E102)+1,N(E102))
repeated

Is volatile given use of INDIRECT etc... better I would say to use INDEX, eg:

F2: =N($E102)+(INDEX($F:$F,$B$101)<>"")
copied across

so to reiterate the key will be to adjust the above to incorporate a further test which identifies header date to be valid but first we need to understand your time boundaries.

My last formula was not correct by the way - typos... doh!

First I would do something like

F11: =TODAY()-F$12
copy across

Then revise row 102

F102: =N(E102)+IF(F$11<='Employee Search'!$B$4,INDEX(F:F,$B$101)<>0,0)
copy across

should work...

Note: you have LOTS of sumproducts - performance will be affected.

On my point re: SUMPRODUCTS ... given you're using XL2007 I would actually advise the following on your Variance Tracking sheet:

C13:
=SUM(SUMIFS($F13:$ZZ13,$F$12:$ZZ$12,">="&$C$1,$F$12:$ZZ$12,"<="&$E$1,$F13:$ZZ13,{"<0",">0"})*{-1,1})
copied down

D13:
=SUMIFS($F13:$ZZ13,$F$12:$ZZ$12,">="&$C$1,$F$12:$ZZ$12,"<="&$E$1)
copied down

E13:
=SUM(SUMIFS($F13:$ZZ13,$F$12:$ZZ$12,"<="&TODAY(),$F$12:$ZZ$12,">"&TODAY()-180,$F13:$ZZ13,{"<0",">0"})*{-1,1})
copied down

I would actually advise you look at using a Dynamic Named Range for your data so you can keep to a minimum (see link in my sig.)

The above are not backwards compatible with earlier versions given use of SUMIFS function but SUMIFS is far more efficient than SUMPRODUCT.

For that chosen employee there are only 2 transactions in the last 90 days both of which are reported (5th & 20th July). Confused. What do you think the results should be ?

(on an aside you've put the wrong formula into Column C)

Re: Water Cooler - you can request membership via your User CP (group membership - I think!)

Please remember to mark thread as solved (see FAQ / HowTo? if needed)

Thanks

Yes, never be afraid to test yourself !

Remove the filter from row 12, highlight A12:B12 and apply filter... row visibility affects all cells - ie hiding all but first employee will hide all cells other than those on that row.