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

Help from Brazil
Good evening aswasis.

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

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.

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

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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´t know nothing about 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.[/quote]

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.[/quote]


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!

I had read someone on this site who asked- find a letter "p' in a sequence of numbers to show "paperback" ... I took that formula and made changes to this-=IF(FIND("8x8x7",A2)<"","64","") "64" being the sq ft. it works. except that is just one. Their are 40 combinations. I tried to repeat the sequence in my own way but it didn't work. Something like this...=IF(FIND("8x8x7",A2)<"","64",""),IF(FIND(" 10x10x7",A2)<"","100","")IF(FIND("12x14x7",A2)<" ","168","") and so on and so on... This was my first question in needing a formula.

Now when you enter the size I was hoping to trigger a preselected wood list of material and prices for that shed to be filled out on a separate worksheet to print later.

Hi,ch;16

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2[/quote]

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

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

[quote='GS[_2_];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 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?

Help from Brazil
Dear aswasis Good Morning .

I believe that your question is totally understandable now.
You were very clear in your explanation.

BUT if you could to attach your spreadsheet here, I´m sure that will be easier for everyone to help you, doing the changes directly on your file, in a quicker and conclusive way.

Have a nice day!

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!

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![/quote]

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´s 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´s 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!

Good Morning, ASWASIS.

You abandoned your post.

Did you gave up your Excel Shed Builder project?

Have a nice day.