• 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

    Hui...

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

To Link an excel sheet with Formatting

V

VARGEESH

Member
Hi,


I have a student data i one sheet. I want to have the data in another sheet with formatting.


Eg: The fields are: StudNo, DOB, Fees, DeptNo, etc.


My linked sheet should have the following formatting:-


Condition for StudNo: It should begins with "0".(In raw data it will not begins with "0")


DOB field: The date format should be MM/DD/YY.


Fess field: The amount format should round up with 2 decimals.


Dept field: The dept number sholud begins with "0". (In raw data it'll not contain the "0")
 
Hi vargeesh,


I think the best possible solution is to make a copy of the first sheet and use it as a second sheet. :)


Regards,

Faseeh
 
Hi Faseeh,


There is a solution for certain things.


To insert "0" in dept field (="0"&Sheet1!T2) so we can get prefix with "0".


But i want to insert some formula in the linked worksheet.


Can you help me how to write a formula in a linked sheet.


Regards,

Vargeesh
 
Faseeh,


I just tried. it works.


This is the formula to get the student number in 5 digits.


=TEXT(Sheet1!E2,"00000")


Regards,

Vargeesh
 
Hi vargeesh,


In order to insert a preset zero to each entry in dept field, select the column, right click and select custom number format and enter
Code: 
"0"#
and press Ok, You will get Zero before every number you enter, but i think i am still unable to understand what you really want?


Am I correct?


Faseeh
 
Hi, VARGEESH!


Assuming that your original data is in Sheet1 in columns A:D and so on, that you want the formatted data in Sheet2 in same columns, go to Sheet2 and:

a) in A1 type:

=Sheet1!A1

b) copy down and across as neeeded

c) select columns A and D, Ctrl-1, Number, Custom and enter "0"# as Faseeh stated

d) select column B, Ctrl-1, Number, Date and choose proper format

e) same procedure... can you finish it by yourself?


Regards!
 
Oh I see whats going on here. Hope this helps.


Condition for StudNo: ="0"&linkedcell


DOB field: Press Ctrl+1, go to custom format and enter: mm/dd/yy


Fess field: =roundup(linkedcell,2)


Dept field: ="0"&linkedcell
 
You must log in or register to reply here.
Back
Top