CODEWITHSUNDEEP
excel
jay
jay

Reputation: 13

OFFSET / INDIRECT function trouble

I have two sheets within a workbook, the first with several thousand lines of expenses, separated by individuals, and the second a summary of totals and such.

On the second sheet, I've created a reference to the first to insert each individual's name (i.e. B4: ='Card Transactions'!D89). I'm having difficulty with the syntax for returning the total of each individual's total, which is in a predictable cell in the first sheet relative to the name (down 1, right 7).

I've tried the following: =offset(indirect(B4),1,7) with only a reference error in return. This seems like it should be relatively simple but I'm not having any luck. . . any suggestions?

Upvotes: 1

Views: 782

Answers (2)

girlvsdata
girlvsdata

Reputation: 1644

The following should work for you as long as your data follows these rules:

  • Your columns have headers
  • The names are all in the same column
  • And you are able to set the range with row numbers and not just full columns

Let's say your first sheet is set out like this:

Screenshot 1

And you want your second sheet like this:

Screenshot 2

And your sheets are named:

  • Sheet1
  • Sheet2

This is the formula in B2 of Sheet2: =INDEX(Sheet1!$A$1:$H$9,MATCH(A1,Sheet1!$A$1:$A$9,0)+1,MATCH("Column 8",Sheet1!$A$1:$H$1,0))

And here's what it does:

Screenshot 3

  • Your index array is the entire blue area, this can be the whole sheet but can't be a full column reference, the row number must be specified. In this example, the index array is $A$1:$H$9 and the $ signs mean the range won't move when you drag down the formula, so they are important!
  • Your first match finds the row number, it uses the name (in this case 'bart') as the lookup value, and the purple area as the array. In this example the row array is $A$1:$A$9 and the row numbers must match the row numbers in the index array. The match has a "+1" at the end, so it will find the matching row, then add one row down to get your offset.
  • Your second match finds the column number, it will need to use the name of your column. In this example the column array is $A$1:$H$1 and the column letters must match the column letters in the index array.

Let me know if this doesn't fit your problem, I'm sure we can figure it out.

Thanks.

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152505

use this:

=OFFSET(INDIRECT(MID(FORMULATEXT(B4),2,300)),1,7)

note:

  • this only works if the formula in B4 only contains the one cell reference.
  • This is a volatile function and will cause a noticeable lag in calculations if used too many times.

Upvotes: 2

Related Questions