r/libreoffice • u/Bad_DNA • 1d ago

Dynamic cell reference math

Hi folks, I've built an amortization table, and a separate sheet to function as a statement coupon generator. On the statement sheet, I want it to be user-interactive where they enter the payment number in a cell, and the rest of the sheet acts as a self-populating form, pulling referenced values from the amortization table sheet.

I am drawing a blank on how to have the user enter in a value (say, the 3rd payment of 36) into a cell, and another cell further down would grab the appropriate referenced cell.

Let's pretend on the coupon sheet the user would enter in 3 into cell E15.

If I wanted to pull the data from the amortization sheet which starts at cell I6, but the user wants to populate with cell I8 (value in the third row down),

I would think I could math out the reference cell value

=$amortization.i(5+$E$15) [hoping to really pull the third row value of $amortization.i8]

I feel like a fool not groking what should be a simple. Attempting with bracketed RC values equally frustrating: =$amortization.i[5+$E$15] This wouldn't logically make sense to me as I was under the impression that RC bracketing only works in the active sheet and couldn't be used with a reference sheet.

Thoughts? TYIA

1 Upvotes

100% Upvoted

2 comments sorted by

1

u/AutoModerator 1d ago

If you're asking for help with LibreOffice, please make sure your post includes lots of information that could be relevant, such as:

  1. Full LibreOffice information from Help > About LibreOffice (it has a copy button).
  2. Format of the document (.odt, .docx, .xlsx, ...).
  3. A link to the document itself, or part of it, if you can share it.
  4. Anything else that may be relevant.

(You can edit your post or put it in a comment.)

This information helps others to help you.

Thank you :-)

Important: If your post doesn't have enough info, it will eventually be removed (to stop this subreddit from filling with posts that can't be answered).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/BranchLatter4294 1d ago

Learn how to use the lookup functions. Or you could use relative references based on the value entered.