0

How can I make excel run like a program such that a particular cell in sheet 1 changes its value according to each successive row operation in Sheet 2. Basically I want to know if a cell can have dynamic value (act like a temporary register of "C" language which changes after execution of calculations in a row in another sheet.

It's confusing but if anyone can help me out. I am modelling something and I need this very badly to proceed further.

Let's say I am working in Sheet 1. I am working across row 1. After filling 5 columms, I want the result to go to a particular cell (Let's say A1) of Sheet 2. Now this Sheet 2 whose values change according to input at A1. This sheet 2 then again sends value to sheet1. here we work across row 2 now and the result goes to the same cell A1 of sheet 2. The point is it's a closed loop thing. and I want after every iteration, the value of particular cell changes according to the result of the iteration.

  • 2
    Welcome to superuser. Your Westin is unclear now. Please explain it with some example: What is exactly the event triggering the change of cell's value, what is the value of the cell? Some sample data can also help. – Máté Juhász Dec 10 '15 at 5:22
1

Ok, I'm going to assume that you are more just trying to move data from one sheet to another sheet, but in the guise of a formula. so i think what your looking for is this

=Sheet2!A1

However, i can not bee too sure, so please do define more and i will edit this answer for that response.

OK, so now that I have a starting point to go on, I can actually answer this question. I do, however, feel that this is a multi-step problem.

So here is my test sheet. Some odd data in a weird matrix in excel

Here, cell G1 is providing the last value in column E. In this cell, I have the following function: OFFSET([StartCell],MATCH(MAX([Range])+1,[Range],1)-1,0)

Lets break it down.

=OFFSET - Well, we are starting a function, and we want it to return data from a cell [StartCell] - the top cell that this function is going to reference MATCH - searches for a specified item in a range of cells MAX - searches for the largest number in the range

So we search for the largest number in our range, add 1 to it to make a number not in the data set, use that number in a match function, which returns the last row of data found then, then we offset to show the data that we want.

For your situation, I would recommend making the column after the inserted data reflect what you want to do on the data, then use this function on that column so you have the data you want on a single cell.

kinda like so an adoption of the equation that i just introduced earlier Cell H1 here can be on any other sheet of this book, and it will work as intended.

=OFFSET(Sheet1!F1,MATCH(MAX(Sheet1!F:F)+1,Sheet1!F:F,1)-1,0)

Now if putting the function after the inputted data is not possible, you can still do this, it will just be a very long formula.

Note: in the last case, F:F is used for the range because it defines the whole column of F so you will not need to redefine the parameters of the function if the data exceeds the predefined area.

Please ask if help is needed

  • Not this actually. Let's say I am working in SHeet 1. I am working across row 1. After filling 5 columms, I want the result to go to a particular cell (Let's say A1) of Sheet 2. Now this Sheet 2 whose values change according to input at A1. This sheet 2 then again sends value to sheet1. here we work across row 2 now and the result goes to the same cell A1 of sheet 2. The point is it's a closed loop thing. and I want after every iteration, the value of particular cell changes according to the result of the iteration. – Neha Dec 10 '15 at 10:26
  • I hope that this better answers your question! – NorNogaAdmin Dec 12 '15 at 4:57

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.