CODEWITHSUNDEEP
excelexcel-formulaexcel-2010vba
excel
excel

Reputation: 3

auto update formula based on another cell excel

I have formula: =COUNTBLANK(E1:E3)

I want the e3 to continue to auto increment when I add a column to A4.

All help and input is highly appreciated

I am using Excel 2010

Adding more details of what i need to complete this issue:

I've Merged cells A-B, C-D, E-F. A-b = item, c-d = item serial, e-f is date item sold.

Item     |    Serial    |    Date Sold
1             001            01/10/2016
2        |    867       |               
3        |    999       |    02/11/2014

Items in store: 1

If item sold date is blank that means my item should still be in store.

So when I add a new item in A-B I need to auto update My formula reading date item sold. So I have formula =COUNTBLANK(E1:E3) which is dependant on A1-Axx depending on how many items I have in store.

If I add item to A4 I need my countblank formula to see I added a new item and Start counting E4 as a blank item now and show, 2 items in store..

Upvotes: 0

Views: 947

Answers (2)

Forward Ed
Forward Ed

Reputation: 9874

=COUNTBLANK(OFFSET($A$1$,0,0,COUNTA(A:A),1))

Should count the number of blanks in the column E that match your data.

You could also go with:

=COUNTA(A:A)-COUNTA(E:E)

That assumes there is nothing under your table

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152465

This will grow as the data grows:

=COUNTBLANK(E1:INDEX(E:E,MATCH(1E+99,A:A)))

If your data in Column A is a string then use this instead:

=COUNTBLANK(E1:INDEX(E:E,MATCH("ZZZ",A:A)))

Upvotes: 1

Related Questions