Formatting based on relative reference

**bdb484** · 03-25-2011, 07:47 PM

Hi, everybody.

Is there a way to tell a cell with a relative value to format itself to match the cell it's referencing?

I've got a workbook full of sheets that each have dates, dollar amounts, percentages and rankings. At the end, I have a single worksheet that pulls all those figures together with references (e.g., "='1952'!J3") to each of those cells.

The problem I'm having is that the cells in the last worksheet are all unformatted, so the values are all displaying with the general number format rather than as money or dates or percentages. The cell referenced above, for instance, displays as 3.6% in the original sheet, but as 0.036009377 in the sheet it's being pulled to.

Forgive me if this has already been addressed. I'm new here, but I haven't been able to find an answer here or anywhere else.

Thanks,

--Brian

**Richard Buttrey** · 03-25-2011, 08:05 PM

Hi Brian,

I don't see this behaviour.
With say A1 formatted as a percentage, and another blank cell elsewhere formatted as general, putting =A1 in the blank cell takes the format from A1.

I suspect that you have created the formula first and referenced A1 before A1 took a % and was still a general format. Create your independent cells first and then the dependent formulae.

Regards

**bdb484** · 03-25-2011, 10:13 PM

Hi Richard.

I'm not sure what I'm doing wrong, because I'm still seeing the same problem. I've even opened up a new sheet and manually re-entered the formulas, but they're still all coming in unformatted.

Any other thoughts?

--Brian

**Richard Buttrey** · 03-26-2011, 06:57 AM

Hello Brian,

Just to be clear, the order of steps is

1. Enter a percentage in say A1. Check that the format is General
2. In another blank cell where the current format is also General, enter "=A1"

This cell should also display a %

Is this what you get?

**bdb484** · 03-26-2011, 12:42 PM

Ah. I see the miscommunication here.

What you've described works fine, but my source cells are not in general format.

Some of my columns are population figures, other columns are income, while others are spending figures. Then I have relative values that break down per capita income and per capita spending, and all of those columns are then formatted as general, currency and percentages.

Those are the ones that I'm trying to keep formatted.

**Richard Buttrey** · 03-26-2011, 01:37 PM

Hi,

That doesn't matter.
As long as you create the link formula AFTER your source cells are formatted with whatever format is appropriate, AND the linked cell starts out formatted either as General or the same format as its source cell, the linked cell will show the same formatting as the source cell.

It seems to me that your problem is that the linked cell is starting out as something other than General, (or in the example case a %).

Regards