+ Reply to Thread
Results 1 to 7 of 7

Moving data daily to a different column with the today function possible?

  1. #1
    Registered User
    Join Date
    07-27-2015
    Location
    south dakota
    MS-Off Ver
    2010
    Posts
    5

    Moving data daily to a different column with the today function possible?

    OK here's what I'm trying to do.

    I have a score sheet that is updated on a daily basis. Each column (A,B,C,D,etc) on that sheet is for a different person. Each row (1,2,3,4,etc.) is a different subject.

    Then each person has their own sheet to track their scores over time. Columns are day 1, day 2, etc, Rows again the subject.

    It's easy enough to copy and paste them each day from the daily sheet to the persons individual sheet.

    However I'm looking for a way to have it auto update and was wondering if there was a way to compare the dates in the cells to do this.

    Such as on the persons sheet if day 1 equals the same date as on the score sheet then copy those cells over from the master sheet to the persons sheet on day 1.

    I know there are macro's and such to do this but I am limited to what I can do on work computers I pretty much have to stick to formulas no matter how tedious it is.

    I didn't make the original sheets and I'm still learning excel by trial and error. The individuals persons sheets auto-populate with dates in each column once a starting date is entered in a cell at the top of the sheet.

    I do however have permission to pretty much do what I want with the sheets when it comes to this even if I have to do a rehaul of the entire thing.

    If it's easier to just copy and paste let me know.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Moving data daily to a different column with the today function possible?

    From what you've described, I don't believe it's possible with formulas. You are talking about clearing the scores on the score sheet daily? Any formulas that you use to pull those values into Day 1 will lose those values once you clear the board on Day 2. You could copy and paste special > values before the end of the day but that's not much better than simple copy paste (depending on your layout).

    Now, if you're not talking about clearing the board day by day, that's a different story.
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    07-27-2015
    Location
    south dakota
    MS-Off Ver
    2010
    Posts
    5

    Re: Moving data daily to a different column with the today function possible?

    Thanks, thats what I was thinking also. We date the main sheet and place it in an old scores folder then start a new sheet off of a master sheet for the next day. I've come close to doing it but nothing I've done seems to really be any better than just copy and paste. Although I'm sure I'll find a better way eventually but until I have that ah ha moment I have bigger projects to work on.

    I thought about trying the special values because every persons sheet is created from 1 master and I would only have to get the master sheet pulling the values once a date was entered to start the continuous date generation. But that would be a lot of work assuming it's even possible with a formula, we may have a person for 2 days or 200 days and every day would have to be done individually.

    Then the question is can I enter it on the master sheet used to create each persons sheet and keep it intact before a date is entered. I know it's possible as I had it working once and I thought I copied the formula. But low and behold I lost it. So I never got to test it to see what would happen when the sheet it pulled the data off of was placed in the old scores folder and a new one was created with the master.

    I never give up so I think at some point I'll find a solution maybe a crazy one but if it works who cares.

    I'm the one person who will spend months trying to figure out a way to take 1 minute off my time required to do something lol.

    Thanks again.

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Moving data daily to a different column with the today function possible?

    If you are saving the old scoresheets, then you might be able to do this with INDIRECT function. How do you name each sheet? (i.e ScoreJune21)?

  5. #5
    Registered User
    Join Date
    07-27-2015
    Location
    south dakota
    MS-Off Ver
    2010
    Posts
    5

    Re: Moving data daily to a different column with the today function possible?

    Their saved by date such as 7-18scores.xlxs, 7-19scores.xlxs etc. However I don't name them so the name may not always be exactly the same some people may and chances are will add the year to the end at some point. So that was the other problem I did come across. For now I'm just going to stick with copy and paste and try to redo the forms in my spare time and make them more formula friendly for lack of a better term.

  6. #6
    Registered User
    Join Date
    07-27-2015
    Location
    south dakota
    MS-Off Ver
    2010
    Posts
    5

    Re: Moving data daily to a different column with the today function possible?

    I found a way to do this but still working the bugs out just for an FYI of anyone else trying to do it.

    The daily form I have excel display the current day of the week, then on the tracking form I have the column under Monday for example cross check the daily form to see if it's Monday and check the column's name and if both match then populate it with the scores in that column. Then before midnight you just have to open the one form, highlight that column copy, then paste the values.

    Now there is a record for 10 weeks so every single Monday column will be populated until the next day when every Tuesday column is then populated. Once the scoring is done the 10 week record is moved to another folder, breaking the links and causing that days column to disappear (if they leave after week one, week 2-10's Monday for example when moved to the other folder will no longer have values)

    Right now I'm working on a formula to check the name on the 10 week form with the 17 different possible columns that may contain their name on the daily form. Because people come and go it is possible the names could be moved around sense it's just a daily form. I know a very long way to do this but looking to keep my formulas short. I don't want to have to wait 30min for the form to load each time lol. Will update if I find a quick way.

  7. #7
    Registered User
    Join Date
    07-27-2015
    Location
    south dakota
    MS-Off Ver
    2010
    Posts
    5

    Re: Moving data daily to a different column with the today function possible?

    For future reference I have done it all with just formulas yay lol.

    On the individual sheets I have it cross check the day of the week then cross check the name with the names on the daily sheet. with nested IF AND statements.
    then assign a number 1-15 for example
    =IF(Sheet1!B2="Monday", AND(B4=Sheet1!B4, "1" etc.

    Then each cell on the individual sheets that scores are stored in checks the number assigned to it (there are 7 rows one for each day of the week) so underneath each days row is where the above formulas are.

    So if it's a 7, it copies whatever is in row 7 on the daily sheet for example:
    =IF(C36="1",Sheet1!C36, IF(C36="2",Sheet1!D36 etc.

    This populates the current day with the scores you still have to highlight the entire row click copy the click paste values but it does eliminate having to go between the two sheets. Now to get everyone to name "sheet1" the same name every day will be the trick lol.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 8
    Last Post: 09-26-2014, 03:51 PM
  2. [SOLVED] Single column countif with moving range based on today's date
    By JonesyCC in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-16-2013, 02:45 PM
  3. [SOLVED] Inserting daily quote based today date
    By vickicr888 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-10-2013, 12:56 PM
  4. Replies: 15
    Last Post: 02-06-2012, 04:25 AM
  5. [SOLVED] Returning most recent daily data in a column if blanks in column
    By InnesMcc in forum Excel General
    Replies: 6
    Last Post: 11-04-2011, 09:41 AM
  6. [SOLVED] Returning most recent daily data in a column if blanks in column
    By InnesMcc in forum Excel General
    Replies: 2
    Last Post: 11-04-2011, 06:28 AM
  7. Replies: 6
    Last Post: 08-08-2011, 08:32 AM
  8. Save daily workbook with day of today
    By Tha-G in forum Excel General
    Replies: 4
    Last Post: 03-21-2011, 12:30 PM

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