Indirect Formula Modification?

Originally Posted by mycon73

Hi Excel Users,

I have a file that has multiple worksheets in it. The majority of the worksheets is a basic form that helps summarize job requirements for each specific project.

In this file, I have a Work Summary Log worksheet in which I'm extracting some data from the other worksheets to "summarize" all jobs into one nice work summary log.

The following form works great to "automatically" extract desired data from desired worksheets:

=IF(C20="","",INDIRECT("'"&C20&"'!I9"))

C20 = Worksheet name or Worksheet tab name

Example: 03-2008-TNG06307-101_P1

I9 = The desired extracted data
I have a handful of variables for each task, such as:
C = Complete
WIP = Work in Progress

When I use the above formula, I would like the result to be 'C' or "WIP" if that's what is stated from the 03-2008-TNG06307-101_P1 form or worksheet.


If I copy this formulat to the next row or rows, the "I9" variable does not change - The rest of the formula is fine.

How can I modify this formula so that "I9" variable becomes "I10" & gives the desired result from the worksheet in which I'm extracting data from?

Example:

If "I9" = 'C' in worksheet, 03-2008-TNG06307-101_P1, my result will be 'C' on my Work Summary Log worksheet

=IF(C20="","",INDIRECT("'"&C20&"'!I9"))


Is there another formula that works similar to the above formula or description given that will provide desired results?

Thanks

Hi,

The I9 does not automatically change as you drag the formula down since of course by definition it is text which happens to refer to a cell, rather than a reference to the cell itself.

The trick is to substitute a function for the number bit of that text. The easiest function is the ROW() function. So for example if the formula

=IF(C20="","",INDIRECT("'"&C20&"'!I9"))

is on row 20 change the formula to:

=IF(C20="","",INDIRECT("'"&C20&"'!I"&ROW()-11))

HTH

Originally Posted by mycon73

Hi Richard,

Thanks for the reply. I'll give this modified formula a try in a few minutes. What does the '-11' do for the formula?

Thanks


Original:

Code:
=IF(C20="","",INDIRECT("'"&C20&"'!I"&ROW()-11))
HTH

Hi,

Just realised I got that slightly wrong.
It should read:

=IF(C20="","",INDIRECT("'"&C20&"'!I"&ROW()-(Row)-9))

Your I9 cell is a fixed reference. This is of course always in Row 9. Your INDIRECT() function which is text is akin to using I9 as an absolute reference $I$9.

The row in which this formula exists is Row 20, which is the value that the function ROW() returns. Hence if you subtract 9 from 20 you end up with 11 and if you then subtract that from 20 you end up with 9 So the ROW()-11 bit in the formula that says

&ROW()-(Row)-9)

evaluates as

&9

When you copy that same formula down to the next row 21, the same formula is subtracting 9 from 21 resulting in 12, and then subtracting 12 from 21 giving 9 again.

HTH