find a row in a chart

Hello,

the word "chart" in Excel has a special meaning. It is used for a graph like a pie chart, column chart, scatter chart, etc. A "chart sheet" in Excel is a special tab that contains a graph. The Excel-specific use of these words are not common knowledge, but must be learned in the Excel context. I'm just saying this so you are aware that some people may think of a different thing when you say "chart" or "chart sheet". In Excel terms, your question is not about a chart or a chart sheet, but about a worksheet that has a table with data.

That out of the way, let's get to your question.

A formula can return text or a number, but a formula in a cell cannot make the sheet scroll to a specific row.

A formula in a cell cannot change any other cell, so it cannot highlight a value in a table.

I understand what you want to achieve. The "scroll to the row that has the specified number" can be done with VBA macros. The highlighting can be done with conditional formatting of the table cells. I can provide these approaches if you want to.

But before I do that, let me walk you through some good practice methods of handling data.

In general, I'd advise to avoid using merged cells. Instead of merging rows 7 and 8, put the label into row 8 and increase the row height. Unmerge cell A10 and put the label into cell A12. Make it big and bold, but don't merge any cells.

You state your Excel version as 2007, which means you can use Excel Tables. Excel Tables are a great way to work with structured data and can make your life a lot easier. An Excel Table needs unique column labels, so I suggest that your copy the values from row 11 into row 12.

There is a hidden row in your spreadsheet. Select rows 58 to 62, right-click and unihde the hidden row. Now delete rows 60 and below (the empty rows and the total row).

Next, select A12 (unmerged!!!) to E59 and click Insert ribbon > Table. Tick that your table has headers.

The data is now in an Excel Table with the model code and the dates as the column headers. You can use the drop-down in the Model Code column to filter the table by code. The codes are shown in ascending alphabetical order, so you can scroll to the desired code and select it. The table will then only show the rows with that model code.

Now about summing data in a row below the table:

When you click any cell within the table, you will see the Table Tools ribbon. Here you can tick a box to show the Totals row, which will be showing below the table. You can click each cell in the Totals row and define what kind of data aggregation you want to show: Sum, Average, Minimum, etc.

To insert new rows in the table by right-click > Insert .... or to add a new row before the total row, click the last column in the row above the total row and hit the TAB key.

If you've followed the above, your spreadsheet will now contain a table where you can add new rows of data. The Total row below the table will sum each column. If you want to look at a specific product code, you can use the filter drop-down in the header of the first column.

I'm aware that this may be all new and not quite what you need, but let's take this as a starting point to improve on. The file is attached.

Please let me know how you get on.

cheers, teylyn