0

I am trying to reference cells in Column A of Sheet "Job Summary” from Sheet “Report”.

Except I need to allow 15 empty rows between each referencing formula on “Report” (16 rows total including the row with the formula).

So, dragging down the formula does not work. I thought I could create a filter to hide the 15 rows in the middle, then copy down, but that copies the formula to the hidden rows as well.

The 500 cells to be referenced are in order on “Job Summary” in a column A3:A502..

Need to copy this down 8,000 rows on “Report” so really need a solution.

The first formula should be entered in cell A4 on “Report” referencing Cell A3 on “Job Summary”.

Cell A4 on “Report” ='Job Summary'!A3 Cell A20 on “Report” =’Job Summary!A4 Cell A36 on “Report” =’Job Summary!A5

And so on…

Need a solution to copy down this formula on “Report”.

Improve this question
4
  • 1
    Does this answer your question? Increment Cell Reference in Formula by 10
    – Tedinoz
    Dec 21, 2020 at 23:22
  • Not exactly. I have seen that. I need the formula to be every 16 rows, but referencing only one additional row at the source.
    – Cohutta68
    Dec 21, 2020 at 23:56
  • 1
    That solution allows the referencing formula to reference every x number of rows. Kinda exactly backwards from what I need. I have seen some solutions using mod()and some math -12)+2... but don’t understand them enough to recreate for my 16 row spacing on the cells containing the formula.
    – Cohutta68
    Dec 21, 2020 at 23:59
  • Yes, I agree; I retracted that duplicate reference but obviously it hasn't gone. BUT... I did work out a solution; refer below.
    – Tedinoz
    Dec 22, 2020 at 0:39

3 Answers 3

Reset to default
0

You want to enter a formula in sheet="Report" for the respective value in "Job Summary". The values in "Job Summary" are on sequential rows (A3:502). The first formula on sheet="Report" should be in Cell A4, and then every 16 rows (leaving 15 blank rows between each formula). For example, Cell A4, A20, A36, etc.

There are probably many solutions to this question. This is a quick-and-dirty answer that can be done accurately and reliably in a minute or two. No doubt there are other, more elegant answers (such as that provided by Dimension Datacraft) that would also be solutions to your question.

1 - Report:Cell C1 = 3

  • For the sake of transparency, I created a variable for the starting row on "Job Summary". You could just as easily build this into the formula.

2 - Report: Cell A4

  • ='Job Summary'!A3
  • manual entry of the first value

3 - Report: Cell A20

  • =indirect(address((counta ($A$4:A4)+$C$1),1,4,true,"Job Summary"))

4 - Select and copy the range Report:Cell A20:A35

5 - Select the range Report: A36:A8003

6 - Paste the clipboard value to the selected range.

  • This will create a further 498 copies of the formula, each separated by 15 blank rows, and each referencing the preceding cell on sheet="Job Summary".
Improve this answer
1
  • Thank you for this response! I will also try this first thing in the morning.
    – Cohutta68
    Dec 22, 2020 at 0:46
1

Here is another option to consider:

=ArrayFormula(FLATTEN(SPLIT(FILTER('Job Summary'!A3:A,'Job Summary'!A3:A<>"")&REPT(",",15),",",0,0)))

How It Works

FILTER filters in only non-blank entries from 'job Summary'!A3:3.

To each filtered row is concatenated a repetition of 15 commas, which are then SPLIT at the commas keeping empty text (as signified by 0 in the last parameter of SPLIT). This forms a virtual grid of each non-blank target string with 15 nulls to its right.

That grid is then FLATTENed into a single column.

Note: FLATTEN is still an undocumented Google Sheets function.

Improve this answer
4
  • IMHO, this is the best answer
    – Tedinoz
    Dec 22, 2020 at 22:15
  • Thanks, @Tedinoz. I honestly don't care if my answers are chosen as "Best Answer" in the sense of points or checkmarks. I'm more about efficiency, so I just want someone's answer checked to mark an issue as resolved. These forums prove that there is always more than one way to approach a problem. I just figured this approach might be easier for "newbies" to adjust to their own needs.
    – Erik Tyler
    Dec 22, 2020 at 23:45
  • Thanks for the help today with this. I got it all working well now. Cheers!
    – Cohutta68
    Dec 23, 2020 at 2:11
  • Happy to hear it, @Cohutta68
    – Erik Tyler
    Dec 23, 2020 at 2:34
0

Try this formula in cell A4 on your Report Sheet

=sort('Job Summary'!A3:A,sort(row('Job Summary'!A3:A),mod(Row('Job Summary'!A3:A)-4,16),0),1)

This will just take the range 'Job Summary'!A3:A and separate each row by 15 blank rows. Basically, show the value skip 15 rows show the next value.

You could just put the formula in and leave it, but know that technically this formula is outputting into every cell from 'Report'!A4 down. Meaning if you were to type in a blank cell between two of the values the formula would throw an error (because there is something blocking the formula from expanding it's output fully).

If you don't want to leave the formula and / or need to take action on those blank cells between the values... then simply copy this range and paste as values over itself to make it static.

sample sheet(https://docs.google.com/spreadsheets/d/1RVM_I0e8LB-OstPtnH6t2ReRUviF377gjctKT36wfVU/edit)

Improve this answer
14
  • I copied and pasted the formula: I got "1" in cell A4 (which is right), then blank from A5:A11, then A12=498, A13=436, A14=374, and so on reducing by 62 until A20=2. BTW, the questioner wants 15 blank rows between each formula.
    – Tedinoz
    Dec 22, 2020 at 0:36
  • Excellent. Thank you! I will check this first thing in the morning. It sounds like you are exactly right on with the issue.
    – Cohutta68
    Dec 22, 2020 at 0:45
  • That is correct, needs to be 15 blank rows between each formula. Which means there is a new formula on every 16th row.
    – Cohutta68
    Dec 22, 2020 at 0:46
  • Here is a sample sheet to help and avoid any confusion (docs.google.com/spreadsheets/d/…). @Tedinoz. I'm not seeing that happening on my sheet. And yes sorry for the confusion.. this formula puts the next value 16 cells away (15 blank) I will clarify that in my answer. Thanks for pointing that out.
    – Dimension Datacraft
    Dec 22, 2020 at 0:53
  • Also no worries on leaving the space blank between each formula. Basically leading the entire column blank to allow the formula to function properly. There are no other requirements other than the formula in that column.
    – Cohutta68
    Dec 22, 2020 at 0:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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