Dear all
I would like to extract some data and develop a formula in DNO2 cell to drag left and right to extract the value. But it does not provide any value.
Please provide your valuable feedback.
Regards
Shams
Dear all
I would like to extract some data and develop a formula in DNO2 cell to drag left and right to extract the value. But it does not provide any value.
Please provide your valuable feedback.
Regards
Shams
Hi
Please help me. I desperately need your help.
Regards
Shams
Great timing on the "bump" !
I'll take a look, but no guarantees
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
it seems your announcement dates (column B) are not dates, so no possibility of a match
Hi Dreadwolf
Thank you for your email. Waiting for your response.
Regards
Shams
see post #4
Hello
How to convert them into date formate. Should it be worked =IFERROR(INDIRECT(ADDRESS((MATCH($B2,$D$1:$DEG$1,0)+DNO$2),3,1,1)),"")
Hi I have developed this formula for DNP2 cell. =IFERROR(INDIRECT(ADDRESS($DEH2,(MATCH($B2,$1:$1,0)+DNP$1),1,1)),""). But still it does not retrieve the value. DEH2 is the numeric value 2. (New column of value)
Dear all
I think the main problem is B2 date cell which is different format than the actual date in $1:$! row.
Thanks
Shams
Hi All
=IFERROR(INDIRECT(ADDRESS($DEH2,(MATCH($B2,$1:$1,0)+DNP$1),1,1)),""). This formula works but the only problem is B2 date format. If I changed the date in B2 cell manually, it works. Please advice me how to convert the B column date into standard date format.
Regards
Shams
I think you are missing the point, the numbers in column B are translating intonegative dates,which excel does not handle well, the third date is outside of the allowable range for dates, the other 2 look like a concatenation of cells to reach what looks like a date, then used a value to get a number, but is not a valid date, not sure how these numbers were derived, but they are not valid dates, so your match can never give a value... sorry
There is no "esy" solution, this looks like an amalgram of formulus came up with the dates..... in first 2 you could use text functions, but the third looks like it was derived differently, so I have no "simple" way to correct them...
Assuming all the data is derived the same way, this MIGHT work :
in C2:
Formula:Please Login or Register to view this content.
drag down
EDIT-
mistake -5 instead of -6
no guarantees on this, but seems to work for the sample
Last edited by dredwolf; 11-19-2013 at 01:09 AM.
if you show me the formula that gives you the non-date number, we could probably give you the formula that would make it an actual date, it seems redundant to me to turn a date into a string, then turn it back into a number to use the date..., just my opinion though
hI dREW tHIS FORMULA IN hrr2 DOES NOT PICK THE RIGHT VALUE YET
Last edited by mahershams; 11-19-2013 at 02:03 AM. Reason: new file
@ mahershams, ah, so column B has "magically" been fixed now...interesting ...
Hi Dredwolf
You are great. You have done a great job to convert this impossible to possible. I am very happy. But I do not know why my formula does not capture the right figure yet.
I WILL ATTACH THE FILE FOR YOUR LOOK.
Hi Drew
This formula in HRR2 cell does not pick the right value yet. Would you be able to have a look in HRR2 cell where it goes wrong now.
Regards
Shams
Hi Drew still the formula in HRR2 cell does not work. It retrieve some wrong values. Please have a look if possible.
THANK YOU VERY MUCH FOR YOUR SUPPORT AND TIME.
I don't really need the file, just need to se how the value in column B is derived (ie the formula for column B0, if we can get that to be an actual date, then the rest is pretty simple, or use the C column conversion I already supplied and use that, I just think it's wasting time, memory, storage space to have re-calculate the date when it already exists...
I use the new conversion date in my new formula and it captures some value. But the value was not correct. The value should be 0.052632 in DOT2 should be in HRS2. But HRS2 shows 0.0190 this value. How is this happening.
Also it seems you are answering another poster, yet i see no posts from a drew, maybe PM's?
I feel like I'm in the middle of 3 different conversations here...
Hi Drew
I also lost where I am posting what. However good thing is that I have solved the problem. It now works to retrieve the data
=IFERROR(INDIRECT(ADDRESS($DEF3,(MATCH($B3,$1:$1,0)+DMK$1),1,1)),"")
I would like to tell you my deepest thanks for your support.
Good day Mahersham
Moderators Note:
Are you pertaining to the same personandHi Drew.Hi Dredwolf
If not please refrain from discussing via PM on this and other thread that will be created so not to create confusions on the part of those who are trying to solve your problem and those who are following the thread.
Thanks.
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
Umm...okay, your welcome !
Not really sure what happened, but if your happy, it is all good !
@ vlady, thank you!
still not sure if it was me or another member..lol
EDIT-
The reason for my confusion is that I know we have a member named Drew who is very knowledgeable about Excel, so it throws me off if your calling me Drew...
Last edited by dredwolf; 11-19-2013 at 02:35 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks