Index, match, conditional formatting

[kocumna]

Good afternoon,

My name is Nadine and I am looking for help on trying to solve get this formula to work or if you have a better way to help me get what I need, that would be appreciated!

I have two worksheets, one worksheet has a column with claim numbers in it and one is a blank column that I need to have net savings automatically put into.

My second worksheet has the claim numbers that I need to search and find on the first workbook and then return the correct net savings that is in a different column on the second worksheet and once a match is found, I would like the net savings and claim number to turn red. 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.

The below is the formula that I have so far, but I have to change the workbook and worksheet names every time I use this formula. Is it possible to make a formula to index, match and format without having to change the worksheet names every time?

Thank you so much in advance for your help!

Nadine

Please let me know if you can help me with this?


=IFERROR(INDEX('H:\MACROS\[Sample June 2018 HMO - after Clearing – CA only.xlsm]CLEAN_BILLING CA 060818'!$C:$C,MATCH(LEFT(B130,9),LEFT('H:\MACROS\[Sample June 2018 HMO - after Clearing – CA only.xlsm]CLEAN_BILLING CA 060818'!$D:$D,9),)),"")

 

[Chameleon64]

I think your question boils down to the need to refer to external workbooks without editing the formula each time. You could type the workbook name in E1 (just the name, not the filepath), the worksheet name in F1 and the range in G1 then use the INDIRECT function to refer to them as follows:

=IFERROR(INDEX(INDIRECT("'["&E1&"]"&F1&"'!"&G1),MATCH(LEFT(B130,9),LEFT(INDIRECT("'["&E1&"]"&F1&"'!"&G1),9),)),"")

E1: Sample June 2018 HMO - after Clearing – CA only.xlsm
F1: CLEAN_BILLING CA 060818
G1: $C:$C

You will then just need to change cells E1, F1 and G1 each time the workbook changes

Note that this will only work if the external workbook is open as the INDIRECT function doesn't work on closed workbooks.

 

[kocumna]

I type the below formula in but I am getting an error. When I click on the Help key, it's calling it a broken formula. What am doing wrong?

Also, is there a way to turn the Claim number and Net Savings amount Red once the match is found - maybe using the =and function, but I'm not sure if that's correct.

IFERROR(INDEX(INDIRECT("'["&Sample June 2018 HMO - after Clearing - CA Only.xlsm&"]"&CLEAN_BILLING CA 060818&"'!"&$C:$C),MATCH(LEFT(B6,9),LEFT(INDIRECT("'["&Sample June 2018 HMO - after Clearing - CA Only. xlsm&"]"&CLEAN_BILLING CA 060818&"'!"&$C:$C),9),)),"")

Is that a way that I can attach the spreadsheet to show you what I am trying to do?

 

[Chameleon64]

You need to enter the values in cells E1, F1 and G1 as I indicated, then use the formula I gave you. What you've done is just type the filename etc. into the formula again (and without quotes in the correct place which is why you get an error).

You can turn a cell red if it has a value by using Conditional Formatting.

 

[kocumna]

I did as you have instructed, sorry for the misunderstanding, but it's still not working. Would you please explain how this formula matches the claim number in the CLEAN_BILLING CA 060818 worksheet to the Aetna Par CA worksheet and then enters in the Net savings amount from the CLEAN_BILLING CA 060818 worksheet?

Thank you for your patience!