Display Of Marksheet Format
I am having a data of about 500 students and
each student has 6 subjects A,B,C,D,E,and F. Now the thing is that it is not necessary that a
student must give all papers to pass in exam, it depends on what options the student has
selected. We have made a table in a form given below
RollNO. Name. A Mark Grade B Mark Grade and so on till F Mark Grade.
In the next sheet we have used the function V lookup and printed a format of marksheet as
Subject Marks Grade
ALL USING VLOOKUP
Vlookup Vlookup Vlookup
so when in the next sheet we enter the roll no. the name field and the subject marks and grade
field all Come automatically, now let me tell u the exact problem, if a student has not given C
and E subject, because of Vlook up function ans using if function the fields in place of C and E
subject are left blank and the marksheet is not looking good. Now what i want is that if a
student has not given C paper the subject, mark and grade of D paper should come in place of C
paper place,, so that there would be no gap.. if a student has only attempted paper F instead of
View Complete Thread with Replies
Related Forum Messages:
Time Display Format
I am trying to format cells so when i type in 343 it will display as 3:43 like a clock, i have tried all the time formats and they all read back to me as 0:00 or as 12:00, is there a macro or a formula i am missing?
Display Dates In A Specific Format
I'm in the process of setting up an Excel document and I need to be able to have it display dates in a specific format. I need it to express just a month and year such that the month is represented by a letter (A thru L) and the year is expressed as its last two digits such as in the these examples:
May 2012 = E12
Mar 2009 = C09
Nov 2011 = K11 etc.
I want the date to come out in this format regardless of how the user enters it.
Display Data In Horizontal Format
I need to show data across the page, currently the data is arranged in column's, I need it displayed in row's.
Each material has a number of components, it is the components which I want to show across the page, with the different headings.
I have attached the current format and the required format.
Note the spreadsheet I will be working on has thousands of materials and hundreds of components.task.xls
Cell Number Format And Display - 2003
I enter in numbers, eg: 1, 2, 10, 24, 100, 1000 in a consecqutive cell range
They display as: 0.01, 0.02, 0.1, 0.24, 1, 10. Default cell format is general. If I change cell format to Number (with default display of 2 decimal points) it displays: 0.01, 0.02, 0.10, 0.24, 1.00, 10.00. If I also specify no decimal points, it displays: 0,0,0,0,1,10
The only way I can get the numbers I enter to display (and store) as 1,2,10,24, 100, 1000 is to change cell format to text and THEN retype th numbers into the cells. But, then it creates the green error comment in top left of each cell.
Display Currency Format With Text Boxes
What I am trying to do is display my output in currency format. The catch is that the values I am using are not from cells, they are from text boxes that are locked on a user form. The first text box is a base rate with a value of "$2000.00". Second comes a quantity text box with an integer value of let's say "3". Last comes a markup rate which is a text box with a rate of ".10". The calculation is fine, it although it returns "$6600" when it is critical that I display the two decimal places at the end. Here is my code for the calculation:
Me.txtAirfare.Text = "$" & frmAirfare.txtBaseRate.Value * frmAirfare.txtQuantity.Value * (1 + frmAirfare.txtMarkup.Value)
Input And Display Data In Cell In H:mm Format Without Changing To Time
Can i put into the cells B38-AF38, that would display the sum of B20:AF20 + B24:AF24 + B28:AF28 + B32:AF32 +B36:AF36 in a h:mm format.
When imputing the data into a cell in a h:mm format, I need it to display in that cell and formula bar the h:mm format without having to put an apostrophe in front of "h" or ":mm" value. Once complete, this form is going to go to other trainers to use on their computers and I dont think they would remember to keep putting an apostrophe before the data.
B20= 1:15 (1h15m) NOT 1:15:00 AM
B24= :30 (30min)
B28= :45 (45min)
B32= 1:45 (1h45m) NOT 1:45:00 AM
B38= 4:15 (4h15min)
I have attached a copy of the form for better clarification.
It doesnt seem to be complicated, but it's certainly a lot more than I thought.
Time Display / Non Display.
I have 2 columns E and F that I am using, in Column E, I enter a time say 22:43:00, column F currently ads 15 minutes to what ever is in there with '=E3+TIME(0,15,0)' which is fine except if column E has no time in it when it automatically puts in 00:15:00, hov can I get the cell not to display anything in there is no value in column E.
Userform Date Format Reverting Back To Us Format On Change Event
I have created a userform but I am having extensive problems with the date formats.
My system is set to UK and short date is set to: DD/MM/YYYY
When I used code to add the values in the userform to the spreadsheet, any that contained a date format would revert to the US format.
So I finally figured out to use DateValue to format it correctly for example: ...
Save Conditional Format Colour But Delete Condition Format
I have a Sheet ( Named "Summary" for Example ) of about 4,000 Rows that has a LOT of Conditional Formatting.
I Added Another 100 Rows this Morning and when I Tried Saving it a Message Saying that Not All the Formatting for the New Data that had Been Added had Been Saved. Is there a Macro or Something I can Run that will Make the Conditionally Formatted Cells Stay the Colour that they are but Delete the Conditional Formatting Part of it Achieved Using "Format" & "Conditional Format" from the Menu Please.
Ideally I would like to be Able to Enter the Number of Rows ( From Row ? to Row ? ) that I want this to Apply to.
Change Cell Format Based On Existing Currency Format
I would like to have a single button that changes a range of cells from the USD to EURO to perhaps CAD symbol. Can this operation be performed, such that if I start in dollars, and I click the button once, it shifts by range to EURO (not formulas...just symbol)...and then if I click the same button again, it goes to CAD, and then back to USD with a third click?
Save Number As Text In Txt Format Wo Changing The Format
In order to export an excel table into another program I first need to save the excel file as .txt. The .txt file can then be imported by the other program.
First I however need to make excel understand that the value should be a text and not a value. I therefore format the number as text (0000150235) by adding "0000150235". After saving the file as .txt the format changes from "0000150235" to """0000150235""". I do however need the format in the .txt file to be "0000150235". Can anyone tell me how I can save "0000150235" as .txt and get the value "0000150235" in the .txt file.
Format Climatic Data From A Weather Station Into A Desired Format
I need to format some climatic data from a weather station into a desired format. There are a lot of cut&paste and transposing involved. I think it will be easy but tedious for anyone trained in VB to write a macro but unfortunately I am not. I've tried recording a macro but it turns out that it is not general enough to deal with all the spreadsheets that I've got.
I've attached a spreadsheet which shows the original format (in sheet 1) and the desired format (in sheet 2). To briefly describe, I only need the temperature data for 8am and 2pm, the rest of the information in sheet 1 is useless to me.
Vlookup Function: Re-format The Information Into A Horizontal Format By Week
I have a forecast which is sorted by product code by week vertically. I need to create a lookup to re-format this information into a horizontal format by week. I have started this by transposing the information as my attachment shows but I am hoping there is a lookup formula which will be easier and quicker. I have attached the actual document and the data I want to sort is Sheet 2. I have started in Sheet 1. I want to look up the code in column A, then lookup the week number which would be B2 in Sheet 1 and return the value of that Code in that week from Sheet 1.
Format Email Generated By Sheet To Have An HTML Format
I'm trying to format my email generated of excel sheet to have an HTML formal before they are sent out using MS Outlook.
I have googled, searched your forums and also tried to figure out a solution with the existing helps on the internet. I need help on how to format emails genarated out of my excel sheet. I know a bit of HTML syntax but a newbie to VBA. I'm guessing the formatting has to be done in the Emailbody text function in my code.
I'm also trying to understand the logic behind the code and also learning excel vba bit by bit at the minute.
VBA: Format Amts Into Text $ 9.99 Type Format
Need to create a set of aligned text amts from various cells...Tried Format but unable to get right combination...I've looked at many threads and most seem related to getting amt from text instead of reverse.
Cell may contain nothing or a monetary amt, negative or positive. Output needs to be in format of "$9,999.99-" or similar...with leading zeros suppressed but a min of "$ 0.00" showing, so that above/below amts with be decimal point aligned in a fixed font situation.
I've almost gotten my routine finsihed but this is last remaining obstacle.
Pasted European Date Format Coverting To US Format
I have a problem where i am just trying to do a simple copy of dates from one spreadsheet to another
18/03/2008 00:00 however when the its copied to the other spreassheet is changes to the US date formate 3/18/2008 0:00 I need to keep the format as date as the date needs to be the current day. How do i get this to remain UK
Convert Text Format To Numbers Format In Vb
how i convert text format into numbers format in vb.
i have a formula in vB:
Private Sub Textbox3_Change()
Textbox3 = Val(Textbox1.Value) + Val(Textbox2.Value)
however..when i sum it up (in excel) using"=sum" formula...it ooes not sum up
I faced an error "number stored as text"..how to i convert it to numbers format in vb.
Format Number To Text With Specific Format
I'll get straight to the point: How can I change this text format from 474556788 to 0-47455678-8 (dash added after 0 and before the last number). I need to apply this to around 5000 rows (can't do that manualy). I want it to be int his format:
474556788 to 047-455678-8
Tks Auto Merged Post;474556788 to 047-455678-8 (This is the wanted format)
Format Currency As Correct German Format
I have a spreadsheet which needs to display currency in the correct format dependant upon whether a contract is in English or German Law. Excel defaults currency format as €310,000.00 however in German this needs to be €310.000,00 i.e. with the decimal point and comma in opposite positions. Does anyone know what expression I can use to format it as the latter?
Format Toolbar & Format Button
When the worksheet is protected, all formatting options are disabled.
Is it possible that when the worksheet is protected, that I can determine what format options are to remain available, ie bold, italics some colours.
Can I cerate a buttons to Bold itatlic etc... but only affect cells that are active or the cursor has selected?
What can I also do to be able to UN-bold etc or return to normal?
Convert A Date Format To A Text Format
In D2 I have: =B2
In D3 I have manually input: (space)3-08
As you can see the CONCATENATE puts the "39569" date in A2 But the second line puts the text date as I prefer. What I would like to do is put in a formula or macro in D2 and down that will change the "Mar-08" to "3-08" so it CONCATENATEs correctly to column A. Simply: I'm trying to avoid manually inputing the text version "3-08" (or whatever M-Y) into D2 down a hundred or so rows!
Date Format: Change Format To Mm/dd/yyyy
I have txtboxes in which I write date in format dd/mm/yyyy but if I want to put date in cell correctly from this textbox in need to declare event on exit -change format to mm/dd/yyyy and then it puts date in desired format which is dd/mm/yyyy and my regional settings are English(uk)
Is there a way to put any date format in textbox and in cell there will always be format dd/mm/yyyy or at least if date is entered in format dd/mm/yyyy that also in cell the same format is putted
Vba Code To Format Date In The Format Dd-mm-yy
I would like to format my cells containg date from any format to the format dd-mm-yy. I was doing this manually with the below function
I would like to automate this using vba. i tried a bit but doesn't get what i want. ultimately i want the date in text(dd-mm-yy) format.
Converting? Format To Text Format
On the attached example i have a list of fractions (in the format ?/???). However, i have a problem when the fraction is 6/4 or 4/6 as excel rounds the nominator and de-nominator down to the lowest value so these fractions become 3/2 and 2/3. For these two fractions only i don't want this rounding down to happen.
I am open to any suggestions, but i was thinking of some vba where on pressing a button it would run a macro that went through the cells in the column and if the cell value is 1.5 (3/2) then format the cell as text and enter the value"6/4". For the 2/3 fraction, i think you may need to say if the cell is greater than 0.66 and less than 0.67 because this would possibly be harder to exact as the number of decimal places involved.
Convert Date Format To Another Format
How do I change a date "10/01/2004" into a format like 20041001? I used concatenate(right(A1,4),left(A1,2),mid(A1,4,2)) but the function takes the serial value of the date as an argument and concatenates that . So I get a different value.How can I get away with it?
Format All Numbers With The Accounting Format
When working in Excel I format all numbers with the accounting format. I often use the single and double underlining feature on the Font tab of the Format Cells dialog box. Sometimes when I use the double underline it only puts (and prints) one underline. I've worked with the formatting many times with little success. The only way I can get the double underline to show up in this situation is to change the vertical cell alignment to be centered and then increase the cell height. This causes me other formatting heartaches. It's like the double underline is there it just will not show.
Convert Cells With Percentage Format To 'same' Decimal In A Column With Mixed Format
I have a column of cells with values - 0.2%, 0.32%, 0.22, 0.5 etc. The cells with % symbol are in ' Percentage, 2 decimal' format while the plain numbers are in 'General' format i.e. column contains cells in either of these formats. I need a macro where I can specify the column and it will select the cells with the % format, convert it to 'General' and multiply the result by 100 eg. 0.2% converted to 'General' becomes 0.002. When multiplied by 100, result is 0.2 i.e. is displayed without the % symbol.
Useform With Display
I have a userform that has 6 textboxes that a user inputs quanities and then in the 7th textbox totals it up. See attached code works great.
What Im trying to do now is have the dollar amount display next to each textbox quanity, textbox quanity X cell value(B5) to give the user the dollar value of each textbox quanity. Then have a grand total at the bottom. Its just to show the user the total dollar value for the item.
Private Sub Accept_Click()
Private Sub cancel_Click()
Private Sub TextBox1_Change()
Private Sub TextBox2_Change()
Display But Differ The Value
I need to know if there is a formula to make a cell display the contents of one cell, but contain the value of another.
In the attached image, I would like cell C1 to appear to say Matt, but contain the value 37.
I know I can do this with custom formatting, but this is tedious for more than a few lines.
Display The Combinations
I have a list of numbers from 1 to 20 form a2 to a21, what I would like to do if possible if figure out many combinations of 5 there are for these numbers, and then second if possible display the combinations.
E.g. say there was a total 250000 combinations then show those 250000
I am using excel to link some info to a PowerPoint presentation we use on a monitor in our employee lounge. This presentation runs in a continuous loop. I figured out how to auto save the excel workbook every 30 seconds so that the presentation that is linked to it will have current info. Anyway my question is that I need to have a sheet in my workbook that will only show current anniversary dates and that employee’s name this way when an employee’s anniversary date comes up it will show on in the presentation.
Display Last 8 Entries Only
I have data on sheet A columns (A:DR) over 400 rows, adding more all the time.
On sheet 2 i would like to display only the last 8 entries (rows) from sheet 1 columns A,B,DO,DQ
So i will end up with 8 rows of data on sheet 2, and 4 columns.
I've seen a few finance excel sheets lately (don't have an example right now... ) that have a total number for something and then in parentheses next to it a percentage of the total is automatically calculated. So I have 5 pens and that's 10% of what I have in my pencil cup --> 5 (10%) is how it is displayed.
Cells Display As #######
I copied text from a large volume of different sources into three spreadsheets. When my webmaster went to import the data from a smaller test excerpt, he made sure that the column with the text in it had the cell formatting changed from "general" to "text".
Once this formatting change was made, some but not all, rows in the spreadsheet instead of displaying the text displayed "########". Other rows were fine.
I have tried using the TRIM command and the CLEAN command on one of the cells in order to see if this clears up the problem. But, my cell still displays as "####". For a smaller, sample spreadsheet, my webmaster fixed the problem by manually retyping the text.
I have too much text in the three spreadsheets to retype this all. Plus, when I tried retyping one sample cell, after having cleared it, I still got the "###" displayed as soon as I entered the "." at the end of the first sentence. But, other cells have periods ending sentences with no problems.
I do not know if there are additional hidden codes or some other problem in the text itself.
Does someone know of a tool or tools to clean text data so it is validly formatted as text? Or, if I should use a series of commands, I want to do them in the right order and do all of them so I don't lose any data. I have three spreadsheets each in excess of 1,000 rows, so corrupting the text is one concern and not having to retype text is the other.