two worksheet linking

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi
experts

I am working on 2 worksheet. In worksheet 1, there is a column called
"Customer".
In worksheet 2, all the data related to customer is stored. I have
classified customer into 3 different types.
Class A (Blue), Class B(yellow), Class C(Pink)
No. of customers in every class is different and may get added on in future.
Class A Class B Class C
(Blue) (yellow) (pink)
Cus1 Cus1 Cus1
Cus2 Cus 2 Cus2
Cus3 Cus 3 Cus3
Cus4 Cus 4 Cus4
Cus5 Cus5 Cus5
Cus6 Cus6 Cus6
Cus7 Cus7
Cus8

I have developed a MS Access Query. The data in Customer column in Worksheet
1 gets filled in automatically as soon as I run a macro associated with the
query.

What I am after is as soon as data gets filled in, colour for that customer
should change based on the class that customer belongs to.
Now I have shown only 8, 7 and 6 no. of customer for class A, B and C
respectively. In future, as customer gets added on, the cell colour for that
class of customer should change automatically. I am basically after linking 2
worksheet to get this working.

Any suggestion will be greatly appreciated.
Thanking in anticipation
 
Change the structure so col A has Class and col B has Customer. That will
drastically reduce your issues.
HTH,
 
Hi
Gary
I didn't understand you rsolution. Can you plz make it more detail. I am
after changing colour as well. The problem I am having is coz I have VB
script. I will explain you. Column F heading is Status, as soon as job on
the section finishes, somebody puts C in column F. The entire row gets cut
and paste onto sheet3.
At the moment I am using column from A to W. If you can suggest some
solution for cutting From column A to W, that will solve my problem for time
being.

Thnx for your suggestion
 
Hi Minal,

Your query was not very clear to me. But let me try to understand what you
want.

You have sheet 1, where you will get some data as cus1, cus2, etc. What is
the format, and how is this arranged.

The table you gave in your first post, is it what is in sheet1, or its in a
different sheet.

How does one know what is the color for each class. Have you a text entry
saying this is pink, or have you colored the cell with pink color (the
second one is more easy).

If you answer these queries, I could give you a way to proceed.

Mangesh
 
Hi
Mangesh
Answer to your question>> all the customer name will be stored in one column
(i.e column B) in sheet 1 only.
Table which I say will be in sheet 2, it was in sheet 1 before. It was in
column AD, AE & AF. Everything was working fine. I used conditional
formatting under column heading B. What I have done before >>
Condition 1
Formula is
=OR(B4=$AD$3,B4=$AD$4,B4=$AD$5,B4=$AD$6,B4=$AD$7,B4=$AD$8,B4=$AD$9)
Condition 2
Formula is
=OR(B4=$AE$3,B4=$AE$4,B4=$AE$5,B4=$AE$6,B4=$AE$7,B4=$AE$8,B4=$AE$9)
Condition 3
formula is
=OR(B4=$AF$3,B4=$AF$4,B4=$AF$5,B4=$AF$6,B4=$AF$7,B4=$AF$8)

Each formula represents one class of customer.
Now I want to just change my formulas . If you see 1st formula
B4=$AD$3,B4=$AD$4,..................................
Formula 1 change requirement
$AD$3 should be replaced by the sheet 2 column A row 3 cell reference
$AD$4 sholud be replaced by the sheet 2 column A row 4 cell reference
$AD$5 should be replaced by the sheet 2 column B row 5 cell reference.
Formula 2
B4=$AE$3,B4=$AE$4, .....................
formula 2 change requirement
$AE$3 should be replaced by sheet 2 column B row 3 cell reference
$AE$4 should be replaced by sheet 2 column B row 4 cell reference
$AE$5 should be replaced by sheet 2 column B row 5 cell reference

And this way same for formula 3 as well.
I hope for the correct solution!!
Thanking you in anticipation!!
 
I MANAGED TO SOLVE THE PROBLEM ON OWN.
THNX TO THOSE WHOSE TOOK TIME TO HELP ME OUT.!!!!!!!!!
CHEERS
 
HI EXPERTS
I still could not solve the problem as it says>>>
"""YOU CAN NOT USE REFERENCE TO OTHER WORKSHEET OR WORKBOOK FOR CONDITIONAL
FORMATTING CRITERION""".
I think it's beyond my capabilities now
hope to get good working suggestion!!
 
Since I already have a small code, I might as well post it.
Use this in the sheet1's event module:

Private Sub Worksheet_Change(ByVal Target As Range)

Set sht2 = Worksheets("Sheet2")
If Target.Column = 2 Then

Set rngA = sht2.Range(sht2.Range("A3"), sht2.Range("A3").End(xlDown))
Set rngB = sht2.Range(sht2.Range("B3"), sht2.Range("B3").End(xlDown))
Set rngC = sht2.Range(sht2.Range("C3"), sht2.Range("C3").End(xlDown))

Set v = rngA.Find(Target.Value, LookIn:=xlValues)
If v Is Nothing Then
Set v = rngB.Find(Target.Value, LookIn:=xlValues)
If v Is Nothing Then
Set v = rngC.Find(Target.Value, LookIn:=xlValues)
If v Is Nothing Then
Target.Interior.ColorIndex = 0
Else
Target.Interior.ColorIndex = rngB(1).Offset(-1,
0).Interior.ColorIndex
End If
Else
Target.Interior.ColorIndex = rngB(1).Offset(-1,
0).Interior.ColorIndex
End If
Else
Target.Interior.ColorIndex = rngA(1).Offset(-1,
0).Interior.ColorIndex
End If

End If

End Sub



With your table in sheet2, and the row 2 cells will have a background color
as indicated in the text above


Mangesh
 
Hi Minal,

precisely, I thought so, and I was wondering how you solved it with
conditional formatting. Check my post where I have given a VBA solution.

Mangesh
 
Hi Mangesh

Thank you for VB code. I have copied all the coding in sheet1, but nothing
seems to happen. Can you plz suggest me some tricks so that it should work
fine.

How do I set the colour for column A,B & C in sheet 2. I tried to do that
but didn't get through it. I want to set the colour format for each class.
Say for class A: cyan blue, CLass B: yellow, Class C: Magenta

Thanking you in anticipation!!



Mangesh Yadav said:
Since I already have a small code, I might as well post it.
Use this in the sheet1's event module:

Private Sub Worksheet_Change(ByVal Target As Range)

Set sht2 = Worksheets("Sheet2")
If Target.Column = 2 Then

Set rngA = sht2.Range(sht2.Range("A3"), sht2.Range("A3").End(xlDown))
Set rngB = sht2.Range(sht2.Range("B3"), sht2.Range("B3").End(xlDown))
Set rngC = sht2.Range(sht2.Range("C3"), sht2.Range("C3").End(xlDown))

Set v = rngA.Find(Target.Value, LookIn:=xlValues)
If v Is Nothing Then
Set v = rngB.Find(Target.Value, LookIn:=xlValues)
If v Is Nothing Then
Set v = rngC.Find(Target.Value, LookIn:=xlValues)
If v Is Nothing Then
Target.Interior.ColorIndex = 0
Else
Target.Interior.ColorIndex = rngB(1).Offset(-1,
0).Interior.ColorIndex
End If
Else
Target.Interior.ColorIndex = rngB(1).Offset(-1,
0).Interior.ColorIndex
End If
Else
Target.Interior.ColorIndex = rngA(1).Offset(-1,
0).Interior.ColorIndex
End If

End If

End Sub
Click to expand...
 
Hi Minal,

The following code was written with the example you gave. The only thing you
need to do is:
In sheet2, cells A2, B2, and C2 should be colored (the backgroud should be
colored as per your requirements).

Mangesh
 
Hi Mangesh

Thnx for your suggestion!!! I appreciate you help, Sorry but mothing seems
to happen again. As you said the code was written a/c to my example, I would
like to clarify few more bits.

I have set background colour in sheet 2 cell A3, B3 & C3 (and not whole
column) as per my choice. I already have set up conditional formatting in
sheet 1 in other columns. I have written VB script in sheet 1. I am using
column A to column W in sheet 1.
Data in sheet 1 from column A to column E gets copied over automatically. I
have developed MS Access query to get the data. After I analyse executed
query with excel, all the data in executed query gets copied over to another
excel file(say A). I have recorded macro to copy data from ( A) excel file
into main excel file sheet 1.
Data in column B in main excel file(where all the customer name gets listed)
also gets copied over.
As I have said before as soon as I run macro, data gets copied over, from
column A to column E, column B should change colour depending on the class. I
think this might help you a bit. If you need more help from me, I am happy to
answer your query.
Thanking you in anticipation!!!
Minal
 
First thing before I read your mail completely, set the background color for
cells A2, B2 and C2 and not row 3.

Mangesh
 
Hi mangesh

I have done that , the day you gave me solution. I have done that this way
Select Cell A2 in sheet 2, click right hand mouse button, select patterns,
select colour, click ok . Done that for cell B2, C2 as well.
The background colour for the cell A2 changes as per settings but this
information is not getting passed onto sheet 1.

Thnx for ur help!
 
Hi again

Sorry but it is not working. If I check code no error in that, that
background colour information is not getting passed on. Can't figure out any
reason for that.
Thanking you in anticiapation!
 
If you don't mind sharing your excel sheet, why don't you mail me your excel
sheet with a little explanation. Maybe something like I just have to press a
button to run your macro, and a little write-up on what should happen after
pressing this button. You can write to me at my gmail id. And remove NOSPAM.

Mangesh
 
hi Mangesh

If you can give me your email-id that will be really helpful. I can
forward my excel sheet with explanation in it.

Thnx
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

matching & combining data from seperate worksheets 2
Sumif, unique counts, 4
Excel Spreadsheet 3
summaries on alternative sheets 6
identifying data from a separate worksheet 1
compare and return a value from another worksheet 3
Multiple Criteria for Sumproduct 6
Copying data from Sheet1 which is on multiple rows for each customerto Sheet2 on single row. 2

Back
Top