Hi
I have the following formula. The second part should return a value as I separated it out into another cell and it worked fine. So the complete formula is:
And this part I now returns a value:
Please Login or Register to view this content.
Is the formula too long? Is this why it returns an error?Please Login or Register to view this content.
Attach a sample workbook (not image).
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.
Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
An #N/A error suggests that it is not finding a match. Please attach the workbook, as John has suggested.
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.
ok thank you. I have attached sample workbook and have tried to explain what I am looking to do in the workbook.
The formulas appear on the sheet "Today_1FWL" in Col A. I can get the first 2 to work, but the rest just return #N/A when there should be a match?
If anyone can help me out with this, it would be greatly appreciated.
Thanks
In words, please explain what the formula is doing (or meant to be doing). Step-by-step, please.
yes, of course.
1. It looks at the first row of data on the "Today" sheet (which is row 3).
2. It then looks to match the code (col B) and Type (col F) to the summary page.
3. After that, it also looks to match the "TC", Col E on the "Today" sheet.
4. It should then look for the percentage on the summary sheet, and if this is >= 5%, it should return the time value on the "Today" sheet in the corresponding row.
I hope that makes sense but if not, please let me know!
Thanks
Looks clear enough! I'll see if it makes sense when I look at the workbook again.
OK - right. So, Excel is not finding a match, which means one of two things:
1. The formula is not doing what you think it should be doing.
2. There is no match.
Can you talk me through, step-by-step, what Excel should be doing in that first row with the #N/A error? Using specific cell references, please.
The Code "DOO" is not present in Summary column B : hence #N/A error when MATCH is resolved.
You need to add an error trap if this occurs
Last edited by JohnTopley; 07-27-2017 at 06:06 AM.
thank you, yes I can for sure.
I don't know if this is the right order (perhaps that is part of the problem) but it should do the following:
1. Look for the code shown in the "Today" sheet in cell B5 (which is "DOO") in the summary page in the appropriate column.
2. The appropriate column is based on the "TC" type. So if in Col E on the "Today" sheet in the corresponding row (this would be cell E5) it should look at the "TC" type which is "D5" and this group of data starts on the summary sheet from col M.
3. Therefore, it should look for the code (again this is "DOO") in Col N of the summary sheet.
4. Once it has that, it should look for the "Type" shown in the "Today" sheet in the corresponding row (this would be cell F5) in the summary sheet in Col O.
5. There should be a match to this, (which is row 6 on the summary sheet, starting from col M).
6. Finally, if the value in Col P is >=5%, it should take the corresponding value in the "Today" sheet, which in this case is the time in cell D5, and place it in the "Today_1FWL" sheet in cell A5 (where the current #N/A error is).
I hope that makes sense but if not please ask!
thank you John. Hopefully I have explained above but in the case of that record, it shouldn't look at Col B on the summary sheet, but rather Col N.
The problem with the current formula is that if the first part returns an error, you get an error back rather than processing the following parts. I think you might use
=IF(LOOKUP(1E+100,CHOOSE({1,2,3,4},LOOKUP(2,1/((Today!$B3=Summary!$B$4:$B$1000)*(Today!$F3=Summary!$C$4:$C$1000)*OR(Today!$E3={"G","G2","G3","G4","D","D4"})),Summary!$D$4:$D$1000),LOOKUP(2,1/((Today!$B3=Summary!$N$4:$N$1000)*(Today!$F3=Summary!$O$4:$O$1000)*OR(Today!$E3={"D5","S5"})),Summary!$P$4:$P$1000),LOOKUP(2,1/((Today!$B3=Summary!$Z$4:$Z$1000)*(Today!$F3=Summary!$AA$4:$AA$1000)*OR(Today!$E3={"S","Sl6","Sl7","St6"})),Summary!$AB$4:$AB$1000),LOOKUP(2,1/((Today!$B3=Summary!$AL4:$AL$1000)*(Today!$F3=Summary!$AM$4:$AM$1000)*OR(Today!$E3={"H","H8","H9","H10"})),Summary!$AN$4:$AN$1000)))>0.05,Today!$D3,"")
although you currently have "C 6" rather than "C6" in F6 on the Today sheet, and I do not understand why you expect 15:00 for row 7?
Don
Please remember to mark your thread 'Solved' when appropriate.
IF(INDEX(Summary!$D$4:$D$1000,MATCH(1,(Today!$B3=Summary!$B$4:$B$1000)*
Second: code "D5" (in Today!E5) is not in the list you test ..
((Today!$E5="G")+(Today!$E5="G2")+(Today!$E5="G3")+(Today!$E5="G4")+(Today!$E5="D")+(Today!$E5="D4"))
I think you co relation with "Today" sheet only.
Why not mentioned criteria in "Today_1FWL" sheet. for Code In "B3" is ASC, For type In "C3" is C3 & for TC in "D3" is G3
In "A5"Formula:
Please Login or Register to view this content.
If above logic not corrected then you can change data structure in your summary sheet. Instead of horizontal data you do maintain vertical data structure & add one more "TC" column at the end column i.e. in "K" column.
In this case you long formula reduce.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
xlnitwit - Oh I see, thank you. That makes sense.
I have changed "C 6" in F6 on today sheet to C6.
You are also correct about expectingv 15:00 in row 7. In this case the % is below 5% so it wouldn't return a value.
John, I just noticed that, thank you. I will try to include this in the above formula xlnitwit has provided.
xlnitwit - I used this formula and it appears to be doing what it should, thank you. I will do some more testing to see.Originally Posted by xlnitwit
One other question - if none of the data is found, where can I add this, e.g. iferror, "No data" or something like that?
This would be in relation to your earlier pick up about row 7 where no value would be returned, either because the codes and types aren't found, or the % is less than the required value.
Like this:
=IFERROR(your_very_long_formula,"No data - nada - nuffink")
perfect, thanks!
What Ali said.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks