formula displays in cell rather than result

Topic

New Reply

I have a formula in a cell that doesn’t resolve. It displays like text

=IF(LOOKUP($B4,’Manual Entry Timeline’!$A:$A,’Manual Entry Timeline’!J:J)#””,LOOKUP($B4,’Manual Entry Timeline’!$A:$A,’Manual Entry Timeline’!J:J),””)

Before I just had LOOKUP($B4,’Manual Entry Timeline’!$A:$A,’Manual Entry Timeline’!J:J) and it worked fine, but it was putting zeros in my cell when the source was empty. So I tried the IF to get rid of it. All that happens is I now see the formula in the cell. What’s up with that?

Reply | Quote

Replies

Hi

Perhaps the cell has been formatted, as text, in which case when you edit the cell any formula will now appear as text.
If so, change the cell number format back to General (or whatever) rather than Text.

Alternatively, you may have accidentally toggled the sheet view to “formula mode” by pressing Ctrl-` (the ` character is at top-left of your keyboard, under the [Esc] key, so press Ctrl-` again.

zeddy

Reply | Quote

Thanks for the quick response. The cell is formatted as general. I’ve tried different formats but no change. And there are many formulas on the page, only this one displays and it only displays since I changed it to include the IF

Reply | Quote

Hi

Re: “Before I just had LOOKUP($B4,’Manual Entry Timeline’!$A:$A,’Manual Entry Timeline’!J:J) and it worked fine”

To stop it displaying zeros when the lookup is empty, just prefix your formula like this:
=””&LOOKUP($B4,’Manual Entry Timeline’!$A:$A,’Manual Entry Timeline’!J:J)

zeddy

Reply | Quote

Cool! That works.
Now, how can I bring the formatting with the data in a lookup. I want the result to look just like the source. It has both text and cell formatting.

Reply | Quote

You aren’t going to be able to do this with a formula.

Why can’t you just format the cells that contain the formula with the same format as the source.

To carry over the format you would need some VBA.

Edit: How about using conditional formatting to apply the format you desire?

Reply | Quote

The format of the source will change week to week so we can’t manually format the display as it will change. The formatting is manual and up to the person doing the data entry so I can’t cond format. There is also no way to tell what will be in the data so I can’t see how to figure it out. We have to just be able to copy the formatting over as part of the automation. Can you point me in the direction of the VBA idea?

Reply | Quote

Do you have a small sample workbook to illustrate what you have in what cells and what you desire to what cells?

Reply | Quote

The first page is part of the display sheet. The second sheet is data entry. The timeline grey formatting is what I want to show up on the first sheet.

Reply | Quote

How about this as worksheet code on Status Dashboard

Right click tab >> view code >> paste on right side of screen

[Code]Private Sub Worksheet_Calculate()
Dim LR As Long

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

With Sheets(“Manual Entry Timeline”)
LR = .Range(“A” & Rows.Count).End(xlUp).Row
.Range(“B2:G” & LR).Copy
Sheets(“Status Dashboard”).Range(“C2:H” & LR).PasteSpecial xlPasteFormats
End With

With Application
.EnableEvents = True
.ScreenUpdating = True
.CutCopyMode = False
End With

End Sub[/Code]

Reply | Quote

Hi jrb

Your code assumes everything is in the same row order on both sheets.
If it was, you wouldn’t need to use any lookups.
So you could just copy all from the entry sheet and paste the lot.

zeddy

Reply | Quote

Hi zeddy,

Yes I did make an assumption, but after viewing the spreadsheet all rows from one tab matched up perfectly to the other tab. I guess we can get some more information from the OP. Do you have an idea for a possible solution?

Reply | Quote

Hi jrb

As always there are lots of ways of doing stuff in Excel.
If the entries on the [Status Dashboard] were a subset, or in a different order etc, I suspect I would have a [Refresh] button that would trigger a VBA routine to quickly ‘pull’ all the data from the entry data sheet.
To do this, I would probably use a temporary spare column to put a formula to find the matching row number of the Project, then use this to copy the relevant row data (including formats) etc etc

zeddy

Reply | Quote