Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Formula
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())+INDIR ECT("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 -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=96571 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Formula
willemeulen wrote:
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())+INDIR ECT("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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Formula
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("'Shee t1'!N"&ROW())+INDIRECT("'Sheet1'!O"&ROW())-1.75*INDIRECT("'Sheet1'!F"&ROW()) -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=96571 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Formula
willemeulen;345269 Wrote: 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("'Shee t1'!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&R OW())+INDIRECT(Sheet1!O&ROW())-1.75*INDIRECT(Sheet1!F&ROW()) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=96571 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Formula
willemeulen wrote:
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("'Shee t1'!N"&ROW())+INDIRECT("'Sheet1'!O"&ROW())-1.75*INDIRECT("'Sheet1'!F"&ROW()) Define "no results". |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Formula
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 Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=96571 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Formula
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 -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=96571 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Formula
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. willemeulen;346398 Wrote: 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) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=96571 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Formula
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| +-------------------------------------------------------------------+ -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=96571 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
if formula return | Excel Discussion (Misc queries) | |||
Return Last Value in a Row formula | Excel Worksheet Functions | |||
No return for formula | Excel Worksheet Functions | |||
Why does my formula return zero? | Excel Discussion (Misc queries) | |||
I need help with way to return 0 or N/A to a formula | New Users to Excel |