• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formatting and = cell reference


New Member
Hi, I have several spread sheets that obtain data from other worksheets within a spread sheet. Generally what happens is I copy data provided to me from many different sources and my "top sheet" gets the data and then completes formulas based on the values. As you can imagine, not all the data gets to me in a standard format. For example: one user will use 0'30", another will use 30, and still another will use :30 or best 30 sec. My ultimate is that I need it to read 30 period. so when I use =(cell D5) the format needs to be 30 so that when the data is used in another formula well, it works. Reformatting the cells doesn't work as :30 becomes 3 and the others as you can imagine. Is there anyway that I can achieve this without a bunch of extra work?
I would start by spending time with the others and explain what format and why you need data in a specified format, otherwise you will be forever cursing them and there poor data quality. Get it right at the source.
Hi dragon.fly22,

You could also ask them to use Data Validation on their spreadsheet so that you get it in the format you want it in