Hi,
I'd be very grateful for any assistance with a, for me, odd problem that I'm blocking on.
Explanation:
I have a sheet, let's call it 'data', which has values in, lets say A10 through to Z10. These values are just simple decimals, nothing complicated, just typed in values.
I have another sheet, let's call it 'summary' which needs to show values selected from the data sheet but with intervals or 'steps' of five apart. For example:
Cell A10 in the summary sheet is a very simple reference to A10 in the 'data' sheet. So I use { ='data'!$A$10 } and that brings the value from A10 in the data sheet into A10 on the 'summary' sheet.
So far so easy.
Now, in cell B10 of the 'summary' sheet I need to show the value which is in F10 of the 'data' sheet. In cell C10 of the 'summary' sheet I need to show the value in cell K10 of the 'data' sheet.
I need to show values with an 'interval' or 'step' of five columns apart.....
I have been using OFFSET like this in B10: { =OFFSET('data'!$A$10,0,5) } And that works.. just a dead simple offset of 5 columns from the A10 cell in the data sheet.
My problem is that, it does not scale easily (or does it?). For each cell I drag that formula into I need to manually edit the cell reference and or the offset column value.
I was hoping that there was a way to say in cell B10 of the summary sheet "Look at the cell referenced in the formula in cell A10 (Which is cell A10 in the data sheet) add five columns to it and return that value.
Then, if I dragged that formula across into C10 it would be interpreted as "Look at the cell referenced in cell B10 (which would be cell F10 in the data sheet, because I need to jump forward five columns to get the right value). Then in cell C10 of the summary sheet it would look at cell B10, work out which cell is referenced in the formula (F10), then step forward to cell K10 and so on.
Essentially is there any way to look at the cell being referenced in another formula, add five columns on to it and return that value?
Sorry if I have mangled the explanation. Would be very grateful for any assistance! - thanks!
Stuart
Bookmarks