Copy a function to a whole column

**Nimad9** · 07-14-2023, 08:43 AM

Hi Guys!

I do have a simple function in A3 where I look in B3, and if B3 is not empty then A3 will write 1. I will have the same for A4 but it will into B4, and if that is not empty then A4 will write 2, and so on. How do I copy this formula to the whole A column except A1 and A2? I know that I can drag in the small square in the cell of the formula, or dobbelt click on it. If I drag in the small square it will take years to come to the bottom, and if I dobbelt click on that small square, then it will on go to the end of B column where the numbers end. Isn't there a way like A:A for formulas so it knows that it will all the cell in a specific column contains a specific formula?

**Pete_UK** · 07-14-2023, 09:24 AM

If you put your data into a structured table, then the formula in column B will automatically be copied down to the bottom of the table, and if you add more data to the bottom of the table then the formula will also be copied for the new data.

Let us know what formula you are using.

Hope this helps.

Pete

**Nimad9** · 07-17-2023, 02:58 AM

I am not 100% sure that I am understanding what you are meaning about structured table, would it be possible for you to make an example with this formula =if(B3<>"",A2+1), that will be in en entire column A, but not A1 and A2.

**Glenn Kennedy** · 07-17-2023, 03:01 AM

Show us on a simple sample Excel file exactly what you want.

**Nimad9** · 07-17-2023, 03:18 AM

Companies.xlsx

Hi Glenn and Pete

Hope that I have attached the excel the right way

As you can see I want to make a counter, every time a add a new company to the B column, when it should count up in column A.
I know that should can drag in the small square when you writ in a cell or dobbelt click on it, but these two ways do not fill out the whole column A minus A1 and A2.

How hoped that there where a way, and it sounds that way on Pete's answer, and it is only that do not understand his answer

BR Nicolai

**AliGW** · 07-17-2023, 03:21 AM

Please update your forum profile (there's a typo - 36 should be 365).

Clear ALL values and formulae from A2 onwards, then in A2:

=SEQUENCE(COUNTA(B:B),,1,1

=SEKVENS(TÆLV(B:B);;1;1)

**TMS** · 07-17-2023, 03:42 AM

If you need to allow for blank entries in column B, then this in A2 and copied down:

Formula:

Please Login or Register  to view this content.

**TMS** · 07-17-2023, 10:45 AM

Is this resolved now? If so, please provide some feedback and share the solution you chose.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

**Nimad9** · 07-17-2023, 11:13 AM

It is solved I used AliGW way

**Nimad9** · 07-21-2023, 07:57 AM

I thought that it was solved, until I needed to sort in a column because then I got this dialog box
Attachment 837214

I want to sort Column B "virksomheder" from A to Z, but how do I do that then I have used SEQUENS?

Copy of Oversigt over opsigelse.xlsx

BR
Nicolai

**JeteMc** · 07-25-2023, 02:09 PM

Perhaps this will help.
1. Paste the following into cell A2: =IF(B2<>"",SUM(A1,1),"")
2. Press the Ctrl and down arrow keys
3. Press the Ctrl, Shift and up arrow keys
4. Press the Ctrl and d keys
Let us know if you have any questions.

**Nimad9** · 07-26-2023, 03:37 AM

#JeteMc thank you very much it works, but now it takes years for the filtration box to open.

**JeteMc** · 07-26-2023, 10:05 AM

I imagine that is why Pete recommended a table.
In the attached file the data is in a table and the formula for column A is: =ROW()-1
To add another row of data to the select cell C10 and press the Tab key.
Note that the formula in column F is: =COUNTIF(Table1[Måneders opsigelse],E3)
Let us know if you have any questions.

Copy a function to a whole column

LinkBack

Thread Tools

Rate This Thread

Display

Copy a function to a whole column

Re: Copy a function to a whole column

Re: Copy a function to a whole column

Re: Copy a function to a whole column

Re: Copy a function to a whole column

Re: Copy a function to a whole column

Re: Copy a function to a whole column

Re: Copy a function to a whole column

Re: Copy a function to a whole column

Re: Copy a function to a whole column

Re: Copy a function to a whole column

Re: Copy a function to a whole column

Re: Copy a function to a whole column

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Copy function from entire row to column with drag

[SOLVED] Best Method to copy a column and modify cells with function.

function not macros to copy table column from different sheet

function to copy from column a to b

how to copy function in a column skipping cells

copy from one column and paste to another column by (alt + enter) function

can't copy a function down a column

Bookmarks

Bookmarks

Posting Permissions