Hello,
Would you please tell me how I can match the figures from one spreadsheet to the other in two different workbooks and if it matches, turn the figure red?
Would I do this with Conditional Formatting?
Spreadsheets are attached.
Hello,
Would you please tell me how I can match the figures from one spreadsheet to the other in two different workbooks and if it matches, turn the figure red?
Would I do this with Conditional Formatting?
Spreadsheets are attached.
Last edited by kocumna; 06-12-2018 at 05:26 PM.
Before I open either of those files - are we talking workSHEETS (tabs) or workBOOKS (files) here?
and yes, CF is probably what you need to use
Also, please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hi kocumna,
I thought Conditional Formatting only worked on a single sheet but I'm ready to be wrong on this. There are add-in products that claim they do this. See:
https://www.extendoffice.com/documen...ing-rules.html
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Marvin, after 2007, it would work directly across sheet (within the same file)
2007 and before, it would still work across sheets, but you needed to create a named range for the "compare to" cell
Not sure about across files, I will give that a quick test
Nope, wont work (directly, at least) across files, will test using named ranges
Nope, doesnt seem to want to work using named ranges, either
Good evening,
I asked the below question on another thread titled Index, match, len. I am looking to perform this function and once the net savings are found on the Sample CLEAN_BILLING 051818 after Clearing.xlsm spreadsheet that are on the Sample May 2018 HMO Traditional thru 051818 after Clearing - CA Only. XLSX spreadsheet, I would like the figures to turn red so I can see which ones didn't match.
Please let me know if that makes sense. I am using Excel 2016 working with two separate workbooks.
1. Index, match, len
Good afternoon,
My name is Nadine and I am looking for help on trying to solve get this formula to work or you have a better way to help me get what I need, that would be appreciate!
I have two workbooks, one being larges and have several rows and columns. One column has Claim numbers in it (there are several rows before that column) and one is a blank column that I need to have net savings automatically put into. There are several columns in between these two.
My second workbook has the claim numbers that I need to search and find on the first workbook and then return then return the correct net savings that is in a different column on this second workbook. I only can search 9 out of the 11 digits that are in the claim number due to the last two digits are different in each workbook.
Thank you so much in advance for your help!
Nadine
=INDEX('[CORVEL_CLEAN_BILLING 051818 after Clearing.xlsm]CORVEL_CLEAN_BILLING CA 051818'!$G$2:$G$9, MATCH(LEFT('[CORVEL_CLEAN_BILLING 051818 after Clearing.xlsm]CORVEL_CLEAN_BILLING CA 051818'!$H$2,LEN('[CORVEL_CLEAN_BILLING 051818 after Clearing.xlsm]CORVEL_CLEAN_BILLING CA 051818'!$H$2)-2),LEFT(E96:E283,LEN(Claim_number_column: E96:E283)-2),0))
Hi Nadine,
Enter the following array formula into C6 cell of in Net saving column using Shift + Ctrl + Enter:
=IFERROR(INDEX('[Sample CLEAN_BILLING 051818 after Clearing.xlsm]CLEAN_BILLING CA 051818'!$D:$D,MATCH(LEFT(A6,9),LEFT('[Sample CLEAN_BILLING 051818 after Clearing.xlsm]CLEAN_BILLING CA 051818'!$E:$E,9),)),"")
I would also like to know if this can be done without naming the workbooks/worksheets. Currently I have to change the workbook/worksheet names every time I want to run this formula.
I am also wondering if this can be set as a macro.
Thank you so much for your help!
Nadine
Last edited by kocumna; 06-12-2018 at 07:45 PM.
I am using Microsoft Office Standard 2016 32-bit. I hope that helps.
You didn't read post#8
ver.jpg
Okay. It is fixed now. Thank you for showing me that.
You are welcome and thanks. Now will be easier for everyone
@ sandy, thanks for the assist
.
Good morning!
Any luck on figuring this one out?
TIA,
Nadine
Nadine, to begin with, this is not the same question as you started the thread with, so it should have been a new thread.
2nd, if you have already posted this in a different thread, you need to stick with that thread until it has been resolved, not start a new thread or add to an existing thread with that same question.
Please provide the link to that question, here, and we can continue there.
https://www.excelforum.com/excel-for...match-len.html
My apologies. I did it that way because I wasn't sure how to get the link. Please advise if the above is what you are looking for.
Thank you!
Yes, that is perfect, thanks
Seems like you have your answer there anyway?
It doesn't tell me how I can turn the claim numbers red when I find a match. This was a new question to this thread.
It also doesn't tell me how it can be changed so that I don't have to change the worksheet name every time.
Can you help me with that?
Last edited by kocumna; 06-14-2018 at 07:32 AM.
Good morning!
Any luck on figuring this one out?
TIA,
Nadine
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks