can't find the right formula

[twnisbett]

I have a complex series of calculations that I want to run several times,
using different inputs. Let's say that on Worksheet 1, I have an input in
cell A1 and some output in cell A15. Then on Worksheet 2, column A is the
series 5, 10, 15, 20, etc. I want column B to the output of that series; in
other words, B1 would be the output is A1 (5) were the input, B2 would be the
output if A2 (10) were the input, B3 would be the output if A3 (15) were the
input, etc.

I remember doing this several years ago, and I can't remember how I did it.
Any help would be appreciated.

 

[Max]

In Sheet2,
You can do away with the series data in col A
Just place this in any startcell, eg in B3:
=OFFSET(Sheet1!$A$1,ROWS($1:1)*5-5,)
Copy down as far as required.

Easily adapt the expression to suit as desired:
The anchor: Sheet1!$A$1 is the start cell in the source
The "5" is your interval in the source sheet

Wave your success, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[twnisbett]

Sorry, it's either not working or I don't understand you. First of all, the
Offset function requires three other arguments, and I don't know how to make
the function reference the output cell (Sheet1!A15).

 

[Shane Devenshire]

First the OFFSET function does not require all the arguments, infact this is
a legitimate formula, although pecular:

=OFFSET(F1,,)

It would return the value in F1. I would help with the rest of the formula
but I'm not sure what you are trying to do. As always, some sample data and
an explanation from the sample what you would like, is a BIG help, for tired
old minds such as mine.

 

[twnisbett]

Ok, I'll try to explain in more detail, and to make it easier, I'll put it
all on one sheet. Let's say A1 is named "input". A2 contains a formula that
utilizes A1 as a parameter. A3 contains another formula that uses A2 as a
parameter. A4 contains another formula that uses A2 and A3 as parameters.
And thus I name cell A4 as "output". I now want to create a set of data
points which will ultimately be graphed along an (x,y) line chart.

Just for simplicity, let's say that A2 contains the formula "=A1+1"; and A3
contains the formula "=A1+2"; and A4 contains the formula "=A2+A3". (What
the forumulas actually are are irrelevant - in reality, they are far more
complex than this.)

So, if I plug in a 1 into cell A1, A4 then becomes 5. That's my first data
point. If I plug 2 into cell A1, A4 becomes 7 - second data point. If I put
3 into cell A1, A4 becomes 9 - third data point.

So now, starting in cell D1, I put the vertical array (1,2,3). In colume E,
I want to put some formula or use some tool that will give me the array
(4,7,9). Then, I want to be able to copy the table down so that my array
(1,2,3) becomes (1,2,3,...,100), and I get the output results of these 100
inputs.

Does that help?

Thanks for your assitance.

Travis

 

[Max]

Sorry, erratum. the earlier formula should be:
=OFFSET(Sheet1!$A$1,ROWS($1:1)*4-4,)
The above expression will return
what's in Sheet1's A1, A5, A10, etc as you copy it down

Another way to get it done,
where you define the row numbers in A1 down
In B1, copied down: =INDIRECT("'Sheet1'!A"&A1)
This way would be useful if you have irregular row numbers defined in A1 down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[twnisbett]

Thank you all. I found what I needed in the help file. What I'm trying to
do is called a Data Table.

Thanks again!

 

[Max]

Think my 2nd response just crossed your reply here. Take a look at it. You
can also use INDIRECT, especially where you might have irregular row numbers
defined for extract.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

 

[Shane Devenshire]

Hi,

If a data table will do what you need the Pivot Table probably will what you
need and probably better.