2

I have one sheet that needs to pick up singles sells for a lot of other sheets. On the other sheets I have to change the information each month and I work across the rows so I insert a row at the top of the information each month. e.g on the sheet I pick info up from cell e13 is a formula =b13+c13-d13 and I need to pick up the answer from e13 each month. I want my main sheet to pick up from cell e13 each month and not change to e14.

Does anyone know how I can do this please? The $ does not work and I have also tried a few other things which haven't worked.

6
  • 1
    "the $ does not work and I have also tried a few other things which haven't worked." It may help to explain exactly what you have tried. Please edit your question with further information. – 24601 Aug 28 '20 at 13:11
  • As ever, you will get the most efficient and effective help if you share a link to your sheet (or a copy of the sheet) here, being sure to set the sharing permission on the link to "Anyone with the link can edit." – Erik Tyler Aug 28 '20 at 23:39
  • @ErikTyler The OP's question should provide ALL the information to be able to understand the problem and the solution without requiring a spreadsheet as well. In this case however, I suggest that a spreadsheet would add little value. since the data relationships are clearly described in the question. – Tedinoz Aug 29 '20 at 3:56
  • @Tedinoz, I suggest that you answer the question if you understand it as posted. I'm not sure I understand the intent of your personal comment to me (another contributor) here in response to my making the common request for a shared link to the sheet. – Erik Tyler Aug 29 '20 at 3:59
  • 1
    @Tedinoz, thank you for clarifying. A far as I've ever seen, on this site and others like it, requesting a link to a sheet, or a copy of the sheet with confidential data swapped with dummy data, or a representative sheet is the norm. As far as I can figure, it would never be an ethical issue, since it would be up to the user to provide access to a sheet that either didn't contain personal data, or one which they put a little work into adapting or creating for the purpose of getting help. This is also a volunteer service. No one is forcing a user to share anything, ever, to my knowledge. – Erik Tyler Aug 29 '20 at 7:10
2

Use INDIRECT to reference a specific cell. You can write the sheet/cell references longhand in the formula, or build it using variables.

Let's say that you have a sheet "Working", and a sheet "Main". "Working" has a new row inserted every month; "Main" must maintain a link to "Working" Cell E13 despite the new row.

In the relevant cell of "Main", insert this formula:

=indirect("Working"&"!E13")

This will ALWAYS reference cell E13, regardless of how many rows are inserted on "Working".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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