User input in equations

[niels]

Hi

I hope someone can help me with the following problem - and that I can describe it so you can understand it:

When using the matrix reference style (R1C1 reference style) in Excel - is it then posible to use input data in this reference ?

For exampel - In a certain cell-equation I want to "point" to cell RxC5 but need to change the values of x so I dont want the numbers in the code/formulas..
So I put the value of x in cell R1C1. Now I need excel to "read" this value.. In my mind this would look something like this:
R(R1C1)C5

This obviously does not work so I thought I could use some sort of Macro/program to do this.

I am not a visual expert so if the solution for my problem is very complicated let me know - and I'll do the whole thing in Matlab
But if there is a fairly simple solution (or if someone knows how to do this with simple excel equations) I would be a very happy person if you could help me

Best regards
Niels Kau Andersen

[sann0638]

Hi,
try the indirect function:
=INDIRECT("R"&A1&"1C1")

Sorry - this doesn't work. Anyone suggest why not?

_________________
Regards, Mike.

[ This Message was edited by: sann0638 on 2002-05-24 07:20 ]

[JayKay]

Hi

You could also try using the OFFSET worksheet function.

Cheers

JayKay

[niels]

[quote]
On 2002-05-24 07:16, sann0638 wrote:
Hi,
try the indirect function:
=INDIRECT("R"&A1&"1C1")

Sorry - this doesn't work. Anyone suggest why not?

The excel help defines the indirect function as: INDIRECT(ref_text,a1)

where a1 is a logical value. which should be set to false if you're using r1c1 reference style.. Maybe that is it.. I'm trying to find out myself.. Any further help would ofcourse be apreciated,,
thx

Niels

[Mark O'Brien]

Niels,

I'm just curious, but what is your project that you're working on?

I used to use MATLAB a lot and when I stopped using it The Mathworks was beginning to bring out some better tools for connecting to other applications like Excel, gPROMS, SPEEDUP etc.

[niels]

[quote]
On 2002-05-24 07:25, Mark O'Brien wrote:
Niels,

I'm just curious, but what is your project that you're working on?



Well - I'm using this for analysing data from a process simulator (in chemical engineering) and the data comes out as a matrix. Every stream in a column and then the stream data in the rows (temperature, pressure, flowrates of each component etc)

I have made the analysis in a excel-sheet but my boss would like to generalize it.. To aply it to different processes. So my idea was that if you put the number of the component and the number of the stream - you should be able to extract data from the matrix mentioned above..

I.e. If i choose compound = 1 and stream = 5 I should be able to acces the data sheet in the cell r1c5

I know I could do this in matlab - but since all the calculations are already in excel, well I though I could save some time

Niels

[lenze]

Why not use the INDEX function? If Compound is in A1 and Stream in A2, Then INDEX("Yourmatrix",A1,A2) should return the value from your table corresponding to the values in A1 (Row number) And A2(Column Number)

Edit: You can improve this by comining Index with the Match funtion
[ This Message was edited by: lenze on 2002-05-24 07:41 ]

[ This Message was edited by: lenze on 2002-05-24 07:43 ]

[Mark O'Brien]

Every stream in a column

Not a binary distillation column? Or a multi-component distillation column? (I'd be impressed by that one)

My old work had a couple of nice little graphing utilities. One used MATLAB's graphing and the other imported data to Excel. It was rather snifty.

...sorry this is completely off topic, but I can't answer the question and I'm 100% positive someone here can, so there's no harm keeping the thread at the top.

EDIT:: Just reread your post, I guess you mean the array that your passing between simulation blocks when you said "column".
_________________
[b] Mark O'Brien

[ This Message was edited by: Mark O'Brien on 2002-05-24 07:55 ]

[niels]

[quote]
On 2002-05-24 07:53, Mark O'Brien wrote:

Every stream in a column

Not a binary distillation column? Or a multi-component distillation column? (I'd be impressed by that one)

Well, actually it is the whole plant - but lokking only at the "external" streams, between the units..

The "matrix I was desribing looks something like this:
_________stream1 stream2 stream3 etc
temp
pressure
flow1
flow2

with data in the "center"...

So the flow of component 2 in stream 2 would be in cell C5 or R5C3 in the other notation

What I want is if I make a box where people can put component number and another with stream number. That would give Component=2 and stream=2 but I need the data from R5C3 , i.e the code should look like this
R(3+2)C(1+2) where the two 2's would be read from the input box'es and the 3 and 1 just counts for the fact that the data doesn't start in 0,0..

Ayway - thx for the inputs - which I have to look at. And feel free to add more info

Best regards
Niels



[ This Message was edited by: niels on 2002-05-24 08:19 ]

[niels]

On 2002-05-24 07:17, JayKay wrote:
Hi

You could also try using the OFFSET worksheet function.

Hi JayKay

I think you saved me there.. That offset function is pretty good - and I think I can get the whole thing to work using that...

Thx a bunch and just to let you know that I'm very impressed with this forum. Kepp up the good work - and I'll probably be back again

Niels