How can I automatically formulate a column while adding values to another EXCEL

0 votes

I have an excel formula for a column that automatically fills in all the business days from date X to the present. However, when I apply a COUNTIF formula to the column to its right, the cells are not automatically filled as they were before; instead, you have to drag down to finish the formulas. I want this to be automatic as well.

The first column is fulfilled with the following formula: =WORKDAY(MIN(Data\[Fecha_Completa\]-1);SEQUENCE(NETWORKDAYS(MIN(Data\[Fecha_Completa\]-1);MAX(Data\[Fecha_Completa\]-1)))), and the second one (which doesn´t fulfill automatically): =IF(COUNTIFS(Data\[Fecha_Completa\];A2)=0;A2;""); in which it is explained that, if the date of the first column is found in the database (Data[fechacompleta]), I want excel to return me nothing, but if it isn´t found, I want excel to return me the same date. Then the problem is that the second column isn´t fulfilled automatically at the same time as the first does.

enter image description here

The formula exists for that value, as you can see in the first image, but once more dates are added to the database (as you can see in the second image when we get to February), the formula ceases to function and forces me to drag it down.

I tried applying the formula to the entire column, but that wasn't what we were going for because it would have made the file heavier.

enter image description here

Feb 16, 2023 in Others by narikkadan
• 63,600 points
407 views

1 answer to this question.

0 votes

Use a # in your range references to base a formula on an array that is dynamically split. As a result, using A2# in place of A2 will cause the formula to automatically spill to the same size.

=IF(COUNTIFS(Data\[Fecha_Completa\];A2#)=0;A2#;"")
answered Feb 16, 2023 by Kithuzzz
• 38,000 points

Related Questions In Others

0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,600 points
793 views
0 votes
1 answer

How can I sort one set of data to match another set of data in Excel?

In addition, INDEX MATCH is a more ...READ MORE

answered Oct 29, 2022 in Others by narikkadan
• 63,600 points
2,355 views
0 votes
1 answer

Excel - How can I get the average of cells where the value in one column is X and the value in another column is Y?

Use AVERAGEIFS ... =AVERAGEIFS(C2:C13,A2:A13,"Yellow Typ ...READ MORE

answered Nov 11, 2022 in Others by narikkadan
• 63,600 points
1,413 views
0 votes
1 answer

How do I transpose a column to a matrix in Excel?

With data in column A, pick some cells ...READ MORE

answered Nov 25, 2022 in Others by narikkadan
• 63,600 points
2,220 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
4,087 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
2,005 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,690 points
986 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
3,710 views
0 votes
1 answer

How to automatically assign a color to the maximum and minimum values in a set of selected cells in Excel?

See Conditional Formatting, which may be accessed ...READ MORE

answered Apr 7, 2023 in Others by Kithuzzz
• 38,000 points
563 views
0 votes
1 answer

How can I set a number for a public variable at userforms in excel?

Try: Public b As Integer Private Sub exa_Click() b = ...READ MORE

answered Mar 26, 2023 in Others by Kithuzzz
• 38,000 points
479 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP