2

help with this tricky formula is greatly appreciated.

  ='[DATA.xlsm]2018'!$G$6   *this formula works, but . . .

Spreadsheet with formula Spreadsheet with Data

I need the formula to determine where to find the data. See image. The formula in in the VALUE column B. The QTR info in column A tells me where to look for the data in the image.

The four QTR matching fields are D1, I1, N1, S1. Then the OFFSET is 3 columns (for example: D1=1Q18 and col G is the data column, col L for 2Q18, etc) and the OFFSET row is 5 (meaning row 6). {Hopefully OFFSET(5,3) is correct}

On row 10 I need the formula in B10 to read 1Q18 in A10 and {know} to go to '[DATA.xlsx]2018' and find that "1Q18" is D1 and then use the OFFSET(5,3) to find the value "1.80"

When we get down to row 14 on the FORMULA sheet it'll see "1Q19" in A14 and {know} to go to '[DATA.xlsx]2019'. Each year has a new tab-sheet named by the 4-digit year.

I hope I was clear enough and I certainly hope the png images a visible. Thanks in advance for any and all help.

  • It is hard to follow what you want. I have the feeling you may need to use INDIRECT. – cybernetic.nomad Aug 28 '18 at 21:54
  • Refer this superuser.com/questions/1351540. It copies/reads data from one to other. What you need to do is, add Wbook name with Sheet. – Rajesh S Aug 29 '18 at 10:02
1

You're pretty close, you do need OFFSET. But you also need to MATCH the quarter you are looking up. I've split the formula into 2 cells so you can see it piece-by-piece, but once you understand it you can easily combine the two.

I doctored up a fake set of data (I used a worksheet named "Data" in the same workbook): Excel data.

Then I called the other sheet "Report". In the report sheet you first want to MATCH the quarter you are looking up to a position in your data sheet. MATCH has the definition:

MATCH(lookup_value, lookup_array, [match_type])

So we set the lookup_value to the quarter you are after, the lookup_array to the data set, and the match_type to 0 (exact match). Note this only works on a single array (row or col), you can't provide a 2D grid of cells. This will give us a relative position in the array, 1 indexed (first item returns 1, not 0).

We then use this information with OFFSET. The definition for offset is this:

OFFSET(reference, rows, cols, [height], [width])

In this case reference is a single cell. You don't need to provide an array or grid of cells. So in my case I pass Data!$B$1. For rows we need to offset 2 rows down. The amount of columns across is trickier. Since the MATCH function gave as a reference position that starts at 1, we are already moving to the second month. So for cols we need the results of MATCH plus 2.

So finally we get a report sheet that looks something like this: Excel report

  • The note in this comment box says not to say thanks here. Please tell me where/how I can express my appreciation for all to see AND how to I make it known that this answer solved the problem with a great, detailed explanation of how you did it? – GregM Aug 29 '18 at 20:17
  • Glad to hear it helped @GregM. The help section details what to do when someone answers your question. – Ian Aug 29 '18 at 21:15

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.