Maximum Number of Characters in an Excel (2010) Formula = 8,192

[rimist]

It's scary that you know that, Mike.

-Rimist (via Tapatalk)

[Mike Hill]

At least I found something that I know and you don't.....made my day.

Everyone on this thread, their knowledge.....scares the bejeeves out me.

I'm still a student in all regards - pre K(indergarten) to be exact.

Let me know when I graduate to 1st grade.

MH

[DanHouston]

Might I recommend you modify the formula from

=A1+A2+A3+A4+A...

to

= sum(A1:A5000)

 

Now that's funny, but at the same time I have seen that before! 

[Mike Hill]

I have extensive knowledge in Excel - on another note.  If you need me to do any formula configurations, pivot tables, I may be able to assist.  I can work up a quick spread sheet with fill in the blanks, pull down tabs, filters, etc.

That's about all I can offer to those less informative......can some teach me the ways of soldering???  LOL
 

That would be a fair trade.....I think

[rm357]

Macros are only dangerous when you receive a spreadsheet that is infected and run the macro.

Writing macros for your own use is perfectly safe.

If you don't know about a spreadsheet that someone has sent to you, the safe thing is to disable the macros (unless you have disabled the prompt, excell will ask when you try to open the file), then open the file and look at what they have written.

RM

[rimist]

Macros are only dangerous when you receive a spreadsheet that is infected and run the macro.

Writing macros for your own use is perfectly safe.

If you don't know about a spreadsheet that someone has sent to you, the safe thing is to disable the macros (unless you have disabled the prompt, excell will ask when you try to open the file), then open the file and look at what they have written.

RM

and if there is anything you don't understand, don't run it!

-Rimist (via Tapatalk)

[kgustafson]

All the macros in the spreadsheets I have posted (and will continue to post) are completely developed by myself.  Just in case any of yall use the sheets I post.

Kurt

[NavyGator]

All the macros in the spreadsheets I have posted (and will continue to post) are completely developed by myself.  Just in case any of yall use the sheets I post.

Kurt

Haha...I wasn't worried about yours but I do know you can download them and set yourself up for failure if your not careful.

[knguyen916]

Wow scary. I think it's time to create your own visual basic module =D. Editing that formula could be a pain and validation can and may be off   

i'm curious to see this formula 

I probably shouldn't do this...but....here it is. Well 1/3 of it anyways.

It is basically nested if statements. In hind sight there was a couple of other options to do this.

[rm357]

I looked at your code a little and still wonder what it is you are trying to do.

Excel has som really neat features using named ranges and the "SUMIF" command that might be better for what you are trying to do...

Filters are also very powerful and there is a conditional sum that works with them as well.

RM

[NavyGator]

I looked at your code a little and still wonder what it is you are trying to do.

Excel has som really neat features using named ranges and the "SUMIF" command that might be better for what you are trying to do...

Filters are also very powerful and there is a conditional sum that works with them as well.

RM

That is part of the logic statement I made to choose shipping options for a COOP. If someone orders 1 kit that will fit in a medium box it checks to make sure that it can't fit in a small box then moves on to the next box size, etc. Then it looks at how many kits it can fit in the box, say 1-3 for a medium box. If someone orders 4 kits it checks to see if it can fit in the medium box based on the number of kits. Then it goes to the next larger size box...etc. It then returns a shipping cost based on the number of kits chosen. If the number of kits exceeds the maximum amount that can be held in the largest box it returns an error to let you know it needs to be looked at.

The reason it has basically the same nested loop repeated is when the COOP manager chooses which COOP they are going to do it associates the shipping parameters.

The reason this is only one third of the code is for US, CAN/MEX and International shipping.

I know it is ugly but I am trying to see the feasibility of how it will all work to see if it needs further development.

Rich

[Kwajtony]

All I can say is I see enought excel formulas at work, this just re-established my headache 

[Penfold]

Maybe you should put in a table and then use a lookup table to the criteria of the price breaks you are after.  Then infuse that query into your if, then nested function.  It might help you out substantially.  You would just need to correct the price break table when prices change.  For instance IF(B2>50,Vlookup(table,column),"").  I think a table would help your query greatly cut down the data inputs.

[NavyGator]

Maybe you should put in a table and then use a lookup table to the criteria of the price breaks you are after.  Then infuse that query into your if, then nested function.  It might help you out substantially.  You would just need to correct the price break table when prices change.  For instance IF(B2>50,Vlookup(table,column),"").  I think a table would help your query greatly cut down the data inputs.

Yeah.....I actually did that already and had added an item to the list and didn't sort it so it didn't work. That is when I decided to use muscle instead of....

Anyways, I finished removing all of the nested if statements out yesterday and I am using the lookup function now.

Rich

[Kwajtony]

I see you have it working, congrats man!