Formula without sheet names

[kocumna]

Good afternoon,

I have the following formula which works great, but I have to change the worksheet name every time I use it. Is it possible to set up this formula where I don't have to change the worksheet names? Could this formula also be set up as a macro?


=IFERROR(INDEX('[CORVEL_CLEAN_BILLING 051818 after Clearing with Formula.xlsm]CORVEL_CLEAN_BILLING 051818'!$G:$G,MATCH(LEFT(E1286,9),LEFT('[CORVEL_CLEAN_BILLING 051818 after Clearing with Formula.xlsm]CORVEL_CLEAN_BILLING 051818'!$H:$H,9),)),"")

I have also attached the spreadsheets for this formula.

[FDibbins]

I dont see the file you mention in your post? I understand you may have created a dummy file for us, but maybe adjust your formula to show that?

I have just opened both files and cannot see that formula anyway. Nor can I make out what you expect?

[kocumna]

Hi Ford,

I have uploaded the spreadsheets showing the formula that I am using, but I would like it to make the claim numbers and net savings red that have a match. I would also like to change the formula so that I don't have to change the workbook and worksheet names every time I use it.

Here is the formula that I am using.

=IFERROR(INDEX('[Sample CLEAN_BILLING 051818 after Clearing.xlsm]CLEAN_BILLING CA 051818'!$D:$D,MATCH(LEFT(A5,9),LEFT('[Sample CLEAN_BILLING 051818 after Clearing.xlsm]CLEAN_BILLING CA 051818'!$E:$E,9),)),"")

[FDibbins]

1. I still cannot see any formulas in your files (other that the SUM() formula), but not really sure that is relevant anyway.
2. To make cells change color, you would use Conditional Formatting (or VBA), but CF does not work across different files.
3. It looks fo me like you intend having different sheets or files for each date? I would advise against that, rather just put all your data in 1 sheet, each date below the previous date. That will make any analysis MUCH simpler
You could even put both the files you sent me, into the 1 file as well, and have 1 sheet for all data, and another for the x-check info

1 way to do this would be to use MATCH(), in a helper column, then base the CF on that. not sure which file you need to test against, but I used Sample CLEAN_BILLING 051818 after Clearing (2).xlsm
C5=MATCH(A5,'[Sample CLEAN_BILLING 051818 after Clearing (2).xlsm]CLEAN_BILLING CA 051818'!E$2:E$9,0)

Then use CF
1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =$C5
format font as needed

[kocumna]

I'm not sure why it won't keep the formula once I put it in. I have tried several times. The formula that I posted is the formula that I am using in the below workbook that I attached above in post #1.

I need to use separate workbooks because we receive a report every week with different claim number and patients and it is uploaded to new workbooks.

[kocumna]

You mentioned that it can be done using VBA. Would you please tell me how you would do that?