1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy Formula to Right

Discussion in 'Ask an Excel Question' started by tiong999, May 17, 2017.

  1. tiong999

    tiong999 New Member

    Messages:
    28
    Hello All,

    I found myself frustrated with something lack with my excell skill...and hopefully i have someone i could ask here.

    By simply put, i Have 2 sheet :
    Master Table
    Result

    in The Master Table, there will be i input price, and like to have result as i formulated and u can easily see what result i want.
    But, formula i put to next column now in Result sheet is manually...and that will put me so much effort and risky of human error.

    Please spare ur time a while...i believe this should be easy for excel master here :)

    Regards,
    TIONG

    Attached Files:

  2. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,247
    Try........

    In J5, formula copy across yo O5 and all copy down :

    =IFERROR(TEXT(1/(1/INDEX('MASTER TABLE'!$U$4:$AE$100,MATCH(1,INDEX(('MASTER TABLE'!$C$4:$C$100=$C4)*('MASTER TABLE'!$D$4:$D$100=$D4),0),0),MATCH(E$3,'MASTER TABLE'!$U$3:$AE$3,0))),"0.000"),IFERROR(1/(1/INDEX('MASTER TABLE'!$E$4:$O$100,MATCH(1,INDEX(('MASTER TABLE'!$C$4:$C$100=$C4)*('MASTER TABLE'!$D$4:$D$100=$D4),0),0),MATCH(E$3,'MASTER TABLE'!$E$3:$O$3,0)))*INDEX('MASTER TABLE'!$U$1:$AE$1,MATCH(E$3,'MASTER TABLE'!$U$3:$AE$3,0)),""))

    Regards
    Bosco

    Attached Files:

    Thomas Kuriakose likes this.
  3. tiong999

    tiong999 New Member

    Messages:
    28
    Hello Bosco....i'm glad for your assistance again.

    I made a slight adjustment to meet what i need...it worked for a case, but how to formulate if have so many case that i want it to be automated?

    Please review my adjustment on attach file.

    Regards,
    TIONG

    Attached Files:

  4. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,247
    Hi Tiong,

    1] Here's is a long and complicated formula.

    In E4 formula copy across to AA4 and all copy down :

    =IFERROR(TEXT(1/(1/INDEX(('MASTER TABLE'!$T$4:$AE$98,'MASTER TABLE'!$AF$4:$AQ$98),MATCH(1,INDEX(('MASTER TABLE'!$C$4:$C$98=$C4)*('MASTER TABLE'!$D$4:$D$98=$D4),0),0),MATCH(E$3,'MASTER TABLE'!$T$3:$AE$3,0),MATCH(LOOKUP(2,1/($E$2:E$2<>""),$E$2:E$2),{"SHOP A","SHOP B"},0))),"0.000"),IFERROR(1/(1/INDEX('MASTER TABLE'!$E$4:$P$98,MATCH(1,INDEX(('MASTER TABLE'!$C$4:$C$98=$C4)*('MASTER TABLE'!$D$4:$D$98=$D4),0),0),MATCH(E$3,'MASTER TABLE'!$E$3:$P$3,0)))*INDEX('MASTER TABLE'!$T$1:$AQ$1,MATCH(1,INDEX(('MASTER TABLE'!$T$3:$AQ$3=E$3)*(LOOKUP(COLUMN('MASTER TABLE'!$T$2:$AQ$2),COLUMN('MASTER TABLE'!$T$2:$AQ$2)/('MASTER TABLE'!$T$2:$AQ$2>0),'MASTER TABLE'!$T$2:$AQ$2)=LOOKUP(2,1/($E$2:E$2<>""),$E$2:E$2)),0),0)),""))

    2] Due to your merge cell and separates table design, which increase the unnecessary difficulty for the formula design in the Lookup processing.

    3] Although the formula can work, it is not recommended, the formula show inefficient and hard to maintain.

    4] Try to Google in searching from web : "How to Create a Database from an Excel Spreadsheet".

    Regards
    Bosco

    Attached Files:

  5. tiong999

    tiong999 New Member

    Messages:
    28
    Well.....Im happy with yesterday formula, no need looking slow formula.

    Thanks my friend ;)

    Regards,
    TIONG

Share This Page