I have grouped cells in a sheet (3 rows by 4 columns ); I reference into this group a cell value in another sheet. This sheet has no grouped cells.

When I setup the first "equals cell" formula in the group, referencing the single row cell value, then drag or copy the formula down the grouped cells, the given value is the third value down in each grouped cell.

I need to get the next row down in each group of cells in increments of 1 not every third value.

Can someone help (no VBA code required)?

  • As yet the only 'fix around' for this is to add 2 blank lines in the un-grouped sheet and the drag-drop works. but this is messy. – AndystUK Nov 7 '16 at 14:52
  • Can you supply an example for both sheets and also the formula's you use? – bvaughn Nov 7 '16 at 18:18
  • 1
    Well this is how the autofill function of excel works. Do you need to have grouped cells like that? If you can get away with increasing the size of the row and only using one row, you can use the autofill. Otherwise I think it's hard to do automate without VBA. There is a possibility it can work using =OFFSET() and =ROW() But I'm not entirely sure how yet. – Christofer Weber Nov 7 '16 at 18:49
  • You say "no VBA code required" - what do you mean by that? Are you telling us that what you're trying to do doesn't require VBA, or, are you not allowed to use VBA, thus any VBA answer won't help? – BruceWayne Nov 7 '16 at 18:55
  • Ok, this is pretty random, but say that Your first group is A1:D3 and you are referring to Sheet2 A1 simply by the code Sheet2!A1. Second group is A4:D6 and tries to refer to Sheet2 A2 right? Well instead of using Sheet2!A2 try using this crazy piece: =OFFSET(Sheet2!A4,COUNTA($A$1:A3)-(ROW()-ROW($A$1)),0) That should kinda autocomplete down correctly, as long as there are no empty fields... and it doesn't work for the first row... Someone can refine it maybe. – Christofer Weber Nov 7 '16 at 19:41

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.

Browse other questions tagged or ask your own question.