#1
|
|||
|
|||
Index(match) & conditional format vba instead
Hi Guys,
This is a difficult query, unless I'm not seeing something here sorry I typed this one up in a messy way. Hopefully you guys can follow this. I have a MAIN SHEET with a column containing ID, and this ID column is linked to 2 different sheets via index matches, ID UPDATE & ID NEW. The details in said ID is edited via the user in the 2 different sheets AND NOT in the MAIN SHEET. The way I want to show visible updates to ID details is done via a conditional format simply making the text red and bold. Now in order to achieve this I have the following simple excel functions called: I have 5 columns (the details in ID -> ID DESCRIPTION, ID TYPE, etcetc) running =IFNA(IFNA(IF(INDEX(RANGE(A:Z),MATCH(X8,TABLE1[DATA1],0),3)=0,(INDEX(RANGE(A:Z),MATCH(X8,TABLE1[DATA1],0),2)),INDEX(RANGE(A:Z),MATCH(X8,TABLE1[DATA1],0),3)),(INDEX(TABLE3,MATCH(DATA2,TABLE3[DATA1],0),2))),"") - this is an example. Sorry if that's hard to follow.. With 5 conditional formats on all columns (now you see why it lags - note this is what makes the changes to the ID filed red. NEW RULE > FORMULA > =INDEX(RANGE,MATCH(VALUE,COLUMN,0),COLUMNNUMBER)<> VALUE2 APPLIED TO RANGE: whole column Now is there a way to do this without lagging the hell out of my spreadsheets |
#2
|
|||
|
|||
Before I can advice anything, I have a couple of questions.
1. Why doesn't user enter new ID's and updates to existing ID's directly into main sheet? I.e. how are data processed? 2. What happens when there are new ID's or existing ones for updating entered? Are entries on main sheet updated somehow? 3. When main sheet is updated, what happens with data (used to update) in other sheets afterwards? 4. What happens, when existing ID is entered as new one? Or when non-existing ID is entered for updating? 5. Can there be cases, where existing ID must be deleted? |
#3
|
|||
|
|||
ArviLaanemets, thank you for replying, I am going to close this one off as I no longer require this job. Thanks anyway Arvi, commended anyway.
|
Tags |
conditional format, index, vlookup |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Generated spreadsheet in SLK format causes message upon browsing: format / extentsion don't match. | PhillJ | Excel | 4 | 08-22-2017 03:10 AM |
Index/match, Vlookup? | kelwea | Excel | 1 | 01-24-2017 10:19 AM |
Excel - Index and Match Function ,First Second and Third Match | paulzy95 | Excel | 10 | 09-29-2016 10:46 PM |
Index match vba | grexcelman | Excel | 0 | 03-05-2015 10:55 PM |
UDF multiple IFs INDEX-MATCH | grexcelman | Excel Programming | 0 | 02-22-2015 04:20 PM |