I have cell E4 that must change value on daily bases according values that will be entered in cells C20 to R20
I have cell E4 that must change value on daily bases according values that will be entered in cells C20 to R20
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.
cp[ay this in E4 and drag it downFormula:Please Login or Register to view this content.
Punnam
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.
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!
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
Please see post #5 - there is no point in repeating yourself.
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
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
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
This is work sheet with formula. But as I said i probably doing something wrong
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.
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
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???
Formula:Please Login or Register to view this content.
Small change
Punnam
Please don"t miss-understand me. I was replying Punnam on Micros. I'm attaching workbook and picture
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.
I have send it. Perhaps I'm sending it wrong
Are you using a Eurpoean locale?
Try this:
=HLOOKUP(TODAY();$A$19:$ZZZ$20;2;0)
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
See the attachment with the formula in place.
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
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.
Ok will check
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
I meant for Oktalv to open my attachment!
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.My numbers was formatted as text that why that error was popping out.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Your attachment its working and my work book to. My numbers was written as text that is why formula give error.
No, that's not the reason, as I have explained above - it's not the right type of error.
Thank You Punnam. Your formula its also working. Now I just must decide which one is better for my project.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks