condiitonal format sheet 1 from values in sheet2
I am using MS Excel 2007. I want to conditionally format sheet 1 based on the values in sheet 2. I have 10000 x 1000 rows in sheet 1 and sheet 2.
I tried to conditonally format a single row with following formula:
Edits: Small sample example is attached, where I did conditional formatting in column 1.=INDEX(Sheet2_ColA,ROW())>0.5
I defined sheet2 colA as Sheet2_ColA used in naming column function. I tried to name whole sheet 2 and apply to sheet 1 but do not work.
As I have huge number of column to deal with I can not do column by column (at least will not be justified in computer age !)
thank you
Last edited by shramh; 10-21-2011 at 10:42 AM.
What exactly are you trying to do? I.e. what is the logic you are looking for in layman's terms?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
I want apply conditional format to sheet 1 ( whole sheet, i.e. comulns and rows) based on sheet 2 ( whole sheet 2, i.e. coulmn and rows).
Means that if value in sheet2 A1 cell is > 0.5, the sheet1 A1 cell will be formatted as "red" color.
Similarly for all corresponding cells in sheet 1 are conditionally formated based on sheet 2. I could do one by one for each column wise (see edits with example for column 1 attached "example1.xls" ).
I have more than 10000 columns so I want to repeate the same process to whole worksheet.
thanks;
You can apply a name to the whole of sheet2 (although it is recommended you limit the columns and rows because conditional formatting is volatile and therefore recalculates for all cells when you make a change in just one).
so if you select and name Sheet2!A1:L18 like Sheet2Data
Then in sheet1 select A1:L18 and apply conditional formula:
=AND(A1<>"",INDEX(Sheet2Data,ROW(),COLUMN())<0.5)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks