Make this formula shorter

**Challebjoern** · 10-12-2015, 05:07 AM

=OM(OCH(B8;C9;F8);DIV!J2)--OM(OCH(B9;C9;F9);DIV!J3)--OM(OCH(B10;C9;F10);DIV!J4)--OM(OCH(B11;C9;F11);DIV!J5)--OM(OCH(B12;C9;F12);DIV!J6)--OM(OCH(B13;C9;F13);DIV!J7)--OM(OCH(B14;C9;F14);DIV!J8)--OM(OCH(B15;C9;F15);DIV!J9)--OM(OCH(B8;C10;F8);DIV!J11)--OM(OCH(B9;C10;F9);DIV!J12)--OM(OCH(B10;C10;F10);DIV!J13)--OM(OCH(B11;C10;F11);DIV!J14)--OM(OCH(B12;C10;F12);DIV!J15)--OM(OCH(B13;C10;F13);DIV!J16)--OM(OCH(B14;C10;F14);DIV!J17)--OM(OCH(B15;C10;F15);DIV!J18)--OM(OCH(B8;C11;F8);DIV!J20)--OM(OCH(B9;C11;F9);DIV!J21)--OM(OCH(B10;C11;F10);DIV!J22)--OM(OCH(B11;C11;F11);DIV!J23)--OM(OCH(B12;C11;F12);DIV!J24)--OM(OCH(B13;C11;F13);DIV!J25)--OM(OCH(B14;C11;F14);DIV!J26)--OM(OCH(B15;C11;F15);DIV!J27)--OM(OCH(B8;C12;F8);DIV!J29)--OM(OCH(B9;C12;F9);DIV!J30)--OM(OCH(B10;C12;F10);DIV!J31)--OM(OCH(B11;C12;F11);DIV!J32)--OM(OCH(B12;C12;F12);DIV!J33)--OM(OCH(B13;C12;F13);DIV!J34)--OM(OCH(B14;C12;F14);DIV!J35)--OM(OCH(B15;C12;F15);DIV!J36)--OM(OCH(B8;C13;F8);DIV!J38)--OM(OCH(B9;C13;F9);DIV!J39)--OM(OCH(B10;C13;F10);DIV!J40)--OM(OCH(B11;C13;F11);DIV!J41)--OM(OCH(B12;C13;F12);DIV!J42)--OM(OCH(B13;C13;F13);DIV!J43)--OM(OCH(B14;C13;F14);DIV!J44)--OM(OCH(B15;C13;F15);DIV!J45)--OM(OCH(B8;C14;F8);DIV!J47)--OM(OCH(B9;C14;F9);DIV!J48)--OM(OCH(B10;C14;F10);DIV!J49)--OM(OCH(B11;C14;F11);DIV!J50)--OM(OCH(B12;C14;F12);DIV!J51)--OM(OCH(B13;C14;F13);DIV!J52)--OM(OCH(B14;C14;F14);DIV!J53)--OM(OCH(B15;C14;F15);DIV!J54)--OM(OCH(B8;C15;F8);DIV!J56)--OM(OCH(B9;C15;F9);DIV!J57)--OM(OCH(B10;C15;F10);DIV!J58)--OM(OCH(B11;C15;F11);DIV!J59)--OM(OCH(B12;C15;F12);DIV!J60)--OM(OCH(B13;C15;F13);DIV!J61)--OM(OCH(B14;C15;F14);DIV!J62)--OM(OCH(B15;C15;F15);DIV!J63)--OM(OCH(B8;C16;F8);DIV!J65)--OM(OCH(B9;C16;F9);DIV!J66)--OM(OCH(B10;C16;F10);DIV!J67)--OM(OCH(B11;C16;F11);DIV!J68)--OM(OCH(B12;C16;F12);DIV!J69)--OM(OCH(B13;C16;F13);DIV!J70)--OM(OCH(B14;C16;F14);DIV!J71)--OM(OCH(B15;C16;F15);DIV!J72)--OM(OCH(B8;C18;F8);DIV!J74)--OM(OCH(B9;C18;F9);DIV!J75)--OM(OCH(B10;C18;F10);DIV!J76)--OM(OCH(B11;C18;F11);DIV!J77)--OM(OCH(B12;C18;F12);DIV!J78)--OM(OCH(B13;C18;F13);DIV!J79)--OM(OCH(B14;C18;F14);DIV!J80)--OM(OCH(B15;C18;F15);DIV!J81)--OM(OCH(B8;C19;F8);DIV!J83)--OM(OCH(B9;C19;F9);DIV!J84)--OM(OCH(B10;C19;F10);DIV!J85)--OM(OCH(B11;C19;F11);DIV!J86)--OM(OCH(B12;C19;F12);DIV!J87)--OM(OCH(B13;C19;F13);DIV!J88)--OM(OCH(B14;C19;F14);DIV!J89)--OM(OCH(B15;C19;F15);DIV!J90)--OM(OCH(B8;C20;F8);DIV!J92)--OM(OCH(B9;C20;F9);DIV!J93)--OM(OCH(B10;C20;F10);DIV!J94)--OM(OCH(B11;C20;F11);DIV!J95)--OM(OCH(B12;C20;F12);DIV!J96)--OM(OCH(B13;C20;F13);DIV!J97)--OM(OCH(B14;C20;F14);DIV!J98)--OM(OCH(B15;C20;F15);DIV!J99)--OM(OCH(B8;C21;F8);DIV!J101)--OM(OCH(B9;C21;F9);DIV!J102)--OM(OCH(B10;C21;F10);DIV!J103)--OM(OCH(B11;C21;F11);DIV!J104)--OM(OCH(B12;C21;F12);DIV!J105)--OM(OCH(B13;C21;F13);DIV!J106)--OM(OCH(B14;C21;F14);DIV!J107)--OM(OCH(B15;C21;F15);DIV!J108)--OM(OCH(B8;C22;F8);DIV!J110)--OM(OCH(B9;C22;F9);DIV!J111)--OM(OCH(B10;C22;F10);DIV!J112)--OM(OCH(B11;C22;F11);DIV!J113)--OM(OCH(B12;C22;F12);DIV!J114)--OM(OCH(B13;C22;F13);DIV!J115)--OM(OCH(B14;C22;F14);DIV!J116)--OM(OCH(B15;C22;F15);DIV!J117)--OM(OCH(B8;C23;F8);DIV!J119)--OM(OCH(B9;C23;F9);DIV!J120)--OM(OCH(B10;C23;F10);DIV!J121)--OM(OCH(B11;C23;F11);DIV!J122)--OM(OCH(B12;C23;F12);DIV!J123)--OM(OCH(B13;C23;F13);DIV!J124)--OM(OCH(B14;C23;F14);DIV!J125)--OM(OCH(B15;C23;F15);DIV!J126)--OM(OCH(B8;C25;F8);DIV!J128)--OM(OCH(B9;C25;F9);DIV!J129)--OM(OCH(B10;C25;F10);DIV!J130)--OM(OCH(B11;C25;F11);DIV!J131)--OM(OCH(B12;C25;F12);DIV!J132)--OM(OCH(B13;C25;F13);DIV!J133)--OM(OCH(B14;C25;F14);DIV!J134)--OM(OCH(B15;C25;F15);DIV!J135)--OM(OCH(B8;C26;F8);DIV!J137)--OM(OCH(B9;C26;F9);DIV!J138)--OM(OCH(B10;C26;F10);DIV!J139)--OM(OCH(B11;C26;F11);DIV!J140)--OM(OCH(B12;C26;F12);DIV!J141)--OM(OCH(B13;C26;F13);DIV!J142)--OM(OCH(B14;C26;F14);DIV!J143)--OM(OCH(B15;C26;F15);DIV!J144)--OM(OCH(B8;C27;F8);DIV!J146)--OM(OCH(B9;C27;F9);DIV!J147)--OM(OCH(B10;C27;F10);DIV!J148)--OM(OCH(B11;C27;F11);DIV!J149)--OM(OCH(B12;C27;F12);DIV!J150)--OM(OCH(B13;C27;F13);DIV!J151)--OM(OCH(B14;C27;F14);DIV!J152)--OM(OCH(B15;C27;F15);DIV!J153)

Does anyone got a better way to type this? xD

The OM - Means IF
The OCH - Means AND

**NeedForExcel** · 10-12-2015, 05:50 AM

What is the formula doing?

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**Challebjoern** · 10-12-2015, 06:03 AM

That code takes status of 1 & 2,To verify that certain boxse are checked and then takes price from sheet number 2 (called DIV)
Then it displays the price up in the collum Above "Containertömning". It works great this way, but would be fun to know if there's any better way to do this.

ExcelDumper.jpg

**Challebjoern** · 10-12-2015, 06:26 AM

Containerkostnad V31.xlsm

Copy of the file.

**jason.b75** · 10-12-2015, 08:59 AM

I started with just columns B and C to simplify the process.

This works with LDO5 but nothing else, there is probably a way to make this work, but I'm just not seeing it (yet).

Formula:

Please Login or Register  to view this content.

Note that I had to insert blank cells at C18 and C25 so that the checkboxes lined up with DIV!C3:C23.

**jason.b75** · 10-13-2015, 07:28 AM

Just had another look at this and found the problem straight away.

Where I was referencing your existing columns, I hadn't noticed that you had some slight variations which were causing the formula to miss matches ("Zon 1" in one column, and "Zon1" in another), the space in the middle makes a lot of difference with a formula.

After a quick tidy up to get the 2 sets of records in the same format, the formula matches your results.

I've attached the workbook with the formula and the changes I needed to make highlighted in yellow. The formula is down the page slightly in F19, seemed like a convenient place to put it to keep both formula and the checkboxes on the screen for comparing results.

**Challebjoern** · 10-13-2015, 08:10 AM

Smooooth! Looks way better, and the best thing is that I think I know how to write a code the better way in the future. Big thanks all of you, and especially you Jason!

One thing tho, when I mark it and just press on the formula the " { } " signs disapear and the formula stops working, any idea why it does that?

Also a question, is there anyway to make the text in the checkboxes "BOLD" when marked?

**jason.b75** · 10-13-2015, 08:45 AM

It's an array formula, so if you click into it you need to press Shift Ctrl and Enter to confirm it again.

Changing the font on the boxes would need vba, possibly something like this example (which I know is wrong, but will hopefully get you looking in the right direction).

Please Login or Register  to view this content.

I'll have another look at it later if you have no luck with it, but I'm a bit pressed for time at the moment.

**Alf** · 10-13-2015, 12:14 PM

@Callebjoern

Glad att se Renova nyttjar gratis forum kunskaper slik att vi konsumenter slipper hoeyda avgifter!! Bra!!!

Alf

Ps Klicka hans stjaerna da gir du honom bonus.

**Challebjoern** · 10-14-2015, 01:31 AM

@Alf

Detta är ett hjälpmedel jag själv implementerat för att se till att kunderna alltid får till sig rätt information, men håller med dig, en expert hade säkert tagit några tusen för att göra detta. Har get honom en stjärna 3 gånger ,)

**jason.b75** · 10-14-2015, 06:43 AM

Had another look at changing checkbox font, that can only be done with activex checkboxes, not with form control checkboxes (which is what you have).

If you right click one of your checkboxes, then go to 'Format Control', then 'Colors and Lines', the changes you can make here will be what you are limited to.

**Challebjoern** · 10-14-2015, 07:47 AM

Ok, well, i'll survive without that function, just wanted to know if it was possible, was googling around but noone really had a great answer for it.

Another thing tho, most likely my last favor. Is it possible to add percentage discount in the total price? Like for ex. if I make a cell where i want set like 10% and that it should show in the totalprice that it's been a 10% discount on that section.

Something like this-

Edit:
For example like this- (The section where you see (*1,25) is that it add taxes to the price.
=SUMMA.OM(C9:C16:C19:C24:C27:C29;SANT;DIV!D3:D23)*(1,25)(Take % from C4 annd show the discount in total)

**jason.b75** · 10-14-2015, 07:56 AM

That should be possible in a generic formula by changing

=anyformula(lots of numbers)

to

=anyformula(lots of numbers)*(1-x)

Where x refers to the discount percentage.

**Challebjoern** · 10-14-2015, 08:11 AM

Worked great, big thanks Jason!

**Challebjoern** · 10-14-2015, 08:59 AM

Finally done, one thing tho that would be neat to fix. Is it possible to add to the button that says "Tryck för att nollställa" wich means click here to reset. To add that it also resets the percentage %%% cells, is that doable?

Containerkostnad V3.2.xlsm

**jason.b75** · 10-14-2015, 09:17 AM

A slightly less conventional method (but it works). Link your button to this code. I've started it off, but you will need to add in the rest of the ranges. Note that all ranges are enclosed within one set of double quotes.
The ranges should include any cells (not formula) that should be set to 0, and the link cells for any checkboxes that should be off. (0 = false, which turns it off, 1 = true which turns it on).

Please Login or Register  to view this content.

**Challebjoern** · 10-15-2015, 01:28 AM

I only had to add Range("B4:G4") since I already got a command for reseting all the checkboxes, but thanks, it worked great with your code!

Also wanna thank you big time for all the help you put into this, I think the tool is finally done and I hope we get to use it the right way.

Make this formula shorter

LinkBack

Thread Tools

Rate This Thread

Display

Make this formula shorter

Re: Make this formula shorter

Re: Make this formula shorter

Re: Make this formula shorter

Re: Make this formula shorter

Re: Make this formula shorter

Re: Make this formula shorter

Re: Make this formula shorter

Re: Make this formula shorter

Re: Make this formula shorter

Re: Make this formula shorter

Re: Make this formula shorter

Re: Make this formula shorter

Re: Make this formula shorter

Re: Make this formula shorter

Re: Make this formula shorter

Re: Make this formula shorter

Thread Information

Users Browsing this Thread

Similar Threads

How to make this formula shorter

Want to make formula shorter and more efficient by hopefully using a loop funtion.

Make a formula shorter

Alternative Formula for multiples IFs (to make my formula shorter)

Is there a way to make my code shorter?

Make VBA code shorter

[SOLVED] How to make formula shorter ?

Bookmarks

Bookmarks

Posting Permissions