Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










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

follows:

Roll No.

Name

Subject Marks Grade

A
B
C
D
E
F
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

Sponsored Links:

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?

View Replies!   View Related
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.

View Replies!   View Related
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

View Replies!   View Related
Display Cell Format Dialog Box
How to use the xlDialogFormatMain dialog box in vba (Cell Format Dialog Box)?

I don't know the exact require parameters.

Application.Dialogs(xlDialogFormatMain).Show

View Replies!   View Related
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.

View Replies!   View Related
Dates Display As Numbers Even With Date Format
dates column displays numbers even though the format in the format cell options is date format.

the number updates to date format only when i do F2 & enter for each cell. i've about 5000 cells with same problem. how cani do them all in a go instead of doing themone for a time.

View Replies!   View Related
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)

View Replies!   View Related
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.

Example:

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.

View Replies!   View Related
Cell B1 To Display True If Any Of The Values In Column C Match Otherwise I Want To Display FalseOR Function Confusion
Example:....

i m wanting Cell B1 to display true if any of the values in column C match otherwise I want to display false.

A 1
B 0
C 0
D 0

But I can't determine how to get this done.

View Replies!   View Related
Using VLOOKUP To Display Date If Present, If Not Display Today's Date
I'm currently using an IFERROR, VLOOKUP formula to display an availability date for a product.

Atm, it reads some like this

View Replies!   View Related
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.

View Replies!   View Related
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: ...

View Replies!   View Related
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.

View Replies!   View Related
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?

View Replies!   View Related
Time Format: Format Cells To Contain Minutes, Seconds And Hundredths
How can I format cells to contain Minutes, Seconds and Hundredths of seconds to be used in calculations eg 1.24.99 means 1 minute and 24.99 seconds. Example calculation is: 1.24.99 - 1.24.90 =0.0.09

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
Change A Date In Text Format Into Regular Format
I have an excel sheet full of dates in text format and want to convert them into regular format. For instance, one of the dates listed is in text as "60801". I'd like it to show in regular date format of mm/dd/yyyy, so that 60801 becomes 8/1/2006.


View Replies!   View Related
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.

View Replies!   View Related
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

View Replies!   View Related
Format Cell: Changing The Format Of Sheet2 To Exactly Like The Following Image Below
I need in changing the format of sheet2 to exactly like the following image below.
*there are some codes in sheet2 which I think is the place to change the format.

View Replies!   View Related
A1 Format Vs R1C1 Format & Portability
I have a scenario where my VBA code checks the value of a cell, and then depending on the value the address of another cell is stored. I then setup some conditional formatting, and had been using that stored address as a reference.

View Replies!   View Related
Convert Text Format To Numbers Format In Vb
how i convert text format into numbers format in vb.
Currently

i have a formula in vB:

Private Sub Textbox3_Change()
Textbox3 = Val(Textbox1.Value) + Val(Textbox2.Value)
End Sub

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.

View Replies!   View Related
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)

View Replies!   View Related
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?

View Replies!   View Related
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.

if not

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?

View Replies!   View Related
Convert A Date Format To A Text Format
Example......

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!

View Replies!   View Related
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

View Replies!   View Related
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

=TEXT(YEAR(Q2),0)&"-"&(RIGHT("0"&TEXT(MONTH(Q2),0),2))&"-"&(RIGHT("0"&TEXT(DAY(Q2),0),2)).

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.

View Replies!   View Related
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.

View Replies!   View Related
Format Different Numbers To The Same Format (as Text)
How to format different numbers to the same format (as text). Mainly I need code that would format numbers like 25, 25.36 or 254.60 to numbers looking like this 000002500, 000002536, 000025460. They must be of nine digit length with the last two digits as decimals.

View Replies!   View Related
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?

View Replies!   View Related
Check The Date Format Is In Particular Format
In one excel file, i have a date , and i set this to "dd/mm/yy hh::mm am/pm" format. From another file i will open this file and i need to copy that date to this file. My question is , before copying i need to check it is in the "dd/mm/yy hh::mm am/pm" format using vba.

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
Time Format Changes To Other Format
If i export a report to CSV, the Avg Time Spent column as been changes to other format. Even after I change to Time Format in excel, it is not showing the time properly.

View Replies!   View Related
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()
Call AllGood
End Sub
Private Sub cancel_Click()
Unload Me
End Sub

Private Sub TextBox1_Change()
Call totalTextBoxes
End Sub

Private Sub TextBox2_Change()
Call totalTextBoxes
End Sub...............


View Replies!   View Related
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.



View Replies!   View Related
Display Range Value
how can i check the range value if i have the merge cells in the range? i want display it in the msgbox.

View Replies!   View Related
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

View Replies!   View Related
Cannot Display Zeros
I cannot display my zero's in excel. I went to TOOLS - OPTIONS - VIEW - and I checked the ZERO VALUES box, but excel still won't show my zeros.

View Replies!   View Related
Display Anniversaries
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.

View Replies!   View Related
Display 0 Instead #div/0
how to get excel to display 0 instead of this? It pops up everywhere through my spreadsheet but cant get it to display 0.

View Replies!   View Related
Display The Worksheet Name
Each tab in my worksheet is represented by a customer's name. I would like to have inside this worksheet their name displayed.


View Replies!   View Related
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.

View Replies!   View Related
Display The Value Of A Cell
I need to know how to display the value of a cell in another cell. This cell I need to display can change and I need the other cell to do so also.

View Replies!   View Related
Percentage Display
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.

View Replies!   View Related
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.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved