CODEWITHSUNDEEP
excelspreadsheetexcel-formulaworksheet-function
Andrés Botero
Andrés Botero

Reputation: 1040

Spreadsheet multiple formulas mashup

I’m trying to automate some processes for task management, but I’m having no success. I can’t use macros or similar, just formulas, and I’m not an adept at spreadsheet hacking.

Anyways, here’s my workbook, with its **sheets**:

**Form**
TASK     LI    DE    X
Test     1     3
Test2    2

**LI**
WEEK     TASK  COMPLETED
1        Test
2        Test
2        Test  *
4        Test2 *

**DE**
WEEK     TASK  COMPLETED
1        Test  *

What I’ve been trying to do is:

For this example, in order for X to change, TASK must be with an * in the sheets where it is. For instance, if, on Form, Test has numbers in LI and DE, and Test has an * in LI sheet, but not in DE sheet, X must remain empty. But if both have it with *, X must be loaded with the greater WEEK between LI and DE.

If I were to do it with macros, I would simply check each column with a loop, but with formulas I suppose nested IFs would suffice.

I’ve tried with VLOOKUP, but it only takes the first item in the array, and though the order doesn’t matter, it is generally (I think I will make this a policy) the last value.

Any doubt, just let me know! I hope I made my issue clear.

Thank you very much in advance!

Upvotes: 0

Views: 170

Answers (1)

JMax
JMax

Reputation: 26591

I think you can do it with formula but as you will have to loop, you will need SUMPRODUCT or Array Formula.

Here is a formula you can try (validate with CtrlShiftEnter):

=MAX((LI!$C$2:$C$5="*")*(LI!$A$2:$A$5)*(LI!$B$2:$B$5=Form!A2),(DE!$C$2:$C$5="*")*(DE!$A$2:$A$5)*(DE!$B$2:$B$5=Form!A2))

Some explanation:

  • The MAX formula will find the greatest value between the two ARRAY FORMULA of the two worsheets
  • The array formula works like a multiple loop test:
    • (LI!$C$2:$C$5="*") checks if there is a star in the third column
    • (LI!$A$2:$A$5) will return the week number
    • (LI!$B$2:$B$5=Form!A2) will check if the tasks are the same

I hope I understood well what you intended to do :)

[EDIT] Another try thanks to your comment (both task should be completed to appear)

=IF(AND((LI!$C$2:$C$5="*")*(LI!$A$2:$A$5)*(LI!$B$2:$B$5=Form!A2),(DE!$C$2:$C$5="*")*(DE!$A$2:$A$5)*(DE!$B$2:$B$5=Form!A2))),MAX((LI!$C$2:$C$5="*")*(LI!$A$2:$A$5)*(LI!$B$2:$B$5=Form!A2),(DE!$C$2:$C$5="*")*(DE!$A$2:$A$5)*(DE!$B$2:$B$5=Form!A2)),"")

Upvotes: 2

Related Questions