Need to fix IF AND formula for Windows Excel 365 MSO(Version 2308)

Hi Guys,

I really need help on this formula: =IF(AND(L9=0,N9<0),'Open AR'!C4,'Unapplied Cash & Proposed Appl'!H9)
I am trying to use the credits to clear as many invoices until the credit balance is 0. My formula states " if the invoice balance = o, and the remaining receipt amount is less than 0(meaning there's still a credit balance left), pull the next invoice number on the open AR sheet to the apply to invoice column. If false, reuse the same invoice until the invoice equals 0." The problem I have with this formula is the true statement (Open AR'!C4,), continues to move to the next row even when the same invoice number is being repeated. So finally when the invoice equals 0, instead of it using the next invoice which would be C5, it will skip all the invoices and use whatever invoice is on the row when I do the autofill. I just need Open AR'!C4 to stay constant if the same invoice is being repeated, then when a new invoice is pulled to change to the next corresponding row.

Administrative Note:

Welcome to the forum.

Is your forum profile showing the Excel PRODUCT that you need this to work for?

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

What is the PRODUCT?

No. The product: is it Excel 2019, Excel 2021 or Excel 365?

Your file is FULL of error messgaes, as the formulae in it refer to other files which we do not have access to. Please remove those external links AND show us your expected answers, calculated manually. We can do NOTHING with your current sample file.

It’s Excel 365 MSO( Version 2308)

That needs to be added to your forum profile, please. If it's for Mac, add that, too.

Then provde an amended workbook sample as requested in post #8.

Hi Guys,

I hope I followed your instructions correctly. I've attached the sample version and a word doc explaining the formulas and the errors I am running into.

Try the following formulas:
For column A:

Formula:

Please Login or Register  to view this content.

Note that for long receipt numbers that don't start with zero you may need to format the cell as Number with no decimal places (see cell A49)
For column E:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.