Dynamic cell reference

[mztq]

I'm trying to refer to (a row of) cells in a different tabpage within Excel.

The Excel file is connected to an external database and I'm using a parameter to select a specific personcode which then returns a set of data belonging to that specific person. I basicly want one tabpage of raw data (loaded from the database) and another tabpage with calculated data based on those raw data.

In the calculated data tab I'd like to have those cell values changed if a different person gets selected. All I'm getting now is a #REF error when I'm changing the personcode.

 

[PaddyD]

Hi. Not enough info to suggest anything yet. Answers to the following please:

"The Excel file is connected to an external database."
How?

"I'm using a parameter to select a specific personcode "
How? Give an example.

"...which then returns a set of data belonging to that specific person"
Does this already work? How is the data returned? How is the destination specified?

" basicly want one tabpage of raw data (loaded from the database) and another tabpage with calculated data based on those raw data. "
Is this structure already in place? ('Cos your wording above implies that it is).

 

[mztq]

"The Excel file is connected to an external database."
How?

It's an Oracle database. I've created a view within there and I import the view into Excel using ODBC. In MSQuery I've added a criteria on 'personcode'.

How? Give an example.

Inside Excel I can enter a personcode in a specific cell. After that a popup will come up and I enter the personcode again in that popup. Then, it will show the set of data belonging to that person.

Does this already work? How is the data returned? How is the destination specified?

Yes, it works. It's sporttest data so if I select a person it will show the performance of that person from the sporttest. Each row has a timestamp and values like heart rate, speed etc.

Is this structure already in place? ('Cos your wording above implies that it is).

Well, I'm trying to get that structure in place. That's where the problem arises because the cell values (heart rate, speed etc) change whenever the selected person changes.

I want to copy (refer) the rows of raw data tab to the calculated data tab. When that's done, I want to use those rows of data in the calculated data tab to perform some calculations. For example average heart rate, maximum speed and a few graphs.

 

[PaddyD]

"All I'm getting now is a #REF error when I'm changing the personcode. "

"Inside Excel I can enter a personcode in a specific cell. After that a popup will come up and I enter the personcode again in that popup. Then, it will show the set of data belonging to that person."

#ref errors are usually produced by formulas - what's the formula? If not a formula, what's generating this?

 

[mztq]

#ref errors are usually produced by formulas - what's the formula? If not a formula, what's generating this

It's just a simple reference like this:

=LoadedData!F4

F4 is obviously the cell I want to "grab" and copy to the calculated data tab. Actually, it has to be the whole row of F but the row can be of different lengths since every sporttest could have a different amount of timestamps (records). That's another point, though.

So yeah, I use "=LoadedData!F4" but after I change the personcode it will give the #ref error.

 

[PaddyD]

What's in loadedData!F4 ? Probably just me being thick, but I don't see how changing the personcode is generating an error directly in that cell, unless loadedData!F4 itself contains a #ref error (in which case what's in there that's generating it?)

 

[mztq]

Well, I'm not that good in English so I might have provided an insufficient description but it's not only the personcode that changes. The whole set of data changes.

I basicly use the personcode to select a specific person and his data. When I change to a different personcode, values like heart rate, speed will change too. It's sporttest data, so every personcode correspondents to an individual sporter who performed in a sporttest.

I'm basicly looking for a way to get those data into the calculated tab as well so I can make some calculations with it. And at the moment I select a different personcode, the calculated data should change too.

It might be an uncommon way to use Excel but I hope there is some way to do this