Apache OpenOffice Community Forum

Hi,
Please see my attached spreadsheet.

I am looking for a formula that I can use to return the "Sheet" value (orange background) that corresponds to a code that I would enter manually.
The code would range from rows and columns on the lime green background.

Not really necessary, but if the formula also highlighted any duplicates in the Code range, that would be most helpful.

Can anyone help me please?
Thanks in advance
A

angelsoft wrote: ↑Tue Jul 29, 2025 6:58 pm I am looking for a formula that I can use to return the "Sheet" value (orange background) that corresponds to a code that I would enter manually.

The simplest solution is to use Edit → Find & Replace to search for the code. That doesn't require any formula. Find All will show duplicates.

If you want to use formulas, review this attachment. Spreading the codes across columns in the same row may be convenient for you, but it is difficult to work with that layout in Calc, so the formulas are complicated. Put C2825, C0000, and then C9999 in cell I2 to see what happens. The red color when you use the last two codes is provided by conditional formattting. If you don't understand my formulas, using Find & Replace by be a better solution for you.
[Tutorial] The SUMPRODUCT function
[Tutorial] Ten concepts that every Calc user should know

Read about conditional formatting in Help → Index or in User Guides (PDF) or searching for topics about it in the Calc Forum.

Some of the codes in your attachment have a space before the C, for example cells D7 and E7. I assumed that these were mistakes and removed them before investigating formulas. The formulas I created won't find cells containing mistakes in the code.

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.