Excel Formulaes

Try this.

Enter:

=if(A1="", "p", A1)

into cell B1, replicate the formula down the screen as far as is needed, hide column A and use column B instead.

Thanks dear,

But not the solution. suppose i am in cell "A1" and without entering anything in cell "A1" i press "TAB" button. the cell "A1" should have "p" written on it after i press "TAB"

Hi MT, and welcome to CR4,

Although I have taken some elaborate steps to get solutions in Excel, I tend most of the time to take a shorter, although maybe less elegant, path.

For what you are asking, if I understand you correctly, I would create two sheets in the same workbook ... one for the data entry, and one for the results.

PW had the right idea, but if you use two sheets, the results might be a little cleaner ... take a look:

Sorry for the poor .jpg ... I hop you can see what I am doing here. Let me know if this might help. If not, maybe I can or others can think of another solution.

Kind regards ...

Hi DCAD,

Sorry to say but this is not what i asked for. there is only one sheet and i am entering data to cell "A1, A2 ....." and other cells like "B1, B2, C1, C2...ETC". What i wanted to do was " if i skip writing or entering in first column(A1,A2.......) the default value should appear in the skipped cells.

hope you are getting me. i can copy and paste "p" in all cells at a go but its not only "p" it has a meaning too.

Tornado has the best answer. Unless you want to use VBA. Keep It Simple Stupid(KISS)

Not quite sure what you're trying to do. First you say "i want to enter text "p" in all the colums which are left blank." then you refer to entries into just one column.

But if you want a column with 1 everywhere but a specified row, you can enter =IF(ROW(A1)=$D$2,"p",1) in the column you're putting entries in, and copy it down. Put 5 (or the number of any row you want p to appear) in D2.

Cheers........Codey

Control C and Control V are pretty difficult. You could've been done already.

Using VBA,

In the Sheet1 WorkSheet SelectionChange area write the following lines of code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If ActiveCell.Value = "" Then ActiveCell.Value = "p"

End Sub

When running, anyplace that the cursor goes, it will write a p unless you put a number or letter including space in that cell.

Pete Olsen.

To fill the remainder of the sheet, use this code.

Dim I As Integer
Dim J As Integer

Private Sub Worksheet_Deactivate()

For I = 1 To 35

For J = 1 To 25

If Cells(I, J).Value = "" Then Cells(I, J).Value = "p"

Next J

Next I

End Sub

Switch to sheet 2, then back to sheet 1 and the range will be filled with "p"'s

There are many ways to do this, even without macros.

1. Select your target area, CTRL-H for Replace, leave the "find" box empty (so it finds empty cells) and replace with "p". May have to tick the "find whole cells only" box.

2. Formula in a blank column, e.g. B1 = if(A1="","P",A1). Drag the formula to full the range next to the target range. With the filled formulas still select, CTRL-C to copy. Select A1 and Edit -> Paste Special -> Values (I have CTRL-SHIFT-V mapped to this via a macro because I use it all the time). Delete the formulas you created in B1:Bxx

to mtrivedi

If you capture the columns which contain the blanks and use the 'edit - replace' command it will work. Leave the 'replace' line blank and type 'p' in the 'replace with' part then press 'replace all'. Be careful to distinguish between a null cell and a blank cell. If you locate a cell and press 'clear contents' you will get a null cell, but if you press the space bar, followed by the 'enter' key, you will get a blank cell . Both look blank, but the space bar puts an ASCII code 32 character in the cell and 'clear contents' creates null cells.