+ Reply to Thread
Results 1 to 2 of 2

Cross-referencing another spreadsheet (or tab) w/ conditional formatting

  1. #1
    Registered User
    Join Date
    09-08-2014
    Location
    Wake Forest, NC
    MS-Off Ver
    2010
    Posts
    14

    Cross-referencing another spreadsheet (or tab) w/ conditional formatting

    Hey all,

    I have a pricing catalog and a 12-month order report. The pricing catalog contains *all* products available, but I'd like to restrict the information on the pricing catalog to those the customer has purchased within the last 12 months. I read that this function can only be done via macro, so I need some help here. VLOOKUP is, unfortunately, not my forte, so I'd be happy with "if X catalog number is seen in the Order Report spreadsheet, then color the corresponding list number cell in column B green in the pricing catalog file/tab."

    The full pricing catalog has a total of 9 columns, and the order report has 25. The columns to match are the EDI number, which is:
    --Pricing fie: Column B on the real report
    --Order report: Column N on the real report

    The example is below (I can't attach a file due to computer restrictions, my apologies). We may just have to start with "does anything in the right column (those being products ordered) correspond with items in the left column (pricing catalog)".
    Thanks in advance for your assistance!

    01E0121 01P7402
    01E0122 06K3010
    01E0221 02K9803
    01E0222 05P5601
    01E0421 01P7427
    01E0422 08G6321
    01E0442 06K2610
    01E0602 06L4521
    01E0621 01E1320
    01E1120 05P0401
    01E1320 06E4421
    01E3120 09D5102
    01E4603 01E5020
    01E4604 01E4921
    01E4703 01L8203
    01E4921 07K5901
    01E5020 01E4604
    01E6403 06E2365
    01E6506
    01E6605
    01E7102
    01E7103
    01E7502
    01E7802
    01E7804
    01G4601
    01G4702
    01G4704
    01G4804
    01G4805
    01G5101
    01G5201
    01H5901
    01H7301
    01H7601
    01H7701
    01H7801
    01H7901
    01J7220
    01L1901
    01L1902
    01L1903
    01L1904
    01L1905
    01L1906
    01L2201
    01L2401
    01L2402
    01L2601
    01L2602
    01L5640
    01L5901
    01L7101
    01L7110
    01L7125
    01L7127
    01L7501
    01L7525
    01L7555
    01L7566
    01L7601
    01L7625
    01L7655
    01L7666
    01L7701
    01L7725
    01L7755
    01L7766
    01L7901
    01L7910
    01L7925
    01L7935
    01L8203
    01L8213
    01L8227
    01L8237
    01L8240
    01L9101
    01L9102
    01L9301
    01L9401
    01P0602
    01P0603
    01P1201
    01P1601
    01P2901
    01P2902
    01P2925
    01P2927
    01P3001
    01P3002
    01P3024
    01P3028
    01P3101
    01P3125
    01P3202
    01P3227
    01P3301
    01P3325
    01P3401
    01P3425
    01P3501
    01P3525
    01P3601
    01P3627
    01P3650
    01P4101
    01P6501
    01P6510
    01P6525
    01P7402
    01P7412
    01P7427
    01P7437
    01P7450
    01P7601
    01P7602
    01P7603
    01P7604
    01P7610
    01P7611
    01P7620
    01P8301
    02G1699
    02H6302
    02H9002
    02H9003
    02H9101
    02H9602
    02H9801
    02H9802
    02H9901
    02H9902
    02J5001
    02J5101
    02J5301
    02J9301
    02J9421
    02J9422
    02K1001
    02K4101
    02K4110
    02K4127
    02K4137
    02K4201
    02K4210
    02K4225
    02K4235
    02K4301
    02K4310
    02K4320
    02K4325
    02K4402
    02K4410
    02K4411
    02K4421
    02K4427
    02K4437
    02K4502
    02K4511
    02K4524
    02K4529
    02K4539
    02K4601
    02K4610
    02K4620
    02K4625
    02K4701
    02K4710
    02K4727
    02K4801
    02K4810
    02K4820
    02K4825
    02K9103
    02K9112
    02K9133
    02K9610
    02K9803
    02K9811
    02K9821
    02P1301
    02P1310
    02P1327
    02P3211
    02P3250
    02P3601
    02P3610
    02P3625
    02P3635
    02P4001
    02P4025
    02P4035
    02P4621
    02P4630
    02P4690
    02P5070
    02P5080
    02P5089
    02P5401
    02P5410
    02P5427
    02P5621
    02P5622
    02P6802
    02P6901
    02P7302
    02P7501
    02P7601
    02P7701
    02P7801
    02P7901
    02P8001
    02P8101
    02P8201
    02P8301
    02P8401
    03A4230
    03B9602
    03E1602
    03E2020
    03E2120
    03E2220
    03E2320
    03E2420
    03E5026
    03E5031
    03H0501
    03H4001
    03H6201
    03H7601
    03H8002
    03H9901
    03K0101
    03K0120
    03K3321
    03K3322
    03L3401
    03L3402
    03L3403
    03L3404
    03L3420
    03L3520
    03L3601
    03L3602
    03L3610
    03L3612
    03L3620
    03L3720
    03L3820
    03L3920
    03L4020
    03L4101
    03L4102
    03L4103
    03L4104
    03L4110
    03L4112
    03L4114
    03L4116
    03L4120
    03L4201
    03L4202
    03L4203
    03L4204
    03L4211
    03L4220
    03L4301
    03L4302
    03L4303
    03L4304
    03L4305
    03L4310
    03L4502
    03L5301
    03L5302
    03L5310
    03L5311
    03L5325
    03L5327
    03L7921
    03L7922
    03L7931
    03L7932
    03L8021
    03L8022
    03L8031
    03L8032
    03L8122
    03L8123
    03L8132
    03L8133
    03L8221
    03L8222
    03L8241
    03L8242
    03L9901
    03P2201
    03P2202
    03P2203
    03P2209
    03P2425
    03P3601
    03P3610
    03P3625
    03P3921
    03P3922
    03P6822
    03P6832
    03R1130
    03R3001
    03R3025
    04D1803
    04D1901
    04H0202
    04H3401
    04H4001
    04H8701
    04J2901
    04J3001
    04J3101
    04J3201
    04J3301
    04J4501
    04J4901
    04J6620
    04J6720
    04P4801
    04P5110
    04P5201
    04P5211
    04P5220
    04P5301
    04P5310
    04P5325
    04P5335
    04P5425
    04P5440
    04R2921
    04W3301
    04W3302
    04W3303
    04W3311
    04W3312
    04W3320
    05E1301
    05E1501
    05P0202
    05P0212
    05P0225
    05P0235
    05P0301
    05P0310
    05P0327
    05P0401
    05P0521
    05P0621
    05P0721
    05P0821
    05P5601
    05P5602
    05P7610
    05P7710
    05P7810
    05P7811
    05P7812
    05P7911
    05P7912
    05P8010
    05P8110
    05P8301
    05P8302
    05P8410
    05P8411
    06C0601
    06C0610
    06C0627
    06C0635
    06C0701
    06C0710
    06C0727
    06C2650
    06C5458
    06C5488
    06C5560
    06E2365
    06E2710
    06E2720
    06E2730
    06E2740
    06E2750
    06E2760
    06E2770
    06E4421
    06E5702
    06E5703
    06E6701
    06E6801
    06E6902
    06H4301
    06H4501
    06H4601
    06H4701
    06H4801
    06H4901
    06H5301
    06H5401
    06H5601
    06H6401
    06H6501
    06H8101
    06H8801
    06H8901
    06H9201
    06K0120
    06K2610
    06K2612
    06K2614
    06K2621
    06K2630
    06K2631
    06K2641
    06K2642
    06K3010
    06K3110
    06K3850
    06K3851
    06K3940
    06K3941
    06K4150
    06K4151
    06K4240
    06K4241
    06K4502
    06K4504
    06K4602
    06K4604
    06K4702
    06K4704
    06K4902
    06K4904
    06K5002
    06K5004
    06K5321
    06K5322
    06K5421
    06K5422
    06K5522
    06K5523
    06K5621
    06K5622
    06K8920
    06K8930
    06K9130
    06K9530
    06K9541
    06K9940
    06K9941
    06L0001
    06L0101
    06L0402
    06L0501
    06L0601
    06L2101
    06L2110
    06L2125
    06L2201
    06L2210
    06L2225
    06L2301
    06L2310
    06L2325
    06L2701
    06L2710
    06L2725
    06L2820
    06L3020
    06L3101
    06L3440
    06L3441
    06L3501
    06L3520
    06L3601
    06L3602
    06L3603
    06L3605
    06L3610
    06L4521
    06L4522
    06L4541
    06L4542
    06L9620
    06L9720
    06P1801
    06P2201
    06P2210
    06P2227
    06P2237
    07C1401
    07C1503
    07D0460
    07D0840
    07D0845
    07D0850
    07D0855
    07D5324
    07D5421
    07D5522
    07D5532
    07D5621
    07D5622
    07D5821
    07D5822
    07D5842
    07D6222
    07D6321
    07D6322
    07D6521
    07D6522
    07D7123
    07D7132
    07D7321
    07D7322
    07D7422
    07D7522
    07D7531
    07D7532
    07D7921
    07D7922
    07D7931
    07D7932
    07D8031
    07D8121
    07D8122
    07D8250
    07F9301
    07F9320
    07H2201
    07H6602
    07K0040
    07K0041
    07K5901
    07K5910
    07K5925
    07K5935
    07K6102
    07K6112
    07K6127
    07K6137
    07K6201
    07K6210
    07K6225
    07K6230
    07K6235
    07K6301
    07K6302
    07K6310
    07K6312
    07K6325
    07K6327
    07K6332
    07K6335
    07K6337
    07K6401
    07K6402
    07K6425
    07K6427
    07K6432
    07K6435
    07K6437
    07K6450
    07K6502
    07K6510
    07K6529
    07K6534
    07K6539
    07K6602
    07K6612
    07K6627
    07K6632
    07K6637
    07K6802
    07K6812
    07K6827
    07K6832
    07K7201
    07K7210
    07K7225
    07K7250
    07K7501
    07K7510
    07K7525
    07K7535
    07K7601
    07K7610
    07K7625
    07K7635
    07K7701
    07K7710
    07K7725
    07K7750
    07K7801
    07K7810
    07K7825
    07K7830
    07L0101
    07L0201
    07L0301
    07L0401
    07L0501
    07L0601
    07L0703
    07L0801
    07L1701
    07L4001
    07L4010
    07L4020
    07L4201
    07L4401
    07L5951
    08C9407
    08C9414
    08C9419
    08C9421
    08C9429
    08C9436
    08C9447
    08C9449
    08C9456
    08C9463
    08C9465
    08C9466
    08C9467
    08C9489
    08C9490
    08D0604
    08D0613
    08D0631
    08D0641
    08D1502
    08D1525
    08G6321
    08G6322
    08G6621
    08G6702
    08H0601
    08H0602
    08H0604
    08H0606
    08H0703
    08H0801
    08H1001
    08H1002
    08H1201
    08H1302
    08H1401
    08H3001
    08H3101
    08H3201
    08H3301
    08H3302
    08H3401
    08H3801
    08H3901
    08H4101
    08H4301
    08H4501
    08H4601
    08H4701
    08H4801
    08H4904
    08H5201
    08H5304
    08H5401
    08H5602
    08H5603
    08H5701
    08H5801
    08H5802
    08H5901
    08H5902
    08H5904
    08H5905
    08H5906
    08H6201
    08H7895
    08H8920
    08H8928
    08K0002
    08K0401
    08K1301
    08K2503
    08K2504
    08K2512
    08K2513
    08K2527
    08K2529
    08K2601
    08K2602
    08K2611
    08K2625
    08K2627
    08K2701
    08K2702
    08K2710
    08K2711
    08K2725
    08K2727
    08K2803
    08K2804
    08K2812
    08K2813
    08K2828
    08K2836
    08K4103
    08K4112
    08K4128
    08L2431
    08L2441
    08L2530
    08L7101
    08L7201
    08L9121
    08L9122
    08L9141
    08L9142
    08L9221
    08L9222
    08L9241
    08L9242
    09D2207
    09D2211
    09D2212
    09D2804
    09D2921
    09D3120
    09D3205
    09D3303
    09D3403
    09D3503
    09D3602
    09D3703
    09D3803
    09D3903
    09D4004
    09D4103
    09D4202
    09D4302
    09D4503
    09D4803
    09D4805
    09D4810
    09D4903
    09D4905
    09D5002
    09D5102
    09D5203
    09D5303
    09D5903
    09D6103
    09D6304
    09D8721
    09D8921
    09D9121
    09D9221
    09D9222
    09D9321
    09D9322
    09D9621
    09D9622
    09D9721
    09D9722
    09D9821
    09D9822
    09D9921
    09D9922
    09H0001
    09H0403
    09H0601
    09H3102
    09H3201
    09H3802
    09H4101
    09H4201
    09H4202
    09H4602
    09H4702
    09H4802
    09H5001
    09H5101
    09H5201
    09H5301
    09H5406
    09H5701
    09H5801
    09H6101
    09H6201
    09H6901
    09H6902
    09H7001
    09H7201
    09H7202
    09H7301
    09K9401
    09K9402
    09P1821
    2856001
    2856101
    9148401
    9148501
    9253201
    9320101
    9923101
    9931101
    9932101
    9943101
    9964401
    9965201

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Cross-referencing another spreadsheet (or tab) w/ conditional formatting

    If you're just looking to highlight cells by colouring them, you could do that with Conditional Formatting. You could use COUNTIF to check if the order cell exists in the pricing list.

    Easy enough to do but I'm not planning on creating a workbook and worksheets to set up the CF formula. If I try to give you an example based on what you've described, we could spend a lot of time bouncing this backwards and forwards.

    It doesn’t need to be a real file and it doesn't need to come from work but I would still want to see a sample file prepared by you in order to produce a tested solution.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


+ 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] Need macro to populate spreadsheet based on cross referencing of tabs
    By eguirocker3 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-13-2015, 08:46 PM
  2. Populate spreadsheet based on cross referencing of tabs.
    By eguirocker3 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-09-2015, 02:22 PM
  3. Recordset cross referencing spreadsheet data not updating missing values
    By Sc0tt1e in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-20-2013, 08:36 AM
  4. Conditional Formatting - Cross-hair Shading
    By ScotyB in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-02-2013, 10:24 PM
  5. [SOLVED] Cross-referencing images (formatting)
    By Equipoise in forum Word Formatting & General
    Replies: 2
    Last Post: 09-09-2013, 07:09 AM
  6. Conditional Formatting - Ticks & Cross
    By Excel-Access in forum Excel General
    Replies: 1
    Last Post: 07-19-2012, 09:17 AM
  7. Replies: 3
    Last Post: 05-15-2012, 04:13 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