Solved Change number in a formula buy referencing a different cell

March 1, 2016 at 07:27:25
Specs: Macintosh
Basically I have this formula:

=(MIKE!$B8)

and I have a bunch of cells withs Numbers like so:

Cell | Cell Number Change
C1 | 10
C2 | 16
C3 | 29
C4 | 40
C5 | 61

What I want to do is to be able to change the number "8" for the formula by referencing a cell to grab a number, which will thus change the formula and the output of the data. I'll use it to copy and paste down and change the numbers out to get only the output of different cells that I need.


See More: Change number in a formula buy referencing a different cell

Report •

#1
March 1, 2016 at 07:53:32
✔ Best Answer
The INDIRECT function should work for you. The INDIRECT function changes a text string to a reference.

This will reference Mike!B(whatever "row number" is in C1)

=INDIRECT("MIKE!$B$" & C1)

Note the use of the quotes around MIKE!$B$. That tells the INDIRECT function that it is a text string. The C1 is not enclosed in quotes, so Excel uses that as a cell reference.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
March 1, 2016 at 10:20:16
Sweetness thanks again Derby

Report •

#3
March 1, 2016 at 11:10:55
I don't know if this will work for your situation, but you might consider a drop-down in C1 with your list of "row numbers". With an INDIRECT function referencing C1, the row number would be based on your choice from the drop down.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question