Posts by Fotis1991

• Select Totals and Display them in Separate Sheet

Re: Select Totals and Display them in Separate Sheet

Ok. So in A2 of your second sheet put this formula.

=LOOKUP(REPT("Z";5);CHOOSE({1\2};"";INDEX(Sheet1!\$A\$1:\$A\$20;MATCH(TRUE;INDEX(ISNA(MATCH(Sheet1!\$A\$1:\$A\$20;A\$1:A1;0));0);0);1)))

In B2, this one.

=SUMPRODUCT((Sheet1!\$A\$1:\$A\$20=A2)*(Sheet1!\$B\$1:\$B\$20="Total")*(Sheet1!\$C\$1:\$C\$20))

Copy down both of these as you need.

Change ALL semi colons in my formulae to comma, if you neeed to do this(see my signature)

Does this works for you?

• Select Totals and Display them in Separate Sheet

Re: Select Totals and Display them in Separate Sheet

A B C

Sales
Sales Account1 10

Are your data in 3 differents column like this OR all together in one column??

• Multi-Level Data Validation Problem

Re: Multi-Level Data Validation Problem

You use 2 named ranges with names, “Klause_19_9” & “Hendrick_27_7

1) Change these using the new names

2) Change the formula that you use in the Validation List in cell B17 of Report Sheet.

• If a Looked Up MAX repeats, return the multiple values from corresponding rows

Re: If a Looked Up MAX repeats, return the multiple values from corresponding rows

You are welcome! Thanks for the feedback.

Quote

...Is there any way to replace the cells displaying #NUM! with " " ?

=IFERROR(My formula),"")

=iferror(INDEX(\$B\$1:\$B\$12;SMALL(IF(\$C\$1:\$C\$12=1;ROW(\$B\$1:\$B\$12));ROW(B1)));"")

Change the semi colons to comma, if you have to do this.

• Formula Publishing Multiple Entries Originating From Multiple Criteria

Re: Formula Publishing Multiple Entries Originating From Multiple Criteria

You are welcome! Thanks for the feed back.

Quote

...Will this mess up the formula?

To be honest..i don't know!! I never did it. Test it yourself and let me know too!

• If a Looked Up MAX repeats, return the multiple values from corresponding rows

Re: If a Looked Up MAX repeats, return the multiple values from corresponding rows

In this case we do this.

In C1 and copy down this formula.

=IF(A1=LARGE(\$A\$1:\$A\$12;1);1;IF(A1=SMALL(\$A\$1:\$A\$12;1);2;""))

This will be a helper and hidden column.

In F1, this ARRAY formula

=INDEX(\$B\$1:\$B\$12;SMALL(IF(\$C\$1:\$C\$12=1;ROW(\$B\$1:\$B\$12));ROW(B1))) >>>for max

=INDEX(\$B\$1:\$B\$12;SMALL(IF(\$C\$1:\$C\$12=1;ROW(\$B\$1:\$B\$12));ROW(B1)))>>>for min.

Does this works for you?

• If a Looked Up MAX repeats, return the multiple values from corresponding rows

Re: If a Looked Up MAX repeats, return the multiple values from corresponding rows

Try

=INDEX(\$B\$1:\$B\$12,MATCH(MAX(\$A\$1:\$A\$12),\$A\$1:\$A\$12,0)) >>>for max Name

=INDEX(\$B\$1:\$B\$12,MATCH(Min(\$A\$1:\$A\$12),\$A\$1:\$A\$12,0)) >>>for min Name.

• Get the next possible larger value next to the current value

Re: Get the next possible larger value next to the current value

Yes. keep in mind for the next time.... This time I was obliged (as a good servant) to do .. No need to thank me for it. it was just .. my job.:0ops:

From simple curiosity, what was ultimately the solution you wanted?

• Conditional Formatting

Re: Conditional Formatting

I prefer to don't use multiply IF(or for eatch condition, so i use 1 helper and hidden column to create a table and then a simple INDEX&MATCH function in eatch of the Conditional Formatting rules.

=INDEX(D2:D21,MATCH(B25,B2:B21,0))=1

Does this works for you?

• Get the Maximum Value and its corresponding cell

Re: Get the Maximum Value and its corresponding cell

You are welcome! Thanks for the feedback!

• Formula Publishing Multiple Entries Originating From Multiple Criteria

Re: Formula Publishing Multiple Entries Originating From Multiple Criteria

Quote

....Can you put your formula in my attachment TEST.xlsx and reupload it?

I have done already this in my post#2! Pls,look my attachment in that post.

• Formula Publishing Multiple Entries Originating From Multiple Criteria

Re: Formula Publishing Multiple Entries Originating From Multiple Criteria

Sorry but this make no sense for me. My formula DOES exactly this.

Good luck!

• Formula Publishing Multiple Entries Originating From Multiple Criteria

Re: Formula Publishing Multiple Entries Originating From Multiple Criteria

Quote

....I tried using the formula you created and it did not work for me

Can you pls let us know which are the expected results in the yellow columns, if not the results that my formula gives?:question:

Quote

... I need these formulas to fit into a merged cell.

As i told, ARRAY formulae don't work in merged cells... I believe that if you don't want to un-merge your cells, then you'll need helper(s) columns for this.--i mean except of VBA-. But in any case everybody that will try to help you, will need to know the expected results.

• Formula Publishing Multiple Entries Originating From Multiple Criteria

Re: Formula Publishing Multiple Entries Originating From Multiple Criteria

This is an ARRAY formula and does what you need.

BUT: Arrays formula don;t like at all the merged cells....

• Get the next possible larger value next to the current value

Re: Get the next possible larger value next to the current value

Quote

...If a value ex.98 (A COLUMN) matches with the value with 98(B COLUMN),it should pick up my second higher value 103

Formula does this.

IF A2=B2, then will give you the next higher value of B2. IF a2<>b2, then will gives B2 value.

In your last example no cell of column B matces with cell in column A. So the result is the value of column B.

Without any condition this formula in C2 and copy down, gives your expected results.

=SMALL(\$B\$2:\$B\$23,ROW(B2))

Edit: Or..

=IF(MATCH(A2,\$B\$2:\$B\$23,0),SMALL(\$A\$2:\$A\$23,1+COUNTIF(\$A\$2:\$A\$23,"<="&A2)),A2)

• Get the next possible larger value next to the current value

Re: Get the next possible larger value next to the current value

=IF(A2=B2,SMALL(\$B\$2:\$B\$23,1+COUNTIF(\$B\$2:\$B\$23,"<="&B2)),B2)

@ StephenR

Actually he did it! :yourock:

• Get the next possible larger value next to the current value

Re: Get the next possible larger value next to the current value

In this case just use an if function in D2 and copy down.

=IF(A2=B2,B3,B2)

• Get the Maximum Value and its corresponding cell

Re: Get the Maximum Value and its corresponding cell

=max(c1:c20)

=index(b1:b20;match(b24;c1:c20;0))

• Get the Maximum Value and its corresponding cell

Re: Get the Maximum Value and its corresponding cell

Can you pls upload a small sample workbook and not a picture?