Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

multi tabs, how do you keep font format with IFor VLookup

0

i run a report in a web based programme and send excel 

I then copy the report contents and paste it into "Sheet1" of the attached - lets call this the raw data, it is not in any particular order and it is 200 row and 300 columns

then on Sheet "VOC" i have fixed headings in the order i want the data and i use an IF formula to look up "Sheet1" and create my organised report.

BUT it does not bring the "colour" formatting over. 

the X should be RED

and some dots needs to be RED and some need to be GREEN.

at the moment, they are all black as the sheet font colour is black. how to i get the actual font colour to transfer over?

Answer
Discuss

Discussion

Sue,

It's late here and I'm reluctant to answer your question. Why? I spent some time back in August helping to fix your previous problem formula to check multiple criteria in 2 worksheets but you didn't do me the courtesy of marking my Answer as Selected. It may have been an oversight on your part but it felt like I'd wasted my time. Unsuprisingly I'm loathe to do that again!
John_Ru (rep: 6092) Jan 10, '23 at 5:44 pm
HI John, i do appologise, i did not realise that i had not followed through with the selection of answer correctly. i have gone back to the origianl request and i think i have done this now. Let me know if i havent. (not done in the expectation that you will help me again, just my ignorance at using the page properly.)
sue (rep: 4) Jan 10, '23 at 5:53 pm
Sue, thanks for doing that. Please note that we get absolutely no reward for answering users' questions, apart from feeling we've helped, getting your thanks and a slightly increased Reputation on the Forum (only if the user marks the Answer as Selected).

The short answer is that you can't copy cell formatting with just IF / VLOOKUP formulae and your data doesn't lend itself to a solution using Conditional Formatting nor doing a custom horizontal (left to right) sort, since you don't seem to report all columns from the source "raw" data. However...

Rather than state just that as an answer, it is possible to do what you want using VBA (Excel's built-in programming language). I guess you don't know that but can probably do that tomorrow if you could please attach a second file to your question above with the full number of columns and just a few rows of data (but no real personal data - or change the names to fake ones).

That way I can test the solution works properly and provide a working answer. I guess you want that data sorted alphabetically by column A, right?
John_Ru (rep: 6092) Jan 11, '23 at 7:45 am
Hello Sue,
The formulas in your sample file indicate the actual file is 175 rows x 350 columns.
As John has stated, VBA will be needed to achieve what you are lookikng to do. But I am confused how you get your dots and X's. Both sheets have all cells formatted with "Calibri"(11) font but a "dot" is not part of this family and the "X" used on both sheets is also not part of that font family. The "dots" can be green, gold, or red. You have a legend indicating what each colour means but you don't explain how the colours are applied. Typically conditional formatting can be used to control the colour of an entry but not when special characters are used.
More info is needed about the special characters (dots and the X) that are used and how the colour of the dots is applied.
WillieD24 (rep: 537) Jan 11, '23 at 4:46 pm
@Willie
Good comments, thanks. I supsect Sue just runs a web-tool and may not have info on the special characters/ colours. Accordingly I've given an Answer that just matches columns and pastes them into the report sheet (without caring how they got to the source sheet). The macro may be a bit long-winded and confusing (e.g the second sheet is named Sheet1) but it works provided VOC is the first sheet.

@Sue
Please see my Answer. You may need to search the internet to see how to enable macros (or even get your company IT department to allow you to enable them). Loking forward to your comments.

John_Ru (rep: 6092) Jan 12, '23 at 7:16 am
@John_Ru 
Thanks everyone, i did work out that If and Vlookup would not work, and that conditional formatting would not work. I did come accross some info suggesting VBA and marcro enabled workbootk, but i could not get the solution to work. (it was limited and not for a large aray of data)

I have attached 2 documents
Western Range symbol - this is the raw data report i get from the web based programme.

i then copy it and paste into "training matrix, sheet 1" where the VOC page has formulas to sort the data that i want into a logical format and exclude data that i dont need.

I am fortunate that the Data in colum A is already alphabetical, so that is not an issue. 
sue (rep: 4) Jan 13, '23 at 1:25 am
Sue. 

I will look at your new file later today if I get chance but please note that my Answer should work with more columns and rows (but perhaps a little slowly).

Did the basic answer file below work for you?
John_Ru (rep: 6092) Jan 13, '23 at 1:39 am
Sue, I modified my macro very slightly so it works with your training file (i.e. it deletes and pastes from row 3 not 2) but otherwise it works well. Please try and report back. Thanks!
John_Ru (rep: 6092) Jan 13, '23 at 4:06 am
thanks, i will play around with it tomorrow. 
sue (rep: 4) Jan 13, '23 at 4:20 am
Okay Sue but all you have to do initially is press that green button!

Have to say that the height of your frozen rows was distracting- do you need all the Q codes on separate lines? I'd suggest having them just separated by commas (say) and text-wrapped. That way the height of row 2 could be much less
John_Ru (rep: 6092) Jan 13, '23 at 5:47 am
@John_Ru
You are a LEGEND, the report is a amazing. THANK YOU.
Once i enabled macros etc, it worked ! 
VBA is out of my level of expertise, i never would have been able to put together the programming. 

Yes the Row 2 height is large. The Q codes are ID number of vehicles. If i make the colums wider, the report is too wide for printing, so the headers work better this way, i can usually get a section on 2 x A3 pages in landscape. And i break the report into 5 sections wide. Fortunately i only print it monthly.

I will select your answer to close this off. Thanks Again.
sue (rep: 4) Jan 13, '23 at 5:29 pm
Great. Glad it worked for you. Thanks for selecting my answer, Sue. 
John_Ru (rep: 6092) Jan 13, '23 at 5:49 pm
Add to Discussion

Answers

0
Selected Answer

Sue,

You can't copy cell formatting with just just an IF or VLOOKUP formula and your data doesn't lend itself to a solution using Conditional Formatting (without further information on your source) nor doing a custom horizontal (left to right) sort, since you don't seem to report all columns from the source "raw" data. However...

It is possible to do what you want using VBA (Excel's built-in programming language). In the attached file (a version of your training file but which will need macros enabled) I've copied your report sheet to the end (see tab named "Question VOC") so it can be used to compare results).

I've then add added a green button stating "Import columns" in cell A1 of the first worksheet "VOC". 'Behind' that is the macro below (in VBA's Module1). I'm assuming you don't know VBA but if you have your imported data in a second sheet (like your Sheet1) and click that button (with macros-enabled), it will check some columns match (or tell you) and proceed to copy matching columns from Sheet1.

To get an idea of what's happening, follow the comments -red text lines below (starting with ')- especially the major steps starting ' # and in bold.

Option Base 1
Sub SortKeepingFormats()

Dim RepLstCl As Long, SrcLstRw As Long, SrcLstCl As Long
Dim ColXRef() As Variant, CpyRng As Range, Mtch As Boolean
Dim m As Long, n As Long

' speed up for large data sets
Application.ScreenUpdating = False

' #1. work out some sheet dimensions
RepLstCl = Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column
SrcLstRw = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
SrcLstCl = Sheets(2).Cells(1, Columns.Count).End(xlToLeft).Column

'# 2. size cross reference array
ReDim ColXRef(1 To RepLstCl, 1 To 2)

' #3. cross reference headers in report (to source)
For n = 1 To RepLstCl
    'populate the report columns
    ColXRef(n, 1) = Sheets(1).Cells(1, n)
    ' loop through source to find matching headers
    For m = 1 To SrcLstCl
    'check report column c.f. array value
        If Sheets(2).Cells(1, m).Value = ColXRef(n, 1) Then
            ColXRef(n, 2) = m
            If n > 1 Then Mtch = True ' show a column match was made
        End If
    Next m
Next n

' check some columns matched
If Mtch = False Then
    MsgBox "No matching data columns; original data left intact"
    Exit Sub
    Else
    ' clear data from row 3 and continue
    Sheets(1).Rows("3:1000").Delete
End If

' #4. Copy data from source to report using cross refs
' go across headers
For n = 1 To RepLstCl
    ' if first column or there's a cross ref to source
    If n = 1 Or Not IsEmpty(ColXRef(n, 2)) Then
        ' copy source rows 2 to end
        With Sheets(2)
            Set CpyRng = .Range(.Cells(2, ColXRef(n, 2)), .Cells(SrcLstRw, ColXRef(n, 2)))
            CpyRng.Copy Destination:=Sheets(1).Cells(3, n)
        End With
        Else
        'otherwise collect headings not imported
        NoX = NoX & ColXRef(n, 1) & vbCr
    End If

    ' write borders and fill background like header
    With Sheets(1).Range(Cells(3, n), Cells(SrcLstRw + 1, n))
        .Borders.LineStyle = xlContinuous
        .Interior.Color = Sheets(1).Cells(1, n).Interior.Color
    End With
Next n

Application.ScreenUpdating = True ' restore

' tell user what did/ didn't happen
If NoX <> "" Then MsgBox "Imported from source " & Now & " but didn't find matches for report headers:" & vbCr & vbCr & NoX

End Sub

Currently all the dots show green but if you copy a red one on Sheet1 to an imported column (say F,  Contruction Blue/White Card) then you'll see it gets imported correctly.

Note that if it doesn't care how many rows or columns you have in your 2 sheets (provided there are no more that 1000 rows in VOC) and will copy as many source rows and matched columns as there are- if there are 4 rows in your source, you'll get 4, if 230, you get 230 etc.

Also if you add a fill to a row 1 header in VOC (say blue in matching column C as I have), the data will copy but with that colour background added.

Hope this makes some sense and helps. If so, please remember to mark the Answer as Selected.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login