+ Reply to Thread
Results 1 to 3 of 3

Formatting not preserved INDEX - MATCH

  1. #1
    Registered User
    Join Date
    04-05-2016
    Location
    India
    MS-Off Ver
    2013
    Posts
    78

    Formatting not preserved INDEX - MATCH

    There is a sheet named MASTER where the data of all items are available with a code for each item. The text description has few words bold and italics.
    There is another sheet named LIST where I am trying to prepare a list of selected items based on the assigned code.

    For matching the description in the LIST sheet, I have used the following formula

    =IFERROR(INDEX(MASTER!$C$2:$C$5,MATCH(B2,MASTER!$B$2:$B$5,0)),"")

    But, the formatting of text as in the MASTER sheet is not preserved in the LIST sheet. The text description in LIST sheet appears as plain text.
    Could anyone help?

    my worksheet is uploaded http://s000.tinyupload.com/?file_id=...26694586699742
    Last edited by melvinkoshy; 12-07-2017 at 11:11 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,704

    Re: Formatting not preserved INDEX - MATCH

    A formula cannot return or affect the formatting of a cell - it can return a value only.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    04-05-2016
    Location
    India
    MS-Off Ver
    2013
    Posts
    78

    Re: Formatting not preserved INDEX - MATCH

    Code, Description, Unit, Rate in an Excel worksheet named MASTER sheet are in Col. A, B, C & D respectively from row A2 to A8. Some portion of Description of each item is bold and italicized. OUTPUT sheet has Code, Description, Qty, Unit, Rate, Amount. In the Output sheet Code, Description, Unit, Rate are looked up from the MASTER sheet.

    Since the formatting has to be preserved in the OUTPUT sheet, I used code as follows in the OUTPUT sheet. But the quantity column I getting automatically filled up and the rate field does not indicate the rate as in MASTER sheet. Any help in correcting the code will be helpful. Is there any alternate way to copy each column with separate commands in vba code?

    The master and output sheets as desired by me is shown http://s000.tinyupload.com/?file_id=...88599570337640

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim idx As Long
    Application.EnableEvents = False
    If (Target.Column = 1) And (Target.CountLarge = 1) And (Not IsEmpty(Target.Value2)) Then
    idx = Excel.WorksheetFunction.Match(Target.Value2, Sheets("MASTER").Range("$A$1:$A$8"), 0)
    If idx > 0 Then
    Sheets("MASTER").Range("B" & idx, "D" & idx).Copy Target.Resize(1, 3).Offset(, 1)
    End If
    Else
    Target.Resize(1, 3).Offset(, 1).ClearContents
    End If
    Application.EnableEvents = True
    End Sub
    Last edited by melvinkoshy; 12-17-2017 at 01:44 PM.

+ 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. [SOLVED] index match formatting issue
    By nigelog in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-23-2016, 12:33 PM
  2. Conditional formatting with INDEX MATCH MATCH
    By jawebb in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 04-28-2016, 05:01 AM
  3. use source formatting in an index match
    By almostgenius in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-20-2015, 11:42 AM
  4. Formatting with INDEX MATCH MATCH Problem
    By SwissExcel in forum Excel General
    Replies: 1
    Last Post: 07-22-2015, 07:40 AM
  5. Index Match not Formatting Correctly
    By CreamOfWheat in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-20-2015, 07:53 PM
  6. Excel 2010 pivot table formatting not preserved after update
    By MARKSTRO in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-30-2013, 01:37 PM
  7. Formatting not preserved in web query Excel 2007
    By Methodician in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-05-2013, 01:36 PM

Tags for this Thread

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