Formula to return cell value based upon cell reference

I have a large spreadsheet where I would like to reference a cell's name to return a value for a different cell. For example:

Excel sample.png

In the above table cell A2 references cell J2 and returns its value. How could I have cell B2 return a value for cell K2 by referencing the line number in cell A2. Note: Usually the line that I am returning the data to is not the same as the line I am getting it from, but the cell reference is always the same line. In otherwords if the reference for A2 is J7, then B2 would be K7, or if A2 is J85, then B2 would be K85.

Thanks Jeff, but when I click on the Attachments icon, the Upload window does not open.

Attached is my example. In the example columns D thru N have cell references in them. I would like to be able to enter only the
reference in Column D and have formulas set to fill in the rest of the columns. Hope someone can help with this.

Thanks Ford. I was able to upload my file and send it out.

I could not figure out a way to do this without VBA; I know of no function that can return another cell's formula as a string. VBA solution attached. The way this works is that there is a user-defined function RowFromFormula that will take a cell as the argument, and read the formula in that cell and return the row referenced in that formula. Once you have that row, then you use INDEX to look up the value from the desired column using that row.

The function expects the formula to look like =A1.If ForFromFormula tries to read the cell formula but can't determine a row number from it (for example, the formula is =SUM(A1:B2)) then you will get an error.

I have installed this into your file.

Thanks so much 6String. That looks like it will work!! You have saved me lots of time, and as we all know, Time is Money!

Need some more help. I have never used VBA. I was able to copy your VBA formula from row to row in my sample spreadsheet. But when I
tried to copy it to a new spreadsheet it looked like the attached. Do I need to enable VBA or something?

You need to put the VBA code into your spreadsheet:

Press Alt+F11 to open VBA editor

Right click on panel on left-hand side

"Insert" ==> Module

Copy Code below into the right-hand panel

Save file as .xlsm

Please Login or Register  to view this content.

Thanks so much!! I need to learn this VBA stuff - LOL

I was able to insert the module into a sample Excel sheet, but when I tried to do it on the spreadsheet i need it in, it is not
working correctly. The sheet is already an .xlsm sheet so that is not the problem. Please see the attached sheet and see if you
can tell me why it is not working properly.

What value should be in D3? Based on the current value of "=X2" the results are correct (appears to be for JAN not FEB)..

If you put for example =X113 in D3 then you get correct results

I don't know what happened but when I reopened it after letting it sit for a while everything was working!! Excel is a funny animal sometimes.

Ok, here is another related question. If I wanted to get a sum for columns Z to AF based upon the line referenced in column D, what would the formula look like? For example, right now I manually type in the formula "=sum(z2:af2)" (where D3 is "=X2)", in every cell in column E. Can I use the VBA function you gave me somehow in the formula to give me the result?

I tested it out on the spreadsheet. It seems to work. Thanks again John!!

My pleasure.

Can you please mark thread as solved ("Thread Tools" at top of first post).