+ Reply to Thread
Results 1 to 13 of 13

Formula To Define A Collumn Name Using Vlookup

  1. #1
    Registered User
    Join Date
    03-11-2014
    Location
    Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Question Formula To Define A Collumn Name Using Vlookup

    Hiya..

    I have a dynamic collumn generated by Reporting Service. For example if collumn "Working Hours" is in collumn F, it is possible that working hours is in collumn G if I download it tomorrow.

    Is there any formula so I can DEFINE NAME with VLOOKUP Formula?
    I have a table of collumn list and NAME LIST.

    Thanks & Regards,
    ASW

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,780

    Re: Formula To Define A Collumn Name Using Vlookup

    Use Match() function to identify the placement of the text "Working Hours"

    =MATCH("Working Hours",1:1,0)


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    03-11-2014
    Location
    Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Formula To Define A Collumn Name Using Vlookup

    Okay I get the collumn number.
    Is it any idea on how can I define name for the numbers?

    For example, Working Time is in collumn 6. Then I would like to define it as _WT in define names.

    Is there any way to do this?

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,780

    Re: Formula To Define A Collumn Name Using Vlookup

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. 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 demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  5. #5
    Registered User
    Join Date
    03-11-2014
    Location
    Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Formula To Define A Collumn Name Using Vlookup

    Sample.xlsx

    Here you go..

  6. #6
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,780

    Re: Formula To Define A Collumn Name Using Vlookup

    Your excel skill seems to be an intermediate level so I hope you can easily incorporate the below formula in your fie.


    =INDEX($1:$1048576,,MATCH("Problem Mesin",$5:$5,0))


    Change the lookup value for each named range.

  7. #7
    Registered User
    Join Date
    03-11-2014
    Location
    Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Formula To Define A Collumn Name Using Vlookup

    That's nice..
    Now I want to do SUMIFS with the collumn.

    How can I do it? I tried but the results is #VALUE

    Thanks

  8. #8
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,780

    Re: Formula To Define A Collumn Name Using Vlookup

    Could you please let me know the sumifs conditions?

  9. #9
    Registered User
    Join Date
    03-11-2014
    Location
    Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Formula To Define A Collumn Name Using Vlookup

    I would like to calculate Breakdown Machine Collumn (I name it _BreakdownMC) with the condition of Line is "PLI104"

    Normal SUMIFS:
    =Sumifs(_BreakdownMC,$B:$B;"PLI104)

    and the result is #Value

  10. #10
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,780

    Re: Formula To Define A Collumn Name Using Vlookup

    Sumif() is enough in this case.


    =Sumif($B:$B;"PLI104";_BreakdownMC)


    How many columns are there in the _BreakdownMC named range?

  11. #11
    Registered User
    Join Date
    03-11-2014
    Location
    Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Formula To Define A Collumn Name Using Vlookup

    1 collumn, because if I sum the _BreakdownMC, its results is true

    Yes Sumif is OK, but I want to do SUMIFS because there are another condition, for example

    IF Line = PLI104 and Shift = 1

    So the normal sumifs condition is:
    SUMIFS(_BreakdownMC;$B:$B;"PLI104";$H:$H;"1")

    isn't it?

  12. #12
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,780

    Re: Formula To Define A Collumn Name Using Vlookup

    Quote Originally Posted by pqmz1092 View Post
    So the normal sumifs condition is:SUMIFS(_BreakdownMC;$B:$B;"PLI104";$H:$H;"1")
    The formula Seems to be Okay.

    Having doubt on _BreakdownMC range other than that everything is fine

  13. #13
    Registered User
    Join Date
    03-11-2014
    Location
    Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Formula To Define A Collumn Name Using Vlookup

    Okay thanks for the help. Lemme try again from the beginning

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula which will collate names into a collumn
    By rodduz in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 09-19-2014, 06:23 AM
  2. [SOLVED] Find a collumn in a dynamic sheet and select the entire collumn
    By Armand0 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-03-2014, 11:20 AM
  3. reading a name from collumn in a formula
    By sqoo in forum Excel General
    Replies: 2
    Last Post: 04-30-2011, 02:11 PM
  4. Can't use vlookup to count multiple occurences in same collumn?
    By The Hylander in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-07-2009, 06:15 AM
  5. Replies: 1
    Last Post: 05-12-2009, 03:40 PM

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