Free Microsoft Excel 2013 Quick Reference

Displayed value VS. actual

I want to copy the displayed vvalue to another application, but when I copy the selected cell, I can only get the actual value.

IE:

A1=28,117

What I get when I copy is 28116.7805

Thanks for any help.


Post your answer or comment

comments powered by Disqus
I have a variable in a vb macro that grabs a number from a cell with
formatting applied as Number with 2 decimals. In the program, if a number
has zeros after the decimal, the variable is assigned the whole number. For
instance, 100.00 comes in as 100. If the number is displayed as 100.01, it
comes in as 100.01. I need to preserve the displayed decimal, and have that
value assigned to the variable instead of the stored value. Is there a way a
vb macro can grab the displayed value vs the stored value of a cell? J

Hi,
On my worksheet, cell A1 and A2 both contain the value:
0.1655555

I've changed the number formatting to three decimal places so it appears on the sheet as 0.166.

In cell A3 I have a forumla =A1+A2 which currently comes back with the value 0.331 (0.1655555+0.1655555=0.331).

Instead of adding together 0.1655555 I want it to add the displayed value of 0.166 so it should then come back with the value 0.332.

I'm sure there is a simple answer to resolve this issue, any help would be gratefully appreciated.

Regards
Iain

In Excel if I enter 100 in a cell I have the format set to display it as 100.0000. I want to use VBA to change the literal value in the cell from 100 to 100.0000. I have no control over the original data and I need to manage large volumes of information in a data transition.

HI Guys,

I am using a COUNTIF formula to search 2 name lists against each other, i then recieve FALSE or TRUE depending on whether there is a match.. as the lists are very long i need to be able to:

-Use CTRL F or another search method to find all displayed values that are TRUE (Problem is when i use CTRL F it does not find the actual results because the formula is the cell value not the word False or True)

-Create a Macro to perform the search against the entire colum (H) for the displayed value of true.

Please can someone help me? Thanks very much in advance!!!!

Cheers
Chris

After searching this board, I am using a check for this issue to alert when the problem occurs, but I'm hoping to tell Excel to fix the problem itself. The issue occurs when a set of data equals 100%, but when fewer decimals are displayed, the displayed total equals 101% or 99%.

Example:
0.6%
1.6%
97.8%

(Now when the display is changed to no decimal points)
1%
2%
98%

As you can see, the data appears to total 101%, and my boss is being a (insert any comment you probably have about your boss) about me not manually reviewing every single report that goes out for this 1% difference. As there are probably 400 different points at which this situation can occur on a daily basis, working harder is not the solution. /rant

I thought I once heard about a formula or setting where Excel will only calculate the displayed values instead of the actual value or formula. Ideally, the formula would know to take the three values, figure out the 100% limitation, and round the largest one down so the other two can round up. The example above is probably 99% applicable, as we would be rounding the largest value down in order to round the other values up.

Meaning intended result:
1%
2%
97%

Though any result that ensured 100% total over the three values would be much appreciated, regardless of which one was reduced.

Your help is appreciated.

I'm trying to write a function that does the same thing as the "precision as
displayed" setting, but only for a certain range (the selected cells). Is
there any way to get at the displayed value of a cell? I think that will help
me accomplish what I want.

For instance, if the ACTUAL value of a cell is 76.5, but it's showing 77 on
the worksheet, I want to make the value of that cell 77.

Or, if the value of a cell is 13.362145, but it's showing 13.36, I want to
make the value of that cell 13.36.

Only, I don't want to do this for the entire sheet, just the selected range.
I'm thinking that if I create a custom function that says

For Each objC In Application.Selection.Cells

objC.Value = objC.DisplayedValue

Next

Only, I can't seem to find what that "displayed value" property is.

Any help would be great. Thanks.

--
Hmm...they have the Internet on COMPUTERS now!

Hi,
I'm new to VBA. I previously use Data/Validation to set up a drop-down
list for one of the cells. For example, I would go to Data/Validation
and choose List under "Allow" and specified "H,M,L" under "Source".

I need the cell to have a value of H, M, or L but our user would like
to see the drop down list as:
H - HIGH
M - MEDIUM
L - LOW

My question is how can I accomplish this in VBA macro or if anyone can
suggest a better way to do this.
Thanks.

I am trying to find a way to show the sum of displayed values without having to set the whole work sheet to "precision".

I have a series of financial allocations which are based on % and the results are displayed to 2 decimal places. I would like to sum these as per the displayed amounts.

I would like to do a reconciliation on the same worksheet - so that I can determine the difference between what is the displayed result and what is the actual calculated result.

Any help would be appreciated. Thank you

Hello:

I've exported from Quickbooks into an .XLS file and the results are very strange. The displayed values are what i want (numbers with two decimal points), but the cell values are all in date/ time format....

For example, cell D4 shows "257.30" but the cell value is "1/10/1900 5:30:00 PM".

If i use @N, the value returned is "10.72917".

Any ideas? I would like to return "257.30" as the cell value.

Thanks,
-John

I need to display values from cells of sheet1 and sheet2 as comment in corresponding cells of sheet3 whose value in sheet3 is FALSE


	VB:
	
 try() 
    Dim Sheet1cellval As String, Sheet2cellval As String 
     
    Worksheets("Sheet3").Range("A1:D4").Select 
     
    Dim cell As Range 
    For Each cell In Selection 
         
        If Worksheets("Sheet3").cell.Value = False Then 
             
            cell.AddComment 
            cell.Comment.Visible = False 
             
             'Getting the value of Cell in Sheet1 for the corresponding cell in Sheet3 whose value is FALSE
            Sheet1cellval= Worksheets("Sheet1").cell.value 
             
             'Getting the value of Cell in Sheet2 for the corresponding cell in Sheet3 whose value is FALSE
            Sheet2cellval = Worksheets("Sheet2").cell.value 
             
             
             'Displaying the values of cells in Sheet1 and 2 as comment for the corresponding cells in Sheet3
            Worksheets("Sheet3").cell.Comment.Text Text:="Difference" & Chr(10) & Chr(10) & "Sheet1: " & Reportcellval &
Chr(10) & Sheet2cellval  & Chr(10) & "" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Cell A1 has the following value= NAMS Team
Cell A2 has the following value= NAMS Lead
Cell A3 has the following value= NAMS Manager

Cell B1 has the following value= Complete Form A
Cell B2 has the following value= Complete Form B
Cell B3 has the following value= Complete Form C

I have created a Combo Box and have the input range set with Column A Range (A1:A3) and Cell Link set as C1 cell. When I select NAMS Team in the Combo Box, then C1 cell displays the value 1. If I select NAMS Lead, then C1 cell displays value 2 and if I select NAMS Manager, then value is 3.

What I want is the following: When I select NAMS Team in the Combo Box, I want C1 cell to display B1 cell text which is Complete Form A (not the # 1). When I select NAMS Lead in the Combo Box, I want C1 to display B2 cell text which is Complete Form B (not the # 2). And so on…

Please help!

Hi,

I found the following in the Help file which I think explains a problem I am
having, but can't find how to make the change it says to make in the last
sentence. Where/how do I make it use the displayed value instead of the
stored value?
Thankyou,
CD

Change the precision of calculations:
When a formula performs calculations, Excel usually uses the values stored
in cells referenced by the formula. For example, if two cells each contain
the value 10.005 and the cells are formatted to display values in currency
format, the value $10.01 is displayed in each cell. If you add the two cells
together, the result is $20.01 because Excel adds the stored values 10.005
and 10.005, not the displayed values. You can change the precision of
calculations so that Excel uses the displayed value instead of the stored
value when it recalculates formulas.

hi,

I'm plotting a Phase of a project in Excel and need to capture Planned vs
Actual Start dates and End dates- how do I go about doing this in Excel
using graphs? - please advise, thanks.

Hi,
how can i display values in 2 cells into a 3rd cell?
eg. cell 1 value is US
cell 2 value is 1234
cell 3 to display US1234 [both cell 1 & 2 value]
regards

My spreadsheet is created entirely from links to another spreadsheet. I
would like to sort this one by the displayed values, but keep the links
intact so I get the data updates. Is this possible without pasting the
values into new columns?

Thanks

Hi,

I'm a project manager for a facility that re-builds large industrial machines. I have developed a system for budgeting our work that seems to work great, but getting budget vs. actual reports has been a stumbling block for us. Our company uses Oracle E-business suite for our resource management, but it's not customized for our purposes and I can't format my own Oracle reports due to beaurocratic reasons. So, I'm left to fend for myself out here and try to come up with my own system for reporting (which I'm happy to do, but I'm constantly working with one foot in unfamiliar territory here, and time constraints as well).

The way my system currently works is that I have developed a budget scheme that works with the way that day is entered into Oracle. So my output data from the actual labor and materials commited to work parrallels my input budget data.

I'm right on the cusp of the solution, but I'm not sure where to turn for the next step: the Budget Vs. Actual report.

The budget data comes from an excel file currently, but I have formatted it as an Access table as well. The actual data is exported from Oracle Projects. I have several options for exporting the raw data (the option I'm leaning towards is a SQL server the company has set up that can query the Oracle Projects tables).

I've set both of these data sources up as tables and have set up pivot table views in access to help see the organization of the relevant data.

The heirarchy of the actuals data is:

WipName (this is the work order name)
OperationSequenceNumber (this is the task code)
ExpenditureType
Group1 (labor transactions)
Sum of Quantity (hrs)Group2 (material transactions)
Sum of CostAmount ($'s)The budget data is as follows:

Unit#
WipName
OperationSequenceNumber(& description)
Budget Amount for Labor Transactions (hrs)Budget Amount for Material Transaction ($'s)
What I'd like to do is mash these together into a report that shows:

Unit#
WipName
OperationSequenceNumber
Mat'l Budget - Material Act'l - Variance - Lab. Budget - Lab. Act. - Var.This seems like it should be really easy to do, but I'm not sure where to start. I'd like to get it working in Access, and then migrate to SQL reporting services so I could put this report on my SharPoint site. Or perhaps I just need to upload the access DB to Sharepoint and use SQL reporting services to define and run the report.

Does anyone know how this report would be made, or know of any resources that I could go to for perhaps setting the report definition up in SQL reporting services?

Any help is greatly appreciated!!! I've attached my simple Access database for reference.

I want to have a displayed value (text) and a stored value (numeric) for a cell, so that when I save it as a text file, I don't want the stored value to be printed as it is supposed to be secured.

My excel is already password protected and some cells are hidden, locked etc.
But when I save it as a text file all the data is written.

In other words I want to encrypt a cell. When I searched other forums, all of them talk about 'password protection' as encryption.

All I would like to do is save only displayed data when saving any other format than my excel program.

Please help!
Thanks

I need to displays values (Equipment P/N & Descriptions) based upon 2 separate list values.

I have a sheet (Mfg Data) that the data is sorted by Mfg and then by product type and finally by P/N & Description. So A5:E:65 is data for the 1st manufacture, A70:E130 is the 2nd Manufacture and A135:E195 is the 3rd Manufacture.

I have sorted the information by columns, RowB is the Manufacture, RowC is the Product Type, Row D is the Descrptions, RowE is the PN.

So think of it this way:
........A......B........C......D........................E
6..............ABC Company
7........................Wall Based Equipment
8................................Description 1........PN 01
9................................Description 2........PN 02
10..............................Description 3........PN 03
11..............................Description 4........PN 04
12..............................Description 5........PN 05
13......................Ceiling Based Equipment
14..............................Description 6........PN 06
15..............................Description 7........PN 07
16..............................Description 8........PN 08
17..............................Description 9........PN 09
18..............................Description 10......PN 10

65.............123 Company
66.....................Wall Based Equipment
67..............................Description 11......PN 11
68..............................Description 12......PN 12
69..............................Description 13......PN 13
70..............................Description 14......PN 14
71..............................Description 15......PN 15
72....................Ceiling Based Equipment
73..............................Description 16......PN 16
74..............................Description 17......PN 17
75..............................Description 18......PN 18
76..............................Description 19......PN 19
77..............................Description 20......PN 20

B6 is "ABC Company", Cell C7 is product type and D8:D13 is the description, E8:E13 is the P/N and etc down the various manufactures, product types & descritions

I have created 2 named ranges: (BTW: when I select ignore blanks in the validation, it does'nt)
NAC_Mfg (which has 3 options)
NAC_Type (Which has 7 options)

So I have a potential of 126 separate values to display (3 NAC_Mfg x 7 NAC_Type x 6 (Descriptions/PN))

Now onto the next step....
On a separate sheet (Product List) in C3 is a list menu for the named range "NAC_Mfg", and in C4 is a list menu for named range "NAC_Type",. Now based on these 2 lists, Cell C6 needs to display a certain value. So if C3 is selected for "ABC Company and C4 is selected for "Wall Based Equipment", then I need C8:C12 to display the information inputed in D8:D12 from the 'Mfg_Data' sheet. Or if C3 is slected for "123 Company" and C4 is selected for "Ceiling Based Equipment" then I need C8:C12 to display D73:E77 the information inputed in D8:D12 from the 'Mfg_Data' sheet

So, how do I do this....

Any help is appreciated...

Thanks
BV

Is there a way, without changing settings in toold_options_calculations, to get a displayed value to be used rather than the cell value?

The cell value in A1 is 2.001475
The displayed value is 2.001 as it is rounded that way.

In cell A2 is a reference number, in this case simply 2.0

Cell A3 is to show the difference between A1 and A2 after being multiplied by 1000.

So Cell A3 should show 1 (rather than 0.001).

What I'm getting however is the cell value rather than what it would be displayed (so I'm getting 1.475 instead of 1).

I know you can change the settings of excel to calculate dsiplayed values, but I don't want to do it for all cells - just certain ones.

Also the number of decimal places of A1 will vary so I can't just set cell A3 to a specific number of places.

how can i perform calculation in excel 2000 on displayed value instead of
stored value? e.g i have a dated written in a column formatted as 'yy' (i.e
04 etc). how can i add /subtract from this cell?

Hi There,

I have two sheets.

Sheet one has values in Column A(text), ColumnB(text) and column C(Number)

In Sheet two I want to display values of column A and column B IF Column C value is greater than 90.

Your help is greatly appreciated.

Thanks,
Ragini

Hi

I'm sure this has been asked before but I can't find it in Help or on this
forum. I have many formulas in Excel (2003) where some of them return a
#VALUE, I'm happy with this as I know there is certain data missing.
However, for presentation purposes I do not want to display #VALUE, can I
just display a blank or a zero if the result of a formula is an error???
I've looked at some of the functions available in XL but can't find
anything.

Any help will be appreciated.

Thanks

Rob

Hello,

I have been noticing some strange behavior with some text boxes which are embedded in a worksheet.

Say for instance I saved/closed the workbook with the value of "6" in the text box. When I re-open the workbook and change the value to something different, say "4", it will still display "6" whenever the text box is inactive.

If you click in the text box and make it active, it will then accurately display the new value of "4". We use VBA to read the value of these text boxes using the following code:

The VBA code retrieves the correct value of "4", but it is very misleading to the user that the displayed value is
"6" when the text box is inactive.

Anyone else ever encounter this?

Thanks!
Kyle

PS. It does this in both 2003 & 2007.

Hello,

I'm in need of help to copy and display values within a Background Filled Cell into a blank Cell. For example, in a worksheet, I have A1:B10 with data. However within this array there are some cells with it's background filled with different colours (no conditional formatting).

E.g. the following cells are randomly filled with any colour:
B1
A2
A3
B4
A5 and so on.

Irregardless of what color is being used to fill these cells, is there a way to copy the value of the background filled cell in A1:B10 into C1:C10? Note: If Column A is background filled, Column B will not; and vice versa.

Hope you can help.

Kind regards,
scchong


No luck finding an answer? You could always try Google.