Using a single cell refernce to change multiple formulas

[Guest]

I have created a table of suvey results listing multiple businesses each on a
separate line with data from each business in columns to the right of each
business name.
For example:
Revenue Expenses
Company a 'Sheet1'!c3 'Sheet1'd3
Company b 'Sheet1'!c4 'Sheet1'd4
Company c 'Sheet1'!c5 'Sheet1'd5
Etc.

On a separte worksheet, I present data in summary format showing individual
businesses in comparison to the summary by using a chart with cell references
that pull data from the list.

_______________________________________________
Company e 'Sheet1'!c7 'Sheet1'd7
------------------------------------------------------------------
Average ##### #####
Median ##### #####
Minimum ##### #####
Maximum ##### #####

To change the chart presentation above to show a different company in
comparison to the summary data I can use find and replace to change the cell
references from 7 to 3 for example to change company e to company 1.

However, rather than use find and replace, I would like to be able to type
the number 3 into a cell and change all the formulas so that they now refer
to line three rather than line 7. How do I build this single variable cell
reference into my formulas.

 

[T. Valko]

Why don't you just use a lookup formula?

http://contextures.com/xlFunctions02.html

 

[Guest]

Thanks, I considered using a Vlookup formula but I don't think this is what I
require. The Vlookup seems more suited to looking up a specific piece of
data.

I was hoping to be able to change all of the formulas on the spread sheet
with cell references to a single line (the line containing data for a
specific business). Using the table below for example: The formula under
Revenue for Company A below looks like "='Sheet1'!c3". I am trying to
establish a formula that would look something like "'Sheet1'!c(a1)" so that I
can change the contents of a1 from 3 to 4 or 5, or 6, etc to change all the
formulas on my worksheet which refer to that particular line.

Is there a way to make the cell "line" reference the function of another
cell.

 

[T. Valko]

You can use something like this but it isn't very efficient:

A1 = 3

=INDIRECT("'Sheet1'!C"&A1)

Which evaluates to:

=Sheet1!C3

 

[Guest]

Thanks that works perfectly (and I learned something today).

However, I also noted your comment on efficiency and went back again to
re-examine the vlookup option from a fresh vantage point. You are absolutely
right!

The alternative I planned would have resulted in a massive workbook with
multiple separate copied worksheets. I now have a single linked worksheet
that accomplishes everything I was attempting to do with far greater
efficiency.

Much appreciated.

 

[T. Valko]

You're welcome. Thanks for the feedback!