+ Reply to Thread
Results 1 to 15 of 15

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

  1. #1
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    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
    Attached Files Attached Files
    Last edited by avidcat; 09-11-2009 at 08:51 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

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

    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:

    Please Login or Register  to view this content.
    Is volatile given use of INDIRECT etc... better I would say to use INDEX, eg:

    Please Login or Register  to view this content.
    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.

  3. #3
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

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

    Yes you are correct. The track back such as in 30, would be the last 30 days from the current date.

    Thanks for your help with this.

    Nick

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

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

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

    First I would do something like

    Please Login or Register  to view this content.
    Then revise row 102

    Please Login or Register  to view this content.
    should work...

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

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

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

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

    Please Login or Register  to view this content.
    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.

  6. #6
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

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

    Thanks for the help, I made the adjustments recommended and got rid of the sumproducts.

    I am not sure if I missed something, but when I entered the updates/changes for the date & the forumla in line 102, on the employee search page it is not tracking back the proper amount of days.

    Update excel file is attached.

    Thanks,
    Nick
    Attached Files Attached Files

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

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

    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)

  8. #8
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

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

    Yep I was confused. I work grave shift and only had 4 hours sleep before work

    I was looking at the wrong row and thought it was showing up wrong.

    Thanks for the link to Dynamic Named Ranges, I am going to be taking an excel class (a few I bet) and I am learning quite a bit here on this forum.

    The formulas that you and others have helped me out with are way ahead of my knowledge and I really appreciate all the help.

    Thanks,
    Nick

  9. #9
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

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

    I attached another example.

    On the employee search page I was wondering if it is possible to return results for a calendar month based on what we have now. That would be if the back track cell was left blank.

    Is there a way of doing that?

    Thanks,
    Nick
    Attached Files Attached Files

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

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

    Yes, you could use something like the below in row 102 to differentiate:

    Please Login or Register  to view this content.
    If TrackBack is left as 0 and date entered into MonthTracking (assumes you would use 1st of Month) then you should get the respective listing returned.

  11. #11
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

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

    Fantastic, was just playing around with it. That was more than I was hoping for.

    Thanks for all your help.

    oh, I was trying to post in the water cooler room. Just wanted to talk about taking excel classes and I don't have access to post. Is that a permission room for only certain members?

    Thanks again,
    Nick

  12. #12
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

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

    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

  13. #13
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

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

    Thanks, yep I try to always mark my threads solved now.

    One last question off this topic but pertaining to the same excel file.

    Is it possible to only show the filter link on the Employee Name and ID and not the rest of the row, but still have it sort everything?

    It is not that big of a deal, but it covers up the dates and I would like to keep the date columns short and use this feature at the same time.

    Sample attached.

    Thanks,
    Nick
    Attached Files Attached Files

  14. #14
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

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

    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.

  15. #15
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

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

    Thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1