Search code examples
excelexcel-formulaformula

Multiple Cells Check Without VBA?

So, trying to explain what I need here is my two sheets on the same workbook, Sheet Example:

enter image description here

I need to make a formula for each cell that checks Sheet A "Num_Doc_Ini" and "Num_Doc_Fin" against Sheet B "CHV_CTE_REF", calculates the total after adding up the "VL_DOC" numbers on Sheet B, and finally Checks with "correct" or "Incorrect" after comparing the result to the Sheet A "VL_DOC".

So for example, the second line of Sheet A. I need to make so that check see's the NUM_DOC_INI and FIN so "101 - 102" checks those numbers on Sheet B "CHV_CTE_REF", adds up the "VL_DOC" so in this example 4,159.86 + 4,585.1 that equals = 8,744.96, and then matches it with the "VL_DOC" on Sheet A displaying in this case the text "Correct" and If it does not match displays "Incorrect".

Some lines have few docs to reach like "106 - 108" but there are some that have 10 or more to read and sum up the values, because of that I'm really breaking my mind trying to come up with something.

Is that even possible? I can think of a few ways to use VBA for this but I'm trying to do this all with Cells Formula and because of that I am kinda lost, any help would be appreciated thanks!

Edit: I'm using excel 2016 atm. So here is a file for the workbook: https://mega.nz/file/LgVCBbjT#xJnf2HbHd6wdRSNPY7wXSssKv2jZ1-pMsOuKcePlulQ

And the explanation with colors: I need on the Red Cell a formula to check the numbers of the Blue ones on sheet 1, find them on Sheet 2 (blue cells), add up the VL_DOC (Green ones) on sheet 2, and check them up against the values on Sheet 1 Orange cells (VL_DOC), then print out Correct or incorrect based on the match of the values.

Solution

  • SUM, INDIRECT, ADDRESS, MATCH

    • Note that INDIRECT is a volatile function.

    • In cell K2 of Sheet 1 of the example workbook, you can use the following formula:

    =IFERROR(IF(SUM(INDIRECT(ADDRESS(MATCH(H2,Tabela1[NUM_DOC],0)+1,COLUMN(Tabela1[VL_DOC]),1,1,"Sheet 2")&":"&ADDRESS(MATCH(I2,Tabela1[NUM_DOC],0)+1,COLUMN(Tabela1[VL_DOC]),1,1),TRUE))=J2,"Correct","Incorrect"),"")