Link to text and return text into a formula?

[Guest]

Hello.
I have 10 workbooks. Each has its own sheet/tab called CYR, and each has its
own tab called Load. In each file's Load tab, there is the same formula in
cell M1
ABS(CYR!J3)/Z1

where J refers to a column for May data its own CYR tab

The CYR tab has about 200 rows of data (J4, J5, J6, J7 etc.) and 13 columns
of months (May, June, July etc.). I do not want to link to all rows but most
of them.

Each month, I open each of the 10 workbooks, go to their respective Load
tabs, and search & replace J with K to bring in the June numbers from the CYR
sheet to the Load sheet, then the following month replace K with L to bring
in July numbers, and so on.

I was hoping to have a "master" tab in just one of the 10 workbooks in which
I key into its cell A1
CYR!J
and the Load tab has a formula that merges CYR!J with its particular row #.
I would like cell M1 to point to CYR!J3, cell M2 to point to CYR!J4, and so
on.

In other words, I'm hoping to change just one cell each month on a "master"
tab and have all column M cells in the Load tab link that same one cell
automatically to update its formulas. My hope is then that the following
month, I just change the "master" cell A1 from CYR!J to CYR!K and the Load
tab pulls June values.

Next, I would like to open the remaining 9 workbooks' load tabs and have all
of the formulas in their Column Ms point to that "master" cell so that they
all update. Again, each of the other 9 books has its own data in their CYR
tabs.

Do these 2 items make sense? It is tricky trying to make this clear. Thank
you for your help.

 

[Guest]

I think what you want to do is use the Indirect Function. Let's say A1 has
CYR!J in it, you'd want to use something like this

=INDIRECT(A1 & row()) to get the entry in CYR!J (and the row number you're
using).

If that's not what you want, let me us know.

HTH,
Barb Reinhardt

 

[Guest]

Hi, Barb. Thanks for replying.

I keyed into the "Master" tab cell A1
CYRJ.
Then I keyed into the "Load" tab cell M1
=INDIRECT(Master!A1&3)/Z1

I was hoping this would return the value in the "CYR" tab's J3 cell divided
by the "Load" tab's Z1 cell. However, I get a #REF! error in the "Load" tab's
M1 cell.

Also, if it matters, I actually need the "Load" tab's cell M1 to use the
ABSolute values from the "CYR" tab, as the original formula was/is
=ABS(CYR!J3)/Z1

What do you suppose I'm missing?

Thanks again.

 

[Guest]

Try putting CYR!J in A1

Hi, Barb. Thanks for replying.

I keyed into the "Master" tab cell A1
CYRJ.
Then I keyed into the "Load" tab cell M1
=INDIRECT(Master!A1&3)/Z1

I was hoping this would return the value in the "CYR" tab's J3 cell divided
by the "Load" tab's Z1 cell. However, I get a #REF! error in the "Load" tab's
M1 cell.

Also, if it matters, I actually need the "Load" tab's cell M1 to use the
ABSolute values from the "CYR" tab, as the original formula was/is
=ABS(CYR!J3)/Z1

What do you suppose I'm missing?

Thanks again.

 

[Guest]

Hooray! Barb, You are so wonderful! That exclamation point was missing. Thank
you very, very much for your help. You've saved the day.

 

[Guest]

You're the second person to give me kudos this morning. I better watch out
or I'll start to believe it.

Have a good one.

Barb