Forum Discussion
Help with Recognizing new text
I'm trying to figure out if excel can recognize new text in a column of cells as I type it in. I attached a document below to help. As you can see I have two columns: one is a "JOB CODE" the other "TOTAL". As I type a new JOB CODE in column B (that hasn't been inputted in E4 and E5 already), I want excel to recognize that there is a new JOB CODE and automatically insert that in E6 without me having to input that manually. any help would be greatly appreciated. thank you everyone.
Since you don't have access to the dynamic formulas, you may try one of the methods shown in three different tabs in the attached.
9 Replies
Since you don't have access to the dynamic formulas, you may try one of the methods shown in three different tabs in the attached.
Thank You Subodh! those should work as well. thank you for your time and help with this. I really appreciate it! Subodh_Tiwari_sktneer
Hello spalmer ,
what you describe can be achieved with a pivot table.
First, turn your data entry table into an Excel Table object by clicking a cell in the table and then Insert > Table.
Next, select any cell in the table and click Insert > Pivot Table. Use the dialog to insert the pivot table on the existing worksheet. In the PivotTable Fields pane drag the Job Code into the Rows area and the Total into the Values area.
Now enter new data into the data entry table and refresh the pivot table (right-click any field in the pivot table and select Refresh).
If you already have the new Dynamic Array formulas, you can also use formulas. In the screenshot the formulas are
in cell H5 is =UNIQUE(Table1[JOB CODE:])
in cell I5 =SUMIFS(C:C,B:B,H5#)
These formulas have not been copied down and will extend automatically when new data is entered. No refresh required.
Thank you for the help Ingeborg. I don't have access to the new dynamic array formulas yet, but that UNIQUE formula will do the trick. ill just have to be patient haha. Again thank you so much for the help. much appreciated.
