Saturday, March 10, 2012

How to change cell value in Excel based on the contents of another cell?

What I'd like to do is, say, put "47" into a cell A1, and have cell B1 display the contents of Sheet1!A47 (That is, become the formula =Sheet1!A47). Then if i change A1 to "55", B1 becomes Sheet1!A55, etcHow to change cell value in Excel based on the contents of another cell?
=indirect("Sheet1!A"%26amp;A1)How to change cell value in Excel based on the contents of another cell?
You can create a VBA function that will do it easily. (This is step-by-step if you aren't familiar with VBA). Try this:

Press Alt+F11.

Right click below "VBA Project" in the project window and click "Insert Module".

In the editor window, paste this function code:

--------------------------------------鈥?br>
Function Celllookup(Cell As Integer)



Value = Cells(Cell, 1)



Celllookup = Value



End Function

--------------------------------------鈥?br>


Now in B1 insert the formula "=Celllookup(A1)"



Now whatever number you type in A1 will produce the value of the cell from column A with that row number in B1.



Hope this solves your problem!

No comments:

Post a Comment