Offset, tracing where the figure comes from!

**WhieLion** · 08-31-2011, 07:23 AM

Hi all,

I am currently using offset for a dynamic update of prices, and would really like to figure out how to use different font colors for tracing where the latest figure comes from.

Ex.
I have screws in various sizes 10", 12" etc from several suppliers. Supplier 1 is on sheet one and the prices are updated every quarter. (Q1 = red, Q2 = blue etc) See the attached file for concrete example.

How can I see for example from which quarter a figure has been taken when using Offset?

Thank you so much for your help!
Whi(t)eLion

**WhieLion** · 09-01-2011, 01:15 AM

Please, anyone out there knowing how to do this?

Whi(t)eLion

**ConneXionLost** · 09-01-2011, 01:44 AM

Have a look at the conditional formatting.

Hope this helps.

Cheers,

**WhieLion** · 09-01-2011, 07:17 AM

Originally Posted by ConneXionLost

Have a look at the conditional formatting.

Hope this helps.

Cheers,

Thanks for your feedback, however I've had a look at it, and as far as I understand the conditional formatting it doesn't solve my issue.

Any other ideas?

Thanks

**WhieLion** · 09-02-2011, 03:42 AM

ConneXionLost mentioned the option of conditional formatting, but I'm not so sure now if I really understand it - I've tried it but I don't get much out of it, for my use

This really seems to be a difficult question!

Thanks for your time
Whi(t)eLion

**ConneXionLost** · 09-02-2011, 11:52 AM

I just realized that conditional formatting for 2007 does not allow references to other worksheets (it does in 2010), so my previous solution will not work for you. Sorry!

**Andy Pope** · 09-03-2011, 06:42 AM

Why not add 2 extra columns to your table that return the period information and then base you CF off that.

Personally I think the text information is more usable as the person does not have to remember what colour a quarter is.

**WhieLion** · 09-05-2011, 03:16 AM

Hi Andy Pope,

Good idea, thanks. I hadn't thought about it. On the other hand since I have close to 50 "screws" and multiple suppliers my page gets quite crammed. The colors would make it quite readable, but indeed one would have to remember the colors

Could there be any option for Excel 2007 in how to keep the font formatting from the place of origin, when using for instance offset?

Thanks again, for the contribution

Really appreciate it.

**Andy Pope** · 09-05-2011, 03:43 AM

Those helper columns that display the quarter do not need to be visible or even next to the table only on the same sheet.

Only other option would be VBA code

**WhieLion** · 09-06-2011, 06:36 AM

That's true. I'll give it a shot and see how it looks. Thanks again Andy.

However, if you read this and might have another good idea in how to solve this issue, please feel free to post it here

BR
Whi(t)eLion

Ps. When it comes to VBA I'm clueless - had a look at it some time ago, but didn't really get it. Maybe someday!?

**WhieLion** · 09-08-2011, 02:44 AM

I've decided to go with Andy's suggestion which worked very well, and as I presume that my wish is not possible to implement with Excel 2007. However, I've understood it should work with 2010.

Thanks again to those who contributed!

Whi(t)eLion