Formulas don't count merged cells

Hi,

What sidd.iths says is true. If you want a formula that you can simply copy down and which will automatically take care of merged cells for you, you'll need a helper column.

I've put in the helper column for you, and put in the new formula. This way, everything gets taken care of automatically.

Don't forget to click the little star to the left of this post if you feel I helped!

Personally, the advice is, don't use merged cells.

I would split them and then use Conditional Formatting to "hide" duplicated entries.

Split all the cells (model and year). Repeat the model and year in cells A4 and B4. Select cells A2 down to B4. Then select Conditional Formatting. Choose New Rule and "Use a formula ...". The formula is: =A2=A1 (no absolutes) and, in the formatting, choose the same sickly green you have used as the fill colour.

That should hide any duplicated values.


Regards, TMS

I think that TheExcelphile's solution is a neat one and could get you out of a hole ... and it does work if there is a third seller.

However, I still don't like merged cells and wouldn't use this approach. It must be a real pain to set it up if you have several sellers for one model.

If you have your "table" and configuration set up correctly, the formatting will be copied automatically when you start entering data on the next blank row. So, nothing to do but type.

Anyway, two solutions, your choice.

Regards, TMS

Never merge cells in a data table it will contribute nothing but grief, as you have found out.

Unfortunately, your colleague is making a basic error in spreadsheet design ... mixing raw data with analysis and presentation. Excel works best with simple tables of data. Given that, you can sort, filter, produce pivot tables and charts ... the world is your lobster.

But hey, it's his spreadsheet, you've told him why it's a problem, we've supported you and offered some solutions ... it's down to him how he moves forward, or continues to struggle. If it were me, I'd back away quietly.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

@JoshDR: welcome to the forum ...

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.