+ Reply to Thread
Results 1 to 3 of 3

Trying to replicate a formula

  1. #1
    Registered User
    Join Date
    04-02-2014
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    49

    Trying to replicate a formula

    I wrote this formula for a spreadsheet years ago, its an order sheet for a restaurant, when the adjacent cell next to an item in the source sheet is marked with a number it appears on a different sheet, a neat list with no spaces in between. This list was to be emailed to the person buying the supplies. It worked great until someone changed the source and moved tables around. I don't use it anymore so I didn't think about it until I wanted to replicate the formula in different spreadsheet to do basically the same thing, but I cant remember exactly what I did. I've been trying to break it down and change the info but I just cant make it work in a new sheet. Can anyone explain this formula to me? Does this question even make sense?


    These are the first 2 cells, the only difference in every formula is the very last number which is the destination for the source:

    =IFERROR(INDEX(KITCHEN!B:B,SMALL(IF(KITCHEN!$A$4:$A$58<>0,ROW(KITCHEN!$A$4:$A$58)),ROWS($C$1:D1))),"")

    =IFERROR(INDEX(KITCHEN!B:B,SMALL(IF(KITCHEN!$A$4:$A$58<>0,ROW(KITCHEN!$A$4:$A$58)),ROWS($C$1:D2))),"")

  2. #2
    Registered User
    Join Date
    04-02-2014
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    49

    Re: Trying to replicate a formula

    I forgot, those are column B, column A has this, which tells the amount of items:

    =IFERROR(INDEX(KITCHEN!A:A,SMALL(IF(KITCHEN!$A$4:$A$58<>0,ROW(KITCHEN!$A$4:$A$58)),ROWS($C$1:C1))),"")
    =IFERROR(INDEX(KITCHEN!A:A,SMALL(IF(KITCHEN!$A$4:$A$58<>0,ROW(KITCHEN!$A$4:$A$58)),ROWS($C$1:C2))),"")

    They still operate, I cant tell if they changed when someone moved the tables around from the source sheet. I really just wat to understand what this formula is and how I can replicate it. I cant figure out a different way to accomplish what I want, I did that time years ago but now I cant'.

  3. #3
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    7,603

    Re: Trying to replicate a formula

    It appears that the formulas are displaying the smallest non zero value in the range A4:A58 on the kitchen sheet, then the next smallest non zero value and so on. These are all array entered formulas, so you would need to activate them by putting the cell in edit mode (press the F2 key) then simultaneously press the Ctrl, Shift and Enter keys before copying the formula on to other cells. If that does not help resolve the issue I would suggest uploading a sample of the workbook. To upload a sample workbook (not a picture or pasted copy) click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1