forumla showing entered cell value instead of formatted cell value

[Legacy 93538]

Hi

I have an excel worksheet which i am using to do a forumla in another cell.

However the cell i am getting data from has been formatted so the value in the cell appears as "Jan" but the forumla uses the entered value of "01/01/2011" but I need to to bring back the formatted value.

Does anyone know how to do this?

Thank you

Jessicaseymour

p.s. hope that makes sense

 

[RoryA]

Use the TEXT function:
=TEXT(A1,"mmm")
for example.

 

[Sektor]

You should copy formatting. In Excel 2007/2010 right-click source cell and press "Formar Painter". Then click target cell. It will get the same formatting as source cell.

 

[Legacy 93538]

Hi

Thanks for replying!

I should have given more detail. The formula is being placed in the cell by a macro which loops though a cell range and for each cell in the range it puts a forumla in a column which is "the value of the corresponding cell in column E & - & the cell vallue in row 1 of the corresponding column"

Its the value in the row 1 cell which is formatted so it shows as "Jan" but the forumla uses the entered value.

the code is:

For Each cell In PPWBForm.Range("F4:U644")
    If cell.Value <> "" Then
      PPNewIR.cells(Nrow, 9).Value = PPWBForm.Range("E" & cell.Row).Value & " - " & PPWBForm.Range(Mid("ABCDEFGHIJKLMNOPQRSTUVWXYZ", cell.column, 1) & "1").Value
        Nrow = Nrow + 1
        Application.StatusBar = cell.Address
    End If
    
Next cell

 

[RoryA]

Try:

For Each cell In PPWBForm.Range("F4:U644")
    If cell.Value <> "" Then
      PPNewIR.cells(Nrow, 9).Value = PPWBForm.Range("E" & cell.Row).Value & " - " & PPWBForm.Cells(1, cell.column).Text
        Nrow = Nrow + 1
        Application.StatusBar = cell.Address
    End If
    
Next cell

 

[Legacy 93538]

Hi

Thank you so much working perfectly!!!

Thank you!!