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.

Payment workbook Customization.

Discussion in 'Ask an Excel Question' started by Shabbo, Oct 22, 2017.

  1. Shabbo

    Shabbo Member

    Messages:
    200
    Dear sir,

    Reference to attached sheet, I wanted to lookup the vendor name from Hire and Hire2 sheet, Column O contains Voucher number Column C should the vendor name, column C should come from Hire and Hire2 sheet on the basis of Voucher name.

    But if the Vendor name from Hire2 sheet then account name should be fuel expenses.

    Attached Files:

  2. AlanSidman

    AlanSidman Active Member

    Messages:
    302
    Maybe: =IFERROR(VLOOKUP(O2,Hire!$C$3:$D$59,2,FALSE),"Fuel Expenses")
  3. Shabbo

    Shabbo Member

    Messages:
    200
    Dear Sir,
    It should be linked with Hire2 Sheet because there may change in the list of Vehicle.
  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,615
    Hi ,

    Your requirement is not clear.

    1. Which column is the account name ?

    2. In which cell / column is the formula to be entered ?

    3. What should the formula return if the lookup value is found in the tab named Hire ?

    4. What should the formula return if the lookup value is found in the tab named Hire2 ?

    Narayan
  5. AlanSidman

    AlanSidman Active Member

    Messages:
    302
    what does this mean? I don't understand what you are saying. Please clarify.
  6. Shabbo

    Shabbo Member

    Messages:
    200
    Which column is the account name ?1) account name is in Payment sheet Column c,
    2) formula to be entered in C:C
    3) if the lookup value found in tab named hire then account name should be vendor name.
    4) if the lookup value found in tab named hire2 then account name should be fuel expenses.
    Vlookup will not work because if I missed an entry to make in Hire list then also it show as fuel expenses but fuel expenses should be shown only for hire2 vehicles. for example if I missed a entry/Voucher number : V171803042 to make entry in hire list then also vlookup will show as fuel expenses.
  7. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,615
    Hi ,

    It is still not clear.

    We will first lookup the value in column O , in the column C in the Hire tab to see if the voucher number exists in this tab.

    If it exists , the retrieved vendor name can certainly be displayed.

    What if the voucher number does not exist in the Hire tab ? What is to be done ?

    If you say that in this case , the lookup should be done in column B in the Hire2 tab , then how will we ever find out whether an entry was missed in the Hire tab ?

    Narayan
  8. Shabbo

    Shabbo Member

    Messages:
    200
    Dear Sir,


    One suggestion: can you please write formula to see if the Vehicle number from hire2 sheet then fuel expenses else vendor name from hire sheet.
  9. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,615
    Hi ,

    See if this is OK.

    Narayan

    Attached Files:

    AlanSidman and Shabbo like this.
  10. Shabbo

    Shabbo Member

    Messages:
    200
    Dear Sir,
    Formula works fine thanks for the same, just one more thing to be added.

    If There is a voucher number in O column and Column C is Vendor then J column should be billing- N column P3- column O Voucher no. and Column P date

    If There is a voucher number in O column and If Column C is then Fuel expenses J column should be Tripexp - K column P2- column L Voucher no. and Column L date.

    But if there is no Voucher number then Billing and Tripexp both should be blank.

    I have attached my excel for your ready reference.

    Attached Files:

    Last edited: Oct 25, 2017

Share This Page