Formula Help - inserting formula result into other formula.

Discuss the spreadsheet application

Formula Help - inserting formula result into other formula.

Postby Billy Bones » Mon Mar 31, 2008 7:07 pm

Hi Folks,

I have a spreadsheet that tracks productivity in my shop. I am far from a spreadsheet master, so I'm sure it is designed all wrong. On a main page, I have the list of all the orders in rows, with the cells tracking what is in the order, who it is for, and at which stage of production it currently is.

I have separate pages for each of the production stages. What I wanted was for the separate pages to automatically pull content form the main page. So I update the main page every day, and the other pages pull content.

I used this countif function =COUNTIF(A2:A110;A113) -- to tell me how many were at a particular stage.
I used some ridiculousness to figure out the line number that the particular production station's production started at, and I was planning to plug that line number into a vlookup formula. My problem is that I have a number as a result sitting in a cell. I want to plug that number into a formula that looks like =IF(L16>0;(VLOOKUP($A$1;Mainpage.$A46:Mainpage.$J$50;2;0));"") Where the formula has "Mainpage.$A46" I am trying to plug in the number that is sitting in my cell, but I don't know how.

The if formula is a function of the number I got from countif, and in successive cells subtracted "1" so that when there weren't any more units for that station of production, it wouldn't post anything to the cells.

I stayed up all night trying to figure this out. If someone can point me in the right direction, I would be much obliged.

Billy Bones
Billy Bones
 
Posts: 6
Joined: Mon Mar 31, 2008 6:39 pm

Re: Formula Help - inserting formula result into other formula.

Postby h1h » Mon Mar 31, 2008 9:37 pm

I suggest you upload a sample file (without any delicte data) and explain in there what you want
OOo 2.4 (from OOo-site) on Linux
User avatar
h1h
 
Posts: 40
Joined: Wed Nov 28, 2007 8:45 pm

Re: Formula Help - inserting formula result into other formula.

Postby Billy Bones » Tue Apr 01, 2008 1:07 am

Thanks for the idea. here is a simplified version of what I'm trying to do.

Billy Bones
Attachments
Production Spreadsheet.ods
(16.82 KiB) Downloaded 110 times
Billy Bones
 
Posts: 6
Joined: Mon Mar 31, 2008 6:39 pm

Re: Formula Help - inserting formula result into other formula.

Postby keme » Wed Apr 02, 2008 11:00 am

The MATCH() function returns the relative position of an item with a given search value.
The OFFSET() function returns a reference (which can be a range reference).

Try combining the use of those functions to do what you want. You probably won't even need to sort your source data.
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14
User avatar
keme
Volunteer
 
Posts: 3383
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Formula Help - inserting formula result into other formula.

Postby Billy Bones » Wed Apr 02, 2008 12:08 pm

Can you clarify that a bit? I'll start looking into that now.

Billy
Billy Bones
 
Posts: 6
Joined: Mon Mar 31, 2008 6:39 pm

Re: Formula Help - inserting formula result into other formula.

Postby h1h » Wed Apr 02, 2008 11:17 pm

have a look at the attached.

Another approach would of course be filtering your MainPage.
Attachments
Production Spreadsheet.ods
(13.19 KiB) Downloaded 93 times
OOo 2.4 (from OOo-site) on Linux
User avatar
h1h
 
Posts: 40
Joined: Wed Nov 28, 2007 8:45 pm

Re: Formula Help - inserting formula result into other formula.

Postby angiesb » Sat Apr 12, 2008 11:28 pm

I need help in creating an amortization table, I have no idea what formulas to use or even how to get started, I have all the data that needs to be entered but do not know how to go about making it happen. Can you help?
angiesb
 
Posts: 2
Joined: Sat Apr 12, 2008 11:05 pm

Re: Formula Help - inserting formula result into other formula.

Postby h1h » Sun Apr 13, 2008 1:47 am

the PMT function might be what you are looking for. Check the help files in OOo for a description.
OOo 2.4 (from OOo-site) on Linux
User avatar
h1h
 
Posts: 40
Joined: Wed Nov 28, 2007 8:45 pm

Re: Formula Help - inserting formula result into other formula.

Postby kingfisher » Sun Apr 13, 2008 4:51 am

This appears to be a duplicate of Help, amortization table.
Apache OpenOffice 4.1.6 on PCLinuxOS
User avatar
kingfisher
Volunteer
 
Posts: 2120
Joined: Tue Nov 20, 2007 10:53 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 21 guests