Using a single cell refernce to change multiple formulas

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BarryL - 10 Jul 2007 00:54 GMT

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.

Reply to this Message

T. Valko - 10 Jul 2007 02:33 GMT

Why don't you just use a lookup formula?

http://contextures.com/xlFunctions02.html

Signature

Biff
Microsoft Excel MVP

>I have created a table of suvey results listing multiple businesses each on
>a
[quoted text clipped - 32 lines]
> cell
> reference into my formulas.

Reply to this Message

BarryL - 11 Jul 2007 09:00 GMT

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.

> Why don't you just use a lookup formula?
>
[quoted text clipped - 36 lines]
> > cell
> > reference into my formulas.

Reply to this Message

T. Valko - 11 Jul 2007 18:15 GMT

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

A1 = 3

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

Which evaluates to:

=Sheet1!C3

Signature

Biff
Microsoft Excel MVP

> Thanks, I considered using a Vlookup formula but I don't think this is
> what I
[quoted text clipped - 57 lines]
>> > cell
>> > reference into my formulas.

Reply to this Message

BarryL - 12 Jul 2007 07:30 GMT

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.

> You can use something like this but it isn't very efficient:
>
[quoted text clipped - 67 lines]
> >> > cell
> >> > reference into my formulas.

Reply to this Message

T. Valko - 12 Jul 2007 18:29 GMT

You're welcome. Thanks for the feedback!

Signature

Biff
Microsoft Excel MVP

> Thanks that works perfectly (and I learned something today).
>
[quoted text clipped - 93 lines]
>> >> > cell
>> >> > reference into my formulas.

Reply to this Message