+ Reply to Thread
Results 1 to 31 of 31

changing daily value in cell

  1. #1
    Registered User
    Join Date
    05-28-2020
    Location
    Tokyo
    MS-Off Ver
    office pro+ 2016
    Posts
    12

    changing daily value in cell

    I have cell E4 that must change value on daily bases according values that will be entered in cells C20 to R20
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: changing daily value in cell

    Welcome to the forum.

    Try this:

    =HLOOKUP(TODAY(),$A$19:$R$20,2,0)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: changing daily value in cell

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    cp[ay this in E4 and drag it down
    Punnam

  4. #4
    Registered User
    Join Date
    05-28-2020
    Location
    Tokyo
    MS-Off Ver
    office pro+ 2016
    Posts
    12

    Re: changing daily value in cell

    Thank you AliGW.
    Perhaps I'm doing something wrong but if I copy your formula in Cell E4 it gives me error. Also its only E4 that must change value according values entered in cells C20 to R20 so in this case value in E4 should be value that is in I20 etc.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: changing daily value in cell

    What error?

    The range I have given works fine. It finds today’s date in row 19 and then returns The corresponding value in row 20. Don’t change it!

  6. #6
    Registered User
    Join Date
    05-28-2020
    Location
    Tokyo
    MS-Off Ver
    office pro+ 2016
    Posts
    12

    Re: changing daily value in cell

    Thank you Punnam
    Perhaps I'm doing something wrong but if I copy your formula in Cell E4 it gives me error. Also its only E4 that must change value according values entered in cells C20 to R20 so in this case value in E4 should be value that is in I20. tomorrow will be value that will be entered in J20 and so on

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: changing daily value in cell

    Please see post #5 - there is no point in repeating yourself.

  8. #8
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: changing daily value in cell

    Hi Oktalv

    Kindly check Aligw formula its working fine for me and the most both formula are the same except Row(), so update the formula and update the sheet so we can see the error.

    Punnam

  9. #9
    Registered User
    Join Date
    05-28-2020
    Location
    Tokyo
    MS-Off Ver
    office pro+ 2016
    Posts
    12

    Re: changing daily value in cell

    As I mention I need value in E4 to change in to value that is in cell I7 because I7 is last value in data table. Next day value will be entered in Cell J20 and then that new value must be visible in cell E4

  10. #10
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: changing daily value in cell

    Hi
    you are looking quite opposite, for that you need a macro to update the information from e4 to a respective cell in row number 20.

    Punnam

  11. #11
    Registered User
    Join Date
    05-28-2020
    Location
    Tokyo
    MS-Off Ver
    office pro+ 2016
    Posts
    12

    Re: changing daily value in cell

    This is work sheet with formula. But as I said i probably doing something wrong

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: changing daily value in cell

    You don't need a macro. This will work fine:

    =HLOOKUP(TODAY(),$A$19:$ZZZ$20,2,0)

    Because the formula uss the TODAY() function, it will always find today's date in row 19 and return the corresponding value from row 20.

  13. #13
    Registered User
    Join Date
    05-28-2020
    Location
    Tokyo
    MS-Off Ver
    office pro+ 2016
    Posts
    12

    Re: changing daily value in cell

    Perhaps so. If I knew the answer i wouldn't be asking. I was hoping some IF and Hlookup or something similar could solve the problem. Sorry if my question wasn't properly phrased

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: changing daily value in cell

    Please attach a workbook with the formula I have suggested showing an error.

    Nobody said you should know the answer - where has that come from???

  15. #15
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: changing daily value in cell

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Small change

    Punnam

  16. #16
    Registered User
    Join Date
    05-28-2020
    Location
    Tokyo
    MS-Off Ver
    office pro+ 2016
    Posts
    12

    Re: changing daily value in cell

    Please don"t miss-understand me. I was replying Punnam on Micros. I'm attaching workbook and picture
    Attached Images Attached Images
    Attached Files Attached Files

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: changing daily value in cell

    I don't see why the change is needed. The OP stated at the outset that the value required would be the one underneath today's date - I am still waiting to see a copy of the spreadsheet showing what I suggested doesn't work AND explaining why. So far we've had nothing more than vague statements to go on.

  18. #18
    Registered User
    Join Date
    05-28-2020
    Location
    Tokyo
    MS-Off Ver
    office pro+ 2016
    Posts
    12

    Re: changing daily value in cell

    I have send it. Perhaps I'm sending it wrong

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: changing daily value in cell

    Are you using a Eurpoean locale?

    Try this:

    =HLOOKUP(TODAY();$A$19:$ZZZ$20;2;0)

  20. #20
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: changing daily value in cell

    Hi AliGW,

    I downloaded the file the sheet and somehow he is supposed to use F2 and do enter again to activate the formula.

    Punnam

  21. #21
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: changing daily value in cell

    See the attachment with the formula in place.
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    05-28-2020
    Location
    Tokyo
    MS-Off Ver
    office pro+ 2016
    Posts
    12

    Re: changing daily value in cell

    How ever I copy those formulas in my workbook its give me message "you are trying to type formula"
    Wait maybe numbers are not numbers but text

  23. #23
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: changing daily value in cell

    Quote Originally Posted by Punnam View Post
    Hi AliGW,

    I downloaded the file the sheet and somehow he is supposed to use F2 and do enter again to activate the formula.

    Punnam
    There is nothing in F2 - I don't understand where you have got that from.

  24. #24
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: changing daily value in cell

    Please open the file attached to post #21. The error message suggests that you are using a European locale and need semi-colons instead of commas in your formula.

  25. #25
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: changing daily value in cell

    Ok will check

  26. #26
    Registered User
    Join Date
    05-28-2020
    Location
    Tokyo
    MS-Off Ver
    office pro+ 2016
    Posts
    12

    Re: changing daily value in cell

    Its working. Thank you. My numbers was formatted as text that why that error was popping out.
    At list I can go on with my project.
    Thank you AliGW
    Thank you Punnam
    Very much appretiated

  27. #27
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: changing daily value in cell

    I meant for Oktalv to open my attachment!

  28. #28
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: changing daily value in cell

    My numbers was formatted as text that why that error was popping out.
    No, you wouldn't get this type of error for that - you would get a #VALUE! or #NUM! error. It shouldn't matter here, anyway. However, I am glad it's working.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  29. #29
    Registered User
    Join Date
    05-28-2020
    Location
    Tokyo
    MS-Off Ver
    office pro+ 2016
    Posts
    12

    Re: changing daily value in cell

    Your attachment its working and my work book to. My numbers was written as text that is why formula give error.

  30. #30
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: changing daily value in cell

    No, that's not the reason, as I have explained above - it's not the right type of error.

  31. #31
    Registered User
    Join Date
    05-28-2020
    Location
    Tokyo
    MS-Off Ver
    office pro+ 2016
    Posts
    12

    Re: changing daily value in cell

    Thank You Punnam. Your formula its also working. Now I just must decide which one is better for my project.

+ 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. Opening a sheet which name is changing daily
    By Mac86 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-08-2016, 02:25 AM
  2. Referencing Changing Daily Tabs
    By misterk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-22-2014, 04:11 PM
  3. Replies: 3
    Last Post: 08-20-2014, 12:19 PM
  4. Replies: 4
    Last Post: 03-21-2014, 01:41 PM
  5. how to keep recored daily if a cell value is changing daily
    By siddiq1212 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-09-2013, 02:05 PM
  6. daily changing password
    By patilsshree999 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-28-2012, 12:29 PM
  7. Changing a time series from daily to monthly.
    By RKP in forum Excel General
    Replies: 3
    Last Post: 10-08-2011, 06:27 AM

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