I am using small and index formula. Sample file is attached. In sheet 1 the results are not coming, in sheet2 only 3 rows appear and after 7 again the 1st row reappears. I get this mistake often. I would be thankful to you all if error is corrected and the mistake and logic behind the error.
Try
Formula:
Please Login or Register to view this content.
=IFERROR(INDEX('Sales Register'!$A$1:$V$424,SMALL(IF('Sales Register'!$C$1:$C$424=Sheet1!$B$17,ROW('Sales Register'!$C$1:$C$424),""),'Sales Register'!A2-1),COLUMN('Sales Register'!V1)),"")
Hi -
I think the formulas are working correctly. Your sample data is at least part of the problem. For example, on Sheet2, cell A24, where nothing shows up, the formula is taking the smallest of either the ROW where it finds ES/16-17/01878, which is the first row it occurs on the Sales Register Sheet at C38 which is 38 OR the contents of cell 'Sales Register'!$A31-2. The contents of 'Sales Register'!$A31 is 2. 2-2=0. So the smaller of 38 or 0 is 0. By definition, there is no row 0 so you get an error. Since your formula has an IFERROR function, it returns a "". Which is why nothing shows up there.
Another example: Sheet2 Cell A29: The result that shows up is 1. This is the same problem as above. We are comparing Row 38 to the contents of cell 'Sales Register'!$A36-2. That cell contains the number 3. 3-2=1. The smaller of 38 and 1 is 1. Therefore you are getting what is stored in Row 1.
I'm not entirely sure what you are doing with these formulas, so I can't really offer advice on the overall formulas. However, I think your sample data is the first part of the problem.
Hope this helps.
____________________________________________
If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved
If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.
Very much thanks for your effort and reply.Infact sheet sales register column A is helper column. I can remove it. Can you suggest me how to rectify and improve it.
I would value your effort.
Hi -
You will have to explain to me what you are trying to do with this spreadsheet.
sorry in the middle i submiited thread. In column c heading voucher no. I want the details of (sheet 1 or2 B17) it in sheet 1 or 2 (from a21) i wanted result from L column. same from f21 results from v column. This formula i have taken from this forum and in some of workshhet it is working fine.
Try in Sheet2 F21 and copy down
Formula:Please Login or Register to view this content.
In Sheet1 F21 I changed the formula toIt's still array entered.Formula:Please Login or Register to view this content.
One of the changes I made was this 'Sales Register'!C1:$C$424 to this 'Sales Register'!$C$1:$C$424
Another is that to change the K argument of SMALL to ROWS($21:21). It's simpler. Note: the formula starts in row 21.
Another is to use the MATCH function to return the column argument of INDEX. Note: the spelling of "Acutal Quantity" must exactly match the header spelling ... with no leading or trailing spaces.
Dave
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks