2 Formulas needed!!! :)

[aswasis]

One- I have variety of size sheds that when keyed in a cell I need to
return the sq.ft. or I expect to have to calculate sq.ft and enter each
number and place that in a equation.
Example: 8x8x7, 8x10x7,8x12x7,8x14x7 etc. meaning 8' wide by 8' in
length by 7' tall and so on. The 7' is just added on. I can leave it
off and make a cell to enter this info. 7' is not important in the
equation. Figuring it being a long equation when done.

2nd- On a 3 worksheet I'm counting on the '8x8" the 1st worksheet to
signify to a list of material I assigned to it.
Basically I build sheds. Every time we have to put a wood order in we
have to do a wood list for each shed. for years I wanted to do
something on excel to help us so we have it done once, per shed.
Wish I could return the favor to whoever. Don't know who to turn to!
Thanks for the big help here- Soo appreciated. Family business of 5
here! Thanks




--
aswasis

[Claus Busch]

Hi,

Am Wed, 10 Apr 2013 21:44:57 +0000 schrieb aswasis:

> Example: 8x8x7, 8x10x7,8x12x7,8x14x7 etc. meaning 8' wide by 8' in
> length by 7' tall and so on. The 7' is just added on. I can leave it
> off and make a cell to enter this info. 7' is not important in the
> equation. Figuring it being a long equation when done.

your strings in A1, then in a standard module:

Function sqrfeet(rngc As Range) As Double
Dim myStart As Integer, myEnd As Integer
Dim myStr1 As String, myStr2 As String

myStart = InStr(rngc, "x")
myEnd = InStrRev(rngc, "x")
myStr1 = Left(rngc, myStart - 1)
myStr2 = Mid(rngc, myStart + 1, myEnd - myStart - 1)
sqrfeet = Evaluate(myStr1 * myStr2)
End Function

in the sheet you can call the function with =sqrfeet(a1)

> 2nd- On a 3 worksheet I'm counting on the '8x8" the 1st worksheet to
> signify to a list of material I assigned to it.
> Basically I build sheds. Every time we have to put a wood order in we
> have to do a wood list for each shed. for years I wanted to do
> something on excel to help us so we have it done once, per shed.

To sum the different dimensions you can use SUMIF or SUMPRODUCT


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[Kevin@Radstock]

For what reason would you want to put those values in 1 cell (ie:8x10x7)
it is beyond me!

A1=8, B1=10, C1=7, D1=A1*B1*C1=560.

Kevin@Radstock

[Claus Busch]

Hi,

Am Thu, 11 Apr 2013 09:10:25 +0200 schrieb Claus Busch:

> Function sqrfeet(rngc As Range) As Double

sorry for the sqrfeet. You can change it to inch ;-)

[aswasis]

I will have to work on the zip file. Until then I'm sorry for not able
to explain myself better. I like to try it again. backwards- A lumber
list of sizes in cells vertical (column) and beside them a column list
of prices for each item and another column beside those prices for
quantity.

From the option page of my contract there is a place for the size of the
building, whether its "A" frame or Barn Style, and what type of
siding-vinyl,wood etc.

My plan is if I entered in say just the size (8x8x7) say in a cell from
the contract worksheet, that it would trigger a lumber list for that
shed on a different worksheet. Then when my girls are ready to order it
they would print the wood list and fax it to the store.

My thought is to make up all these types of sanario and combinations and
label them by the size "8x8x7 or what ever size. I could use a number
system or letter system if this would be better. I don't have to have
the size be the trigger to make this happen.
Thanks for interest in this. Amazing working knowledge you all share!



Attach your file here and explain exactly what you want.
Remember, you must *ZIP* your file before attach it to the forum

Put a clear example about the desirable result.

Remember, you are a master of your data but we don愒 know nothing about
them. Then be so clear as possible on your explanation.

I惻l be happy to try to help you and your family in this bussines task.

Have a nice Day.




--
aswasis

[GS]

Put the following function in a standard module...

Public Function SquareArea#(RangeRef As Range)
Dim vAreaSize
If InStr(LCase$(RangeRef.Value), "x") > 0 Then
vAreaSize = Split(LCase$(RangeRef.Value), "x")
SquareArea = vAreaSize(0) * vAreaSize(1)
End If
End Function

..and use it as follows from any cell...

A1: 8x8x7
B1: =squarearea(a1)

Result: 64

..where you determine the scale (inches/feet, mm/cm/meters) by way of
header labels. That way your naming does not need to be customized
further unless you want it that way.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[aswasis]

For what reason would you want to put those values in 1 cell (ie:8x10x7)
it is beyond me!

A1=8, B1=10, C1=7, D1=A1*B1*C1=560.

If you go to a Home Depot or Lowes and ask for a wood shed they will ask
you what size do you need. 8'x10'x7high is just on size of many
combinations of sizes. Generally 2' increments in any one direction. I
can't interpret what you wrote out-you're far above me so I can't tell
what your understanding is on my question by what you wrote. Sorry!




--
aswasis

[GS]

For completeness...

Public Function GetSize#(RangeRef As Range, Optional IsCubic&)
Dim vSizes
'Force lowercase and filter numbers and letter "x" only
'Allows use of alpha prefix for product IDs
vSizes = Split(FilterString(LCase$(RangeRef.Value), "x", False), "x")
GetSize = vSizes(UBound(vSizes) - 2) * vSizes(UBound(vSizes) - 1)
If IsCubic Then GetSize = GetSize * vSizes(UBound(vSizes))
End Function

..which can be used as follows...

A1: 8x8x7

Formula to return area:
B1: =getsize(A1)
Returns: 64

Formula to return volume:
C1: =getsize(A1,1)
Returns: 448

..and will allow use of alpha prefixes...

Part/Model #
AF-8x8x7 OR AF8X8X7 OR DX8X8X7 OR DX-8x8x7 etc.

Function FilterString$(ByVal TextIn As String, _
Optional IncludeChars As String, _
Optional IncludeLetters As Boolean = True, _
Optional IncludeNumbers As Boolean = True)
' Filters out all unwanted characters in a string.
' Arguments: TextIn The string being filtered.
' IncludeChars [Optional] Keeps any characters.
' IncludeLetters [Optional] Keeps any letters.
' IncludeNumbers [Optional] Keeps any numbers.
'
' Returns: String containing only the wanted characters.

'The basic characters to always keep
Const sLetters As String = "abcdefghijklmnopqrstuvwxyz"
Const sNumbers As String = "0123456789"

Dim i As Long, CharsToKeep As String

CharsToKeep = IncludeChars
If IncludeLetters Then _
CharsToKeep = CharsToKeep & sLetters & UCase(sLetters)
If IncludeNumbers Then CharsToKeep = CharsToKeep & sNumbers
'From a Balena sample
For i = 1 To Len(TextIn)
If InStr(CharsToKeep, Mid$(TextIn, i, 1)) Then _
FilterString = FilterString & Mid$(TextIn, i, 1)
Next
End Function 'FilterString()

[aswasis]

'GS[_2_ Wrote:
> ;1611181']For completeness...

>
> Public Function GetSize#(RangeRef As Range, Optional IsCubic&)
> Dim vSizes
> 'Force lowercase and filter numbers and letter "x" only
> 'Allows use of alpha prefix for product IDs
> vSizes = Split(FilterString(LCase$(RangeRef.Value), "x", False), "x")
> GetSize = vSizes(UBound(vSizes) - 2) * vSizes(UBound(vSizes) - 1)
> If IsCubic Then GetSize = GetSize * vSizes(UBound(vSizes))
> End Function
>

> ...which can be used as follows...

>
> A1: 8x8x7
>
> Formula to return area:
> B1: =getsize(A1)
> Returns: 64
>
> Formula to return volume:
> C1: =getsize(A1,1)
> Returns: 448
>

> ...and will allow use of alpha prefixes...

> Gary and others who tried to help me I appreciate it but way over my
> head. You guys have a gift!
> Tried copying and pasting and no success what your saying.
> Can someone interpret what I'm asking? My fault for not explaining
> myself better!! Apologize to all
> 1 I A I B I C I
> D I
> 2 I Size: I 8x8 I Here I wanted the sq.ft. '64' I Blank
> ECT...>
>
> Say this is a contract with "A" column asking a question and 'B' column
> the answer- being 8x8. C & D are just blank spaces and so on.
> End of worksheet #1!
> In this example worksheet #1: I wanted B2 '8x8' size to be resolved in
> sq.ft. as a number which is '64' in cell C2 or what ever size is
> answered in B2. 8x10, 8x12, 10x16,12x12 etc. Does this make sense?
> This was my first question!
> On another worksheet #2 I have...
>
> I 8x8 Shed I B I C I D
> I
> 1 I Description I Quantity I Pricing I Extended Pricing I
> 2 I 4'x4'x8' Treated I 2 I $5 I $10
> I
> 3 I Smart Panel I 10 I $10 I $100
> I
> 4
> _________________
> 5 I Total I $110
> I
>
> This is #2 worksheet. It has many list of materials labeled by size of
> sheds- range from 8x8,8x10,8x12,12x16 etc...
> In this example on worksheet #2. In column 1 row-0 '8x8 Shed'
> identifies this list of wood for to build this shed. When the answer in
> B2 on worksheet on #1 is stated as a 8x8 for example or what ever size
> shed it would attach a wood list from this worksheet #2. Maybe attach is
> the wrong word. From this I will somehow make it attach back to the
> contract & maybe fill in a third Worksheet for a printable fax sheet to
> be sent to a store. Whew! Does this make sense?


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
aswasis

[Mazzaropi]

Help from *Brazil*
Dear *aswasis* Good afternoon.

While I�m waiting your file, I did an example about what I understood
concerning your question.

Tell me if any formula can help you!

Please, don�t laugh a lot. I don�t understand nothing about how to
build Sheds. :D

Have a nice day!


+-------------------------------------------------------------------+
|Filename: 12_APRIL_2013_ASWASI_Shed_Builder_Calculator--1.0.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=842|
+-------------------------------------------------------------------+



--
Mazzaropi

[aswasis]

Wow! I could never explain what you have done for me in just that
sample. I know you and others who have commented and offer your help
are amazing at what you do! I'm so appreciated this sample. I didn't
know this was possible. Far exceeded what I was looking for. I'll have
my son do the zip file and just be open for any questions. I understand
know more why everyone was asking for information. Thanks and hope to
speak with you and others who might want to comment!

Dear *aswasis* Good afternoon.

While I“m waiting your file, I did an example about what I understood
concerning your question.

Tell me if any formula can help you!

Please, don“t laugh a lot. I don“t understand nothing about how to
build Sheds. :D

Have a nice day!


+-------------------------------------------------------------------+

+-------------------------------------------------------------------+



--
aswasis

[aswasis]

I had my son help me put together a zip file. It has a letter explaining
things and a copy of the contract file. Thank you and let me know if you
can help at all. Thanks again!


+-------------------------------------------------------------------+
|Filename: excel banter.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=843|
+-------------------------------------------------------------------+



--
aswasis

[Mazzaropi]

aswasis;1611240 Wrote:
> I had my son help me put together a zip file. It has a letter explaining
> things and a copy of the contract file. Thank you and let me know if you
> can help at all. Thanks again!

Good Morning, *ASWASIS*

I took a look at your files.

Well, as this forum is a Colaborative Place I think that many people
will try to help you with your project.

As you said before, there are a lot of questions now that are necessary
to be answered.
I have a initial idea to your project.
I did a file for you where I suggest how the things can be done. It愀
just a suggestion.
If anyone have a better conception about your project, this opinion will
be VERY welcome.

At my file I suggest how you must fill the materials list.
Finish this part to make possible continue to developing the project.

I believe that愀 necessary to clarify some doubts.
As will be so boring to fill a lot of questions here at chat place I put
them at a DOUBTS TAB on the attached spreadsheet .

I hope that many contribuitions can appear here for your project.

Have a nice day!


+-------------------------------------------------------------------+

|Filename: Aswasi_Excell_Project_16_04_2013.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=847|
+-------------------------------------------------------------------+



--
Mazzaropi

[Mazzaropi]

Good Morning, *ASWASIS*.

You abandoned your post.

Did you gave up your Excel Shed Builder project?

Have a nice day.


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Mazzaropi