Excel add 1 to reference?

I currently have a reference that calls for a Text in a different sheet. It's "=People!$B1". I want the next cell to read "=People!$B2". I want it to add 1 to the end of the formula until it gets to "=People!$B17" then I want it to reset to "=People!$B1". How do I add 1 to the end other than dragging it?

3 Answers

Relevance
  • Anonymous
    1 month ago

    So, in Orthodox Church you have one cup and one spoon for body and blood of Jesus; no one gets sick. Roman Catholics tried one cup one spoon ritual and got sick with Bubonic plague; if heresy enters Orthodox monastery, then its inhabitants will get sick too. COVID measures (closure, disposable cups, disposable spoons, washing spoon in some liquid after each use, masks, etc.) = heresy. Orthodox churches who participated in COVID measures = no longer pure brides of Christ = now they serve Satan; Patriarch Irenaios 1st blessed Catacomb movement more than five years ago; what you need is antimins (remains of saints sewn into a towel), wax candles, one cup and one spoon

    Source(s): According to the Last Prophet (aka incarnated ARCHANGEL URIEL aka saint healer VYACHESLAV KRASHENINNIKOV) if the last descendant rejects mark of the beast, then his/her direct ancestors go to permanent heaven. To reject mark of the beast, one needs to hide within a 10-15 people group without electronics/documents. Documents are from Satan; burn them. Electronics can be used to track you and to show the antichrist (even on old broken unplugged TV set from 1970's); reject all vaccines, tests, temperature scans
  • 1 month ago

    I'm not sure I understand the problem. And I don't understand the part about not dragging.  But, if I do understand it, here is what I would do: 

    Put "=People!$B1" into the starting cell. Then select that cell and the 16 cells below. (I'd drag using the mouse for that.) Then, I would use "Fill Down". Since there is no "$" before the "1", the next cell down should have a "2", the one after that a "3" and so on.

    Copy the 1st cell to the 18th, and edit the formula if you need to.

    Excel would have a way to do that using the keyboard instead of the mouse. But, I use different software, so I can't tell you for sure how to select 17 cells without a mouse. I can't help you find "fill down."

    You can try this: Go to the first cell. Hold down the shift key while you press the "down arrow" key 16 times.  On my software, "Fill Down" has a keyboard shortcut:  <Cntrl> D. 

    Comment: I didn't know about the OFFSET function mentioned in another answer.  You could use that with the ROW function, which gives the row number of the referenced cell, and the MOD function, which uses division to get a remainder. 

  • 1 month ago

    You want the function OFFSET. 

    =OFFSET(People!$B1,1,0)

    However you would need to restart the function every 17 rows. 

    you might try a helper column with the numbers 1-17 repeating, and then refer to this cell/column in place of the '1', thus following the pattern you set up. 

Still have questions? Get your answers by asking now.