[EXCEL] Apply a formula to a column

Solved

Antoine -
karlitodu41 Posted messages 484 Registration date Status Member Last intervention - 21 Feb 2011 at 19:10

Hello,

My question might seem silly, but I'm new to the wonderful world of Excel formulas.

Here’s my problem: I want all the cells in one column to acquire a value based on the cell in the previous column. The issue is that if the cell in the previous column is empty, I don't want it to display a "#N/A".

Basically, I want to avoid using fill handle.

To be more specific, only certain data can be entered in my column E (each cell presents a dropdown menu). All the options in this menu are located in a matrix, and in the column to the right of the matrix, another value is associated with them.
I would like my column F to automatically provide the associated value as soon as I choose a value in E. And if the cells in E are empty, the cells in F should not return a "#N/A".
The formula used is a VLOOKUP, which works well with fill handle, but my goal is for the user to only have to select options and enter values, nothing more.

Thank you

Antoine

Configuration: Windows XP Internet Explorer 6.0

7 answers

Answer 1 / 7

Best answer
The best answer is the one that has been validated by our teams.
Vaucluse Posted messages 27336 Registration date Status Contributor Last intervention 6 453

Just in case, to avoid copying across all lines (it's long):
Select the column
Type the formula corresponding to the first line
Close with ctrl+ enter
This also applies to entering a value in consecutive or scattered cells after a collective selection.
25
1. karlitodu41 Posted messages 484 Registration date Status Member Last intervention 144
  
  Euh, je ne vois pas comment vous faites ça, ça m'intéresse.
  
  0
Answer 2 / 7
Alain42

To be more precise:

=IF(E1 = "";VLOOKUP(.....etc...))

that is to say if the cell is empty then do nothing otherwise the function applies.

and then you can use the incremental copy afterwards.

A small tip you might know:

if in an incremental copy you don’t want a row or a column to increment, you put a $ sign in front.
4
Answer 3 / 7
Antoine

It's indeed the formula I was looking for, thank you very much^^

However, I would like this formula to be applied automatically to all the cells in my column F (unless it's too heavy...). Indeed, I will have to protect almost all the content (the document is not intended for me), and the goal is that nothing (except the cell entries) is modifiable. So no possible incremental copying.

Thank you
1
Answer 4 / 7
Alain42

You make the incremental copy before protecting your document, of course.

And this from the first line to the last line of your sheet.
1
Answer 5 / 7
Antoine

Thank you, it's working correctly...
1
Answer 6 / 7
mha

Hello,

Here is 1 line of information separated by underscores that I need to put into several columns, that is to say:

44900000_040_0_001001_AE.pdf
Column A without underscores:
44900000
Column B without underscores:
040
Column C without underscores:
0
Column D:
001001
I can do the 1st formula for the 8 digits but after that it doesn't work anymore!
Do I need to remove the underscores beforehand, what procedure should I follow?
Thank you in advance for your help.

MHA
1
1. Vaucluse Posted messages 27336 Registration date Status Contributor Last intervention 6 453
  
  Good evening
  
  Don’t expect many responses by clinging to a topic that’s a year old…!
  Here are still two solutions in the file that you can copy from the attachment below.
  Good luck.
  Best regards
  
  http://www.cijoint.fr/cjlink.php?file=cj200807/cijU0oO0PM.xls
  --
  The truth is not possessed; it is only sought.
  
  0
2. Sylv@nor > Vaucluse Posted messages 27336 Registration date Status Contributor Last intervention
  
  >Antoine, Tuesday, July 24, 2007 at 4:36:11 PM
  >(...)
  >Basically, I don't want to have to use the incremental copy.
  Right on point for my problem. Even after all this time.
  
  >Alain42, Tuesday, July 24, 2007 at 5:47:18 PM
  >And there you can then use the incremental copy.
  Right on the manipulation to avoid... It's super cumbersome and totally shaky at the slightest modification, especially related to a DBMS. Furthermore, the statement was precise (rare on forums for me to highlight it with a big thank you).
  
  >Vaucluse, Wednesday, July 25, 2007 at 8:20:14 PM
  >Just in case, to avoid copying on all rows
  Ah, an interesting lead?
  
  >Type the formula corresponding to the first row
  >Close with ctrl+ enter
  Yeah... Another thing to type by hand...
  
  I write in general: I hope the comments come from a secretary's perspective. No, there is no negative aspect to perceive in the previous sentence: I am only talking about what I was taught, that is to say that caricaturally, a secretary (or even a clerk or paper pusher) simply contents themselves schematically with typing and copy/pasting. It's a long and tedious job.
  
  But from an IT perspective (therefore thoughtful), the problem becomes much easier (provided one acts in an orderly manner). Without being an expert (or even a novice in the field), I was able to find the answer, which I write here for all those who may need it (including myself if I forget in the meantime).
  
  I nonetheless started from the hint given in passing by Vaucluse. But by adding a bit of my own touch: macros. We create a new macro (long live the "internet tutorials") by executing the instructions. Once Ctrl+Enter is done, we finish the recording. And we look at the macro and then analyze it.
  
  The addition that was made is the ability to execute the macro when returning to the cell that was pointed to by the cursor. With the following, the case is to copy data from column A to column B: no utility in itself, I agree, but the goal here must be primarily instructional.
  
  And we get this:
  Sub MacroFormuleColonneB() Call MacroFormuleColonne("B", "=RC[-1]") End Sub Sub MacroFormuleColonne(LettreColonne As String, Formule As String) 'Declarations Dim CelluleSave Dim ConvertCountColumn As Integer Dim ConvertCountRow As Integer Dim ConvertCountCell As String Dim ConvertCountCharA As Integer 'Initialisations CelluleSave = ActiveCell.Address ConvertCountColumn = ActiveCell.Column ConvertCountRow = ActiveCell.Row ConvertCountCharA = Asc("A") ConvertCountCell = Chr$(ConvertCountCharA + (ConvertCountColumn - 1)) & ConvertCountRow 'Traitement Columns(LettreColonne & ":" & LettreColonne).Select Selection.FormulaR1C1 = Formule Range(ConvertCountCell).Select End Sub
  
  Depending on everyone's issue, it will be easy to adapt.
  
  In addition, this solution allows, upon loading the Excel sheet, to automatically call our column macro. In the VB linked to Excel, we write in the right place* (i.e., the VB of the sheets and not that of the VB macros and modules):
  Private Sub Worksheet_Activate() Call MacroFormuleColonneB End Sub
  
  * There is a VB project explorer once it is in the active window.
  
  Thus, I hope this long paragraph demonstrates that it is possible to do simple and effective things. Purists will say that this requires the use of macros that must be activated, that there are risks, etc. As for the risks, no, there are none except for macros that we create ourselves or that we understand how they work. Each situation is unique but this will address the majority of cases. Have a good day everyone.
  
  P.S. Certainly, this solution is limiting: it only works on the first 26 columns. Once again, if your case does not match, it needs to be adapted. As always, it is possible to do so.
  
  0
3. Sylv@nor > Sylv@nor
  
  Two lines are unnecessary and should only remain in drafts:
  Dim CelluleSave
  
  and
  CelluleSave = ActiveCell.Address
  
  They can therefore be removed (code optimization).
  
  0
4. zennnn > Sylv@nor
  
  mmm, I'm looking for a similar function
  
  a table should calculate page implementations
  so I have a reference cell giving the number of sheets
  
  I'm trying to apply the formula to all the calculation cells, with a calculated result by valid cell and 0 if I exceed the reference. So my formula is good :-)
  but if I copy it into the other cells, my reference cells change too
  and I don't know how to copy while fixing certain elements in the formula that need to remain static
  
  not easy to explain, a small example:
  
  IF(K34<=F18;A33+2;0)
  
  the analysis actually goes from K34 to K210
  F18 is the reference, so it should always stay F18
  A33 increments up to A209
  as indicated by the formula, if K.. exceeds the reference F18, the calculated cell becomes "0"
  
  It's not a major issue if it's not possible, but I like finding things that make my life easier.
  
  Thanks in advance
  
  0
5. Vaucluse Posted messages 27336 Registration date Status Contributor Last intervention 6 453 > zennnn
  
  Hello
  Maybe a bit late, the answer:
  You simply need to lock the reference cell with the $ sign
  IF(K34<=$F$18;A33+2;0)
  For your information:
  $F18 locks the column when you move the cell down the row
  F$18 locks the row when you move across the column
  $F$18 locks in both directions
  Best regards
  --
  Truth is not possessed, it is only sought
  
  0
Answer 7 / 7
Volkrin Posted messages 2 Status Member

I'm not sure I fully understand your problem, but if you don't want it to display N/A when nothing is selected, you can use the IF() function. For example:

=IF(E1 = "","",E1)

This checks if E1 is empty; if yes, it leaves the cell with the formula empty, otherwise, it puts the content of cell E1.

volkrin
0

[EXCEL] Apply a formula to a column

7 answers

7-zip compression

Record your game and film yourself at the same time

Starting office 2007 on windows 11 home

Orange mail new version

Libreoffice calc: unable to assign a macro to a button

Hello i am reading audio files from a usb key but qua

Fusionsolar for android: unable to connect

Excel subtotals

Wi-fi connection

Mobile data impossible