Return Formula

[willemeulen]

I constructed a lookup table with formula's on the second sheet which I
want to use to return a working formula on sheet 1.

Example Shape Code 39:

Formula (as in the book)
A + 0.57 B + C -1.57d

Value's A,B and C are user defined and can be found on sheet 1 in the
columns M,N and O
d= is the diameter and is user defined in column F


In my lookup table I have the following formula (sheet 2)

INDIRECT("M"&ROW())+0.57*INDIRECT("N"&ROW())+INDIRECT("O"&ROW())-1.75*INDIRECT("F"&ROW())

What do I need to do to get this cell working on sheet 1
Now it eiter returns the text (when = sign is left out) or returns the
result from sheet two

Thanks,

W

 

[Glenn]

I constructed a lookup table with formula's on the second sheet which I
want to use to return a working formula on sheet 1.

Example Shape Code 39:

Formula (as in the book)
A + 0.57 B + C -1.57d

Value's A,B and C are user defined and can be found on sheet 1 in the
columns M,N and O
d= is the diameter and is user defined in column F


In my lookup table I have the following formula (sheet 2)

INDIRECT("M"&ROW())+0.57*INDIRECT("N"&ROW())+INDIRECT("O"&ROW())-1.75*INDIRECT("F"&ROW())

What do I need to do to get this cell working on sheet 1
Now it eiter returns the text (when = sign is left out) or returns the
result from sheet two

Thanks,

W

Put

'Sheet 1'!

in front of your column letters (inside the quotes) if you want the formula in a
cell on Sheet 2 to reference cells on Sheet 1.

 

[willemeulen]

I put "sheet1" in front of the column number but no results

Now formula in lookup table is as follows:
=INDIRECT("'Sheet1'!M"&ROW())+0.57*INDIRECT("'Sheet1'!N"&ROW())+INDIRECT("'Sheet1'!O"&ROW())-1.75*INDIRECT("'Sheet1'!F"&ROW())

 

[Simon Lloyd]

I put "sheet1" in front of the column number but no results

Now formula in lookup table is as follows:
=INDIRECT("'Sheet1'!M"&ROW())+0.57*INDIRECT("'Sheet1'!N"&ROW())+INDIRECT("'Sheet1'!O"&ROW())-1.75*INDIRECT("'Sheet1'!F"&ROW())



I think the main issue is I want excel to return a formula and not a
result. The lookup table contains the formula for all different shape
files. The formula needs input from the user, this input is inserted in
sheet 1.

The example shows the formula for shape 35 (to be particulare this is a
U shape). The formula which now needs to be sourced from the lookup
table in sheet 2 will calculate the length. The values (A/B/C etc) are
inserted by the user, A shape does not say how long the legs, bends etc
are. Every new row the user filles out the material diameter etc and a
shape code. In total ther are about 60 different shape codes. (S,L,Z,W
etc.)Maybe this will work better for you:

=INDIRECT(Sheet1!M&ROW())+0.57*INDIRECT(Sheet1!N&ROW())+INDIRECT(Sheet1!O&ROW())-1.75*INDIRECT(Sheet1!F&ROW())


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[Glenn]

I put "sheet1" in front of the column number but no results

Now formula in lookup table is as follows:
=INDIRECT("'Sheet1'!M"&ROW())+0.57*INDIRECT("'Sheet1'!N"&ROW())+INDIRECT("'Sheet1'!O"&ROW())-1.75*INDIRECT("'Sheet1'!F"&ROW())

Define "no results".

 

[willemeulen]

I think I'm making it to complicated. In stead of trying to use one
formula per shape code I will generate the same formula as suggested but
now for each row, so I will repeat it every row, hopefully this works.
Will try tomorrow.

 

[willemeulen]

For some reason it looks like the indirect function does not work on my
sheet. The only way I can get things working is by creating a Hlookup
table which contains the formula for all possible shape files and direct
references to sheet 1. It's not realy pleasing to look at but yeah, any
suggestion how to make it work better.

Intended setup:
Vlookup table

A B
Shape Code Formula
20 =Indirect(sheet1M&row .......
31
32
35
39

etc

Because the lookup function returns a value and not the actual formula
answers will only be correct if row is corresponding between sheet 1 and
2.


New Hlookup table

A B C D
Shape Code 20 30 31 32 etc
11 =sheet1!M11*N11-O11 etc
12
13
14
15
16
corresponding row number with sheet 1

Now I have a big table with exact formulas in the corresponding row, is
this the way to do it or is it a bit duh:ill

 

[Simon Lloyd]

Willemeulen, why not attach a sample workbook that we can help you with?
Attatchments.

To upload a workbook, click reply then add your few words, scroll down
past the submit button and you will see the Manage Attatchments button,
this is where you get to add files for upload, if you have any trouble
please use this link or the one at the bottom of the
any page.

For some reason it looks like the indirect function does not work on my
sheet. The only way I can get things working is by creating a Hlookup
table which contains the formula for all possible shape files and direct
references to sheet 1. It's not realy pleasing to look at but yeah, any
suggestion how to make it work better.

Intended setup:
Vlookup table

A B
Shape Code Formula
20 =Indirect(sheet1M&row .......
31
32
35
39

etc

Because the lookup function returns a value and not the actual formula
answers will only be correct if row is corresponding between sheet 1 and
2.


New Hlookup table

A B C D
Shape Code 20 30 31 32 etc
11 =sheet1!M11*N11-O11 etc
12
13
14
15
16
corresponding row number with sheet 1

Now I have a big table with exact formulas in the corresponding row, is
this the way to do it or is it a bit duh:ill

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[willemeulen]

Hi Simon,

Find the file attached.

Brief discription what the workbook actually contains.
The worksheet contains a bending schedule which is used on site and
steel suppliers use it to fabricate the order. In structural concrete
work you use reinforcing steel, this steel is bend in certain shape
depending on it's purpose/shape of the concrete work. All standard
shapes have a shape code with coresponding formula to calculate the
actual cut length before bending the steel into the shape needed. In the
case of reinforcing steel there two types (type R and Y), type R is
slightly more flexible as type Y and therefore has sharper bends, hooks
etc. Besides the type of steel the diameter determines the bends as
well.

The big challange in my sheet is the calculation of the cut length
(marked in red), it would be nice to use a small simple lookup table
what I have in the current sheet. I would like excel to return the
formula and not a result. As said in my previous mail it seems like I
have to create a big table and mirror the rows from the bending shedule
(sheet1) with the rows on sheet2 and write the formula for each possible
shape code. This way I could actually return a result. For now I'm
focussing to make this work with the simple formulas (the completed
ones) the other formulas are more complex and need index/vlookup values
within the formula, these I will try and sort out ones I have this
working.

Thanks,

WillemBlink1

PS All my post on the forum refer to this worksheet at this stage. Nice
project for me to sort out.


+-------------------------------------------------------------------+
|Filename: Bending Schedule.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=139|
+-------------------------------------------------------------------+