2 Formulas needed!!! :)

[aswasis]

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

2nd- On a 3 worksheet I'm counting on the '8x8" the 1st worksheet t
signify to a list of material I assigned to it.
Basically I build sheds. Every time we have to put a wood order in w
have to do a wood list for each shed. for years I wanted to d
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
here! Thank

 

[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.

 

[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

 

[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 ;-)


Regards
Claus Busch

 

[aswasis]

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

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

My plan is if I entered in say just the size (8x8x7) say in a cell fro
the contract worksheet, that it would trigger a lumber list for tha
shed on a different worksheet. Then when my girls are ready to order i
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 an
label them by the size "8x8x7 or what ever size. I could use a numbe
system or letter system if this would be better. I don't have to hav
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´t know nothing abou
them. Then be so clear as possible on your explanation.

I´ll be happy to try to help you and your family in this bussines task.

Have a nice Day

 

[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 as
you what size do you need. 8'x10'x7high is just on size of man
combinations of sizes. Generally 2' increments in any one direction.
can't interpret what you wrote out-you're far above me so I can't tel
what your understanding is on my question by what you wrote. Sorry

 

[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

 

[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()

--
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]

;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...

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()

--
Garry


Gary and others who tried to help me I appreciate it but way over m
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 explainin
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 Blan
ECT...>

Say this is a contract with "A" column asking a question and 'B' colum
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 i
sq.ft. as a number which is '64' in cell C2 or what ever size i
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 o
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 i
B2 on worksheet on #1 is stated as a 8x8 for example or what ever siz
shed it would attach a wood list from this worksheet #2. Maybe attach i
the wrong word. From this I will somehow make it attach back to th
contract & maybe fill in a third Worksheet for a printable fax sheet t
be sent to a store. Whew! Does this make sense

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

 

[Mazzaropi]

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

While I´m waiting your file, I did an example about what I understoo
concerning your question.

Tell me if any formula can help you!

Please, don´t laugh a lot. I don´t understand nothing about how t
build Sheds.

Have a nice day

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

 

[aswasis]

Wow! I could never explain what you have done for me in just tha
sample. I know you and others who have commented and offer your hel
are amazing at what you do! I'm so appreciated this sample. I didn'
know this was possible. Far exceeded what I was looking for. I'll hav
my son do the zip file and just be open for any questions. I understan
know more why everyone was asking for information. Thanks and hope t
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 understoo
concerning your question.

Tell me if any formula can help you!

Please, don´t laugh a lot. I don´t understand nothing about how t
build Sheds.

Have a nice day

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

 

[aswasis]

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

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

 

[Mazzaropi]

I had my son help me put together a zip file. It has a letter explainin
things and a copy of the contract file. Thank you and let me know if yo
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 peopl
will try to help you with your project.

As you said before, there are a lot of questions now that are necessar
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 wil
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´s necessary to clarify some doubts.
As will be so boring to fill a lot of questions here at chat place I pu
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]

Good Morning, *ASWASIS*.

You abandoned your post.

Did you gave up your Excel Shed Builder project?

Have a nice day

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