I am at a complete loss

[dfreemanii]

I am sure this has been asked before but I do not know how to word it for a search. I have a nice little budget put together and am trying to plan for buying a home. Basically I want a cell to auto-update based on the value of another cell. Simple enough right but here is the catch. If I put in ='Sheet1'!A42 in my budget I want cell AA42 to up date with the value for 'Sheet1'!AA42. I was thinking of an IF formula but not sure how to word it. Any ideas? Anything would help, because I would rather change the value of 1 cell than have to change 3 every single time I want to update

 

[jasonb75]

That's not making a lot of sense.

Are you saying you want all of the formula to look at row 42?

You could try =INDEX(Sheet1!A:A,B1)

Then type 42 into B1.

Does that help?

 

[dfreemanii]

Thank you for the quick response, and I apologize that I was unclear. I want the formula to look at the entire column A from A17:A999 of sheet1 for a match, then when it finds a match it returns the value of column AA of the matching row. For example: if Sheet2 A17= Sheet1 A39 Sheet2 AA17 would then return the value of Sheet1 AA39 I hope that I explained it better so that I can get the help I am asking for.

 

[jasonb75]

That makes more sense now

Would the values in column A of sheet 1 be unique?

If not, in the event of duplicate entries found, should the formula return the first result, last result or a sum of all results (assuming that sheet 1 column AA contains numbers)?

 

[dfreemanii]

Thank you again for the fast response, Yes the values in column A are unique ( dates ) and the values in column AA are numbers ( dollar figures )

 

[Rada1]

In Sheet2 AA17

=INDEX(Sheet1!$AA$17:$AA$999,MATCH(Sheet2!A17,Sheet1!$A$17:$A$999,0))

or

=VLOOKUP(A17,Sheet1!$A$17:$AA$999,27,0)

 

[jasonb75]

Out of Rada's 2 suggestions, I would say the first is preferable, even though it might look more complex if you're new to excel.

 

[dfreemanii]

Thank you Thank You Thank you! It did exactly what I wanted it to.

 

[Rada1]

You are welcome, I'm glad I could help.