Solved Hide formulas

[Slartybart]

Sooooooo close - I could say that I left it as an exercise for the student, but that would be lying

Thanks again Tanya. It's nice that someone who has current working knowledge with Excel is here on the thread. Sven would still be trying to figure out what I was trying to convey if you weren't

Bill
.

 

[Sven1458]

Tanya,
you are amazing, when I started this, I hade no clue where to go. The only thing I could do was write simple formulas.
this in a lot higher on the ladder of Excel, and I am excited to learn more.
I will spend the weekend trying t make this work.
thanks gain for your help

Sven

 

[Sven1458]

Hi Tanya
I put those formulas in and it worked like a charm.
I had to change the column / cell numbers, but got it done.
Now I was wondering if you could give me the formula for the following problem:
The side plate size (J) depends on the total height (D).
Total height up to 170 will need a side plate of 15
Total height up to 240 will need a side plate of 18
Total height up to 350 will need a side plate of 20.5
Can you come up with a formula that will display the correct Side plate size when I type the height?
Thanks for your help,

Sven

 

[TanyaC]

When you say "up to" are you saying that lower values would also get the various side plate size, or are there only 3 allowable total height values.

For example, could I have total height values of 145, 219, 299, 308 and 349 which would give results of 15, 15, 18, 20.5 and 20.5?

What do you want if the total height is greater than 350?

If it's just the three values, the formula is just another nested IF statement.

Eg. If($C5="","",if($D5=170,15,if($D5=240,18,if($D5=350,20.5,"ERR"))))

Thanks
Tanya

 

[Slartybart]

I'll sit back and keep an eye on the thread, for now Tanya has the lead and is in a better position, having Excel, to help you Sven.

@Tanya: you should get a consulting fee from Sven's company... or at least a Tshirt

Good luck and have fun,

Bill
.

 

[Sven1458]

Hi Tanya,
thanks for sticking with me.
to clarify:
any height between 0 and 170 requires a side plate of 15
at 171 up to 240 requires a side plate of 18 and anything beyond that requires a side plate of 20.5. the height limit is 350 and will fit into a 20.5 plate. nothing bigger is manufactured by us. lets say the height is 144, the plate would be 15 if the height is 222, it would be 18, if 308 it would be 20.5
hope this clarifies it.
again thanks for your help

Sven

 

[Sven1458]

Hi Tanya,
I modified your formal: If($C5="","",if($D5=170,15,if($D5=240,18,if($D5=350,20.5,"ERR"))))
to: If($d13="","",if($D13<=170,15,if($D13<=240,18,if($D13<=350,20.5,"ERR"))))

now it does what I want it to do.

Thanks for all your help.
Sorry, we don't have T-shirts and I don't get paid for this either. Just dedication to the company.

Thanks Again.


Sven

 

[Sven1458]

I have another Question:
is it possible that after data input in the first 4 columns, the form must be printed and can not be saved with the data in it? Tis way the form is always empty when you open it.

Thanks
Sven

 

[TanyaC]

Hi Tanya,
I modified your formal: If($C5="","",if($D5=170,15,if($D5=240,18,if($D5=350,20.5,"ERR"))))
to: If($d13="","",if($D13<=170,15,if($D13<=240,18,if($D13<=350,20.5,"ERR"))))
Sven

Well done! Looks like you've got the hang of this stuff.

Tanya

 

[TanyaC]

I have another Question:
is it possible that after data input in the first 4 columns, the form must be printed and can not be saved with the data in it? Tis way the form is always empty when you open it.

Thanks
Sven

Not that I can think of using native excel functions. It is possible with some vba code, but that is probably overkill.

However, you could make the excel file read only, so it couldn't be saved.

 

[Sven1458]

Hi Tanya, thanks for your help.
make it read only, now why did I not think of that.
I think I will enroll in a class that teaches Excel to get more familiar with it. cant hurt.

best regards
Sven

 

[TanyaC]

lol. Good luck with your spreadsheet and excel learning.

 

[RickL1]

I have another Question:
is it possible that after data input in the first 4 columns, the form must be printed and can not be saved with the data in it? Tis way the form is always empty when you open it.

Thanks
Sven

The first thing to do is to SAVE your file in multiple locations so that you will have backup copies that do not have any data entered. You have worked too hard on this to lose it due to a twitch of a finger. I have seen a few instances of the Read-Only being override-able. For the part about always starting with a blank sheet, consider saving the "daily use" copy as a Template. When things work properly in a template, a User can try to Save it and is then required to use a new/different name for the file. File->Save-As->File Type = Template(.xlt)