LinkBack URL
About LinkBacks
hi. i have attached my sheet. if a word in the range a20:a100 is in cell a5, then i want a formula that will return 100 in cell b20. if the word is in cell a8, then i want 103 returned in b23.
Hope you guys can sort me out for this one!!
thanks
hi. i have attached my sheet. if a word in the range a20:a100 is in cell a5, then i want a formula that will return 100 in cell b20. if the word is in cell a8, then i want 103 returned in b23.
Hope you guys can sort me out for this one!!
thanks
I don't follow your logic but this might get you started:
Formula:
Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
If I understand you correctly, if the value in A20 matches the value in A5 then return 100 and if the match is somewhere else in A5:A15 return 103. If no match, return nothing.
Formula:Please Login or Register to view this content.
Call Me Vic the who Banditry Greenlaw Red Unico Poolstock Edge Red Inca Rough Courte Centre Haafhd Lady Sugarfoot Call Me Vic Greenlaw Still Believing Belmount Howaboutnow Red Anchor Storm Of Swords My Brother Sylvest Easily Pleased Lily Little Legs Speedy Bruere Venetian Lad Ilewin Geez Welluptoscratch Renfrew
Last edited by newdoverman; 05-10-2015 at 10:39 AM.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Try this in b20
Copied down
Please Login or Register to view this content.
If it is not ok, please explain more so that you may get answer earlier
Mark the thread as solved if you are satisfied with the answer.
In your first post under the thread tools.
Mahju
Aengus, you've got us a bit confused, I think... Certainly I am. Can you explain the logic that lies behind what you want?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
thtas not what im looking for guys. here is the formula i have at the moment and it works perfect. but its so long and i need to repeat in the next cell down for about 50 cells. so im looking for an easier way of writing this formula
=IF(OR(A$62=A5,A$63=A5,A$64=A5,A$65=A5,A$66=A5,A$67=A5,A$68=A5,A$69=A5,A$70=A5,A$71=A5,A$72=A5,A$73=A5,A$74=A5,A$75=A5,A$76=A5,A$77=A5,A$78=A5,A$79=A5,A$80=A5,A$81=A5,A$82=A5,A$83=A5,A$84=A5,A$85=A5,A$86=A5,A$87=A5,A$88=A5,A$89=A5,A$90=A5,A$91=A5,A$92=A5,A$93=A5,A$94=A5,A$95=A5,A$96=A5,A$97=A5,A$98=A5,A$99=A5,A$100=A5,A$101=A5,A$102=A5,A$103=A5,A$104=A5,A$105=A5,A$106=A5,A$107=A5,A$108=A5,A$109=A5,A$110=A5,A$111=A5,A$112=A5,A$113=A5,A$114=A5,A$115=A5),100,0)
so the next cell will be,
=IF(OR(A$62=A6,A$63=A6,A$64=A6,A$65=A6,A$66=A6,A$67=A6,A$68=A6,A$69=A6,A$70=A6,A$71=A6,A$72=A6,A$73=A6,A$74=A6,A$75=A6,A$76=A6,A$77=A6,A$78=A6,A$79=A6,A$80=A6,A$81=A6,A$82=A6,A$83=A6,A$84=A6,A$85=A6,A$86=A6,A$87=A6,A$88=A6,A$89=A6,A$90=A6,A$91=A6,A$92=A6,A$93=A6,A$94=A6,A$95=A6,A$96=A6,A$97=A6,A$98=A6,A$99=A6,A$100=A6,A$101=A6,A$102=A6,A$103=A6,A$104=A6,A$105=A6,A$106=A6,A$107=A6,A$108=A6,A$109=A6,A$110=A6,A$111=A6,A$112=A6,A$113=A6,A$114=A6,A$115=A6),101,0)
Phuq me!!! Can you try to explain again what it's doing. It might help if we could see your real sheet to see why you want something that, on first view, looks very odd indeed.
haha. its a betting sheet. so i get a list of horses in the morning and paste them into A62. the values in a5:A40 update automatically for every race.
so when a name in A62:a115
This is even more confusing than before. The first data set is too small and too few matches to make sense of this.
Please post your actual workbook because just having the two formulae doesn't make a whole lot of sense the way it is. We don't know where or what you are doing with these formulae. Are you filling them down, are you re-writing formulae for each row or what?
In the mean time, try this in B5 and fill down:
Formula:Please Login or Register to view this content.
Last edited by newdoverman; 05-10-2015 at 02:49 PM.
thanks. thats it!!
Thank you for the feedback.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks