Hi!
Could someone maybe help me to create an IF-formula for this?
Automate the F-column. If D15 says aapp, then F15 should say Dove, but if D15 says bbeeUU, then F15 should say Sobel, Brunörad (Heterozygot för Umborous) instead, and so on. It needs to be case-sensitive. And if D15 is blank, then F15 should also be blank.
So depending on what code there is in the cell to the left of it, the cell should say what color that genotype stands for.
Thank you!
//Tina, Sweden
Color coding help.xlsx
i think you need something like
=INDEX($J$5:$J$100,MATCH(TRUE,INDEX(EXACT($D$5:$D$100,D5),0),0))
BUT - all you are doing is making whatever is in Row (D) = what is on the same row in (J)
so i think i am missing something here
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
With VLOOKUP
Vlookup wont work because it needs to be case sensitive.Originally Posted by cbatrody
From your screenshot, I can see u just need to remove +'Färger och teckningar'! placed at the end of the formula, before the semi-colon as highlighted in the screenshot I posted. Try removing it, I think with that the formula is not evaluated.
Last edited by Saarang84; 05-06-2014 at 03:56 AM.
If my assistance has helped, there is a reputation icon * on the left hand corner below the post - you can show your appreciation to the user who has helped in resolving your requirement.
If your requirement has been solved please mark your thread as Solved.
In the menu bar above the very first post, select Thread Tools, then select "Mark this thread as Solved".
Kindly use [FORMULA] or [CODE] tags when posting your code.
Regards,
Sarang
Another formula
=INDEX($J$5:$J$98,SUMPRODUCT(EXACT(D5,$D$5:$D$98)*(ROW($D$5:$D$98)-4)))
However, eeppSgsgUU gives an error because you have it twice (unless Excel misses something in the translation)
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
Hi Tina,
Think I've located the issue. In your screenshot, towards the end of the formula, instead of the comma symbol "," there is reference to some other sheet.
Refer highlighted area in the enclosed screenshot.
Thank you so much, all of you!
I tried cbatrody's forumla first and it works fine and it's an easy, simple formula.It's just not case-sensitive. Is that easily added?
I would g with ChemistB's solution as it will show if you have duplicates in your data.
Oh, ok. I tried it, but I can't seem to get it to work over different sheets (the color key is on another sheet in my actual document). Do you think you could add the formula to AB8 in this document?
Color coding New help.xlsx
Check whether you need to use TRIM function to remove leading and trailing spaces in the strings in column AD in the sheet before you use the INDEX formula.
If the issue still persists, please post sample workbooks (of both English and Swedish versions), will look into it
In AB8
=INDEX('Färger och teckningar'!$C$2:$C$37, SUMPRODUCT(EXACT('Färger och teckningar'!$B$2:$B$37, Korsningshjälpen!$AD8)* (ROW('Färger och teckningar'!$B$2:$B$37)-1)))
See attachment
Thank you! Now it's getting somewhere!
Is it possible to also add that if it can't find the code in the table, then it should leave the cell blank?
And could you maybe add that it should not only look in those very cells of the table, but the whole columns where the table is? That way I can always add more colors to the table and expand it without meddling with the formula.
Thank you so much for your help, once again!
Have you tried to delete existing formulas and replace them once again in the column AB in your main sheet?
In the screenshot, I find the cells AB8 and AB9 blank and I think you've dragged and extended the formula till AB23.
Delete and replace the existing formulas in column AB with the below formulas. I've highlighted the places where you need to use the TRIM function (RENSA in Swedish) in blue
English :
Formula:
=IFERROR(IF(SUMPRODUCT(EXACT('Färger och teckningar'!$B:$B,TRIM(Korsningshjälpen!$AD8))*(ROW('Färger och teckningar'!$B:$B)-1))=0,"",INDEX('Färger och teckningar'!$C:$C, SUMPRODUCT(EXACT('Färger och teckningar'!$B:$B,TRIM(Korsningshjälpen!$AD8))*(ROW('Färger och teckningar'!$B:$B)-1))+1)),"")
Swedish:
Formula:=OMFEL(OM(PRODUKTSUMMA(EXAKT('Färger och teckningar'!$B:$B;RENSA(Korsningshjälpen!$AD8))*(RAD('Färger och teckningar'!$B:$B)-1))=0;"";INDEX('Färger och teckningar'!$C:$C, PRODUKTSUMMA(EXAKT('Färger och teckningar'!$B:$B,RENSA(Korsningshjälpen!$AD8))*(RAD('Färger och teckningar'!$B:$B)-1))+1));"")
Let me know what happens
Last edited by Saarang84; 05-07-2014 at 07:00 AM.
in AB8 will do what you want.Formula:=IF(SUMPRODUCT(EXACT('Färger och teckningar'!$B:$B,Korsningshjälpen!$AD8)*(ROW('Färger och teckningar'!$B:$B)-1))=0,"",INDEX('Färger och teckningar'!$C:$C, SUMPRODUCT(EXACT('Färger och teckningar'!$B:$B,Korsningshjälpen!$AD8)*(ROW('Färger och teckningar'!$B:$B)-1))+1))
Thank you! :D
The only thing left now it that it's showing #REFERENCE! if the cell in AD is blank (when it's got nothing to read from). Could you maybe add that if the cell it is reading from is blank, the cell in AB should also be blank?
Use gak67's formula with an IFERROR instead.
Formula:=IFERROR(IF(SUMPRODUCT(EXACT('Färger och teckningar'!$B:$B,Korsningshjälpen!$AD8)*(ROW('Färger och teckningar'!$B:$B)-1))=0,"",INDEX('Färger och teckningar'!$C:$C, SUMPRODUCT(EXACT('Färger och teckningar'!$B:$B,Korsningshjälpen!$AD8)*(ROW('Färger och teckningar'!$B:$B)-1))+1)),"")
Thank you!
I think it works!
I am just wondering about the cell for Fenotype Uu, though. It gives me "0". Do you know why that is?
Namnlös.jpg
Don't drag down after you paste formulas into column AB. Use Paste-->Paste Special-->Formulas to paste it.
Also, before you do the above copy & paste thing, just delete and replace the contents of column AD.
One thing I want to know though - does column AD contain any formulas?
It doesn't do this in the workbook I gave you, but it does in my real workbook (the one in the picture).
I cant think of a reason why, but check your ranges and make sure it covers Uu.
Hi Tina,
I checked your sample workbook you posted in this thread.
The formula in column AD uses the sheet Uträkningar, however the corresponding formula placed in column AB which you got from this forum uses the Färger och teckningar sheet, which does not contain the output for Fenotype word "Uu" from Uträkningar sheet. I think due to this reason, the formula in column AB evaluates to 0 (zero).
Please do a manual check for the Fenotype word "Uu" in the Färger och teckningar sheet and let me know.
Last edited by Saarang84; 05-09-2014 at 06:32 AM.
Try evaluating the formula and let me know what you get.
By clicking on the fx button in the formula bar, you'll get a window, where you can evaluate the formula.
I can't see what it is. What's weird to me, though, is that when I click the different parts of the formula, it doesn't show me any marked areas in "Färger och teckningar", but maybe it's supposed to be like that with this formula. This is beyond my knowledge... :S
Namnlös.jpg
Hi Tina,
Always happy to help.. You are welcome.
When translating the formula into Swedish I have to replace all commas with ;
I think it's just how the Swedish version of Excel works. So if you look at the rest of the formula, there's ; all over it instead of commas. Or do you see anything else in the highlighted area that's not supposed to be there?
It's weird. Now that I went back to the formula the +'Färger och teckningar'! isn't there. But the problem still is.
Namnlös.jpg
It's weird. I put all the data that you would need into a new workbook and THERE it works! :S Is there another formula I could use in my big document? Or how do I use the TRIM function?
All I need is basically: IF the cell to the right of the formula is exactly the same as anyone in the B column of the sheet Färger och teckningar, the cell should = the cell to the right of the cell in column B.
How do I use the trim function? I added the formula to AB8 and dragged down, but it still won't work. The weird thing is that it worked when I put some of the data into a small workbook.
Are you sure there is no other IF-formula that would work, like in the example I posted two posts up?
(You have no idea how much I appreciate your help! Thank you sooo much for putting down all this time on this issue!). :D
Forgot the pic as usual. :P
Crap.png
Anyone who knows what might be the problem?
Yes, AD contains this formula: =Uträkningar!BC4 (Cell from another sheet, and this cell does also contains formula).
I tried to delete and copy/paste the way you described above, but Uu and UU still gives me 0 instead of Umborous. :/ It's weird, since everything else gives me the correct thing. Does the combination Uu or UU itself mean anything to Excel? o.O
AD in Sheet 1 (Korsningshjälpen) uses Uträkningar, but apart from that, I think that the formula itself in AB reads only from Färger och teckningar.
THOUGH, I tried to change the position of the cells that said UU and Uu in Färger och teckningar and that solved it! I have no idea why it won't read it correctly when it's located further down in the column, but I added two new rows at the top of the column and wrote UU and Uu (and their corresponding colors) there and it worked! Now it's showing the right thing! Weird, but great!
Thank you SOOOOO much for putting so much time into all my questions and concerns! I am VERY thankful! Thank you so much, to all of you who have posted in this thread!
Sincerely,
Tina, Sweden
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks