Barhomopolis

New Member
Joined
Jan 10, 2012
Messages
21
Greetings Everybody :)

I have a workbook in wich I consolidate 65,535 rows of data from 6 other workbooks using compound lookup. I lookup (using INDEX & MATCH) based on a double criteria. That's not the issue.

The issue is that I need to grab not only the values of the matching cells, but also their colours. I need to it the simplest way possible for the sheet is huge and the lookup function is already too complex.

The lookup formula I'm currently using (nested six times for the six files in a nested IFERROR) looks like this:

=IFERROR(INDEX('Z:\1.xlsx'!Table1;MATCH([@
Code:
]&[@Loc];'Z:\1.xlsx'!Table1[Code]&'Z:\1.xlsx'!Table1[Loc];0);4);INDEX('Z:\2.xlsx'!Table2;..........And so on six times for six files.

This copies the value of the cell with certain Code and Location from workbook 1.xlsx
I want something that copies the value AND the colour of the cell that matches the Code and Location.

I appreciate any help.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Have you looked at conditional formatting if the criteria is set in the other workbooks to set the colour then you should be able to use the same criteria in your look up workbook.
 
Upvote 0
@Trevor G

Thank you for your reply :)
The colouring of the source cells is not conditional and cannot be turned to conditional because it is based on subjective measures, i.e. the colours of the source cell are independent of their values.

mmm.... But now you gave me an idea... Maybe we can: 1- add a column to the source workbooks with values that change based on the source cells colour; 2- then, lookup the values of that new column in the consolidation workbook; 3- then, conditionally colour the consolidated looked up cells based on the values of the new looked up column...

But then again: how do we do step 1? Is there something to change the value of a cell based on the colour of another cell in the same sheet?
And would that be an easier workaround than solving the original problem?

Thanks again.
 
Upvote 0
If your going to use a helper column then that is the same as using conditional formatting. What are the variable values of the cell, could you make up a small table to identify the conditions?
 
Upvote 0
There are no conditions... the source cells are coloured manually by the users on a totally subjective basis. I just want the consolidation workbook to see that the user of the source workbook chose to colour a certain cell with a colour, and lookup that cell's value and format the corresponding target cell with the same colour.
 
Last edited:
Upvote 0
Can you work with this then.

[face=Courier New]Sub cellcol()
Range("E3") = Range("B3").Value
Range("E3").Interior.ColorIndex = Range("B3").Interior.ColorIndex

End Sub
[/face]
 
Upvote 0
Thank you, Trevor G.

Could you please show how would this be used in my particular case:

Lookup value columns: 'Z:\7.xlsx'!Table7
Code:
 & 'Z:\7.xlsx'!Table7[Loc]

Lookup columns 'Z:\1.xlsx'!Table1[Code] & 'Z:\1.xlsx'!Table1[Loc]

Source column: 'Z:\1.xlsx'!Table1[CPC]

Destinantion column: 'Z:\7.xlsx'!Table7[CPC]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top