help with updating formula

[grjnmiller]

HI all

I Need helping fixing up an formula that one of the guys on here helped me with mths ago.

i have the same supplier name in both column "N" & "P".
1 needs top say UNI FIRE and they other should say FIREPLUMB at the moment they both say UNI FIRE. they both have the sell price and that's the problem. How do I fix this

thanks for your help with this

[JohnTopley]

I added a helper row 5:

in B5

=IF(B2="PRICE",B4+COUNTIFS($B$2:B$2,"PRICE",$B$4:B$4,B4)*0.0001,"")

in N4

=INDEX($B$1:$M$1,MATCH(SMALL($B5:$M5,1),$B$5:$M$5,0))

in O4

=INDEX($B$4:$M$4,MATCH(SMALL($B5:$M5,1),$B$5:$M$5,0))

in P4

=INDEX($B$1:$M$1,MATCH(SMALL($B5:$M5,2),$B$5:$M$5,0))


in Q4

=INDEX($B$4:$M$4,MATCH(SMALL($B5:$M5,2),$B$5:$M$5,0))

[JeteMc]

Another option.
The following array entered formula* will work PROVIDING the order of price and supplier is reversed in N1:S1

Please Login or Register  to view this content.

*Array entered formulas are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
After activating this formula in O4 press Ctrl+C to copy then select Q4 and press Ctrl+V to paste paste into S4.
Let me know if you have any questions.

[grjnmiller]

Another option.
The following array entered formula* will work PROVIDING the order of price and supplier is reversed in N1:S1

Please Login or Register  to view this content.

*Array entered formulas are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
After activating this formula in O4 press Ctrl+C to copy then select Q4 and press Ctrl+V to paste paste into S4.
Let me know if you have any questions.

Hi JeteMc

Thanks but when I fill down this happens

[JeteMc]

Filling down will require locking both the column and row references in INDEX as in:

Please Login or Register  to view this content.

Here is the file with the modification applied.
Let me know if you have any questions.

[grjnmiller]

Filling down will require locking both the column and row references in INDEX as in:

Please Login or Register  to view this content.

Here is the file with the modification applied.
Let me know if you have any questions.

thanks that work

why do I have to do this - Array entered formulas are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

why cant I just copy and paste or just type in back in. I tried to just add the $ sign where needed and it didn't like that

[JeteMc]

My understanding is that once you type something into the formula, such as a $ sign, that puts it back in edit mode so that it has to be reactivated. If you just push enter then the formula activates as a regular formula, so to again activate the formula as an array entered formula you need to go through the CSE protocol. After the formula has been activated as array entered, it will copy in that manner. There are probably some members that could give you a better explanation.
To see the difference between how the formula behaves as a regular formula and an array entered formula, put the formula in edit mode by pressing F2, then press enter and run the Evaluate Formula from the formula tab. Next put the formula back in edit mode and activate with CSE. Now run evaluate formula again.
If we have resolved the question about supplier names, please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. Of course let me know if you have any questions. I hope that you have a blessed day.