Is there any add-in/tool that present data in a custom format?I have the following data:
A
B
C
D
1
Shipment
Driver
Customer
Order
2
S1
D1
C1
O1
[Code] .......
The desired output is:
Shipment
Driver
S1
D1
Customer
Order
[Code] ......
I'm trying to make a converter between about 8 various types of values. These are not units like Km or miles or something like that, but rather numbers that represent a specific "hardness value" on a variety of scales (to name a few: HRC, HRA, K)
What I've been doing so far is plotting the two types against eachother and then getting the best trendline I can so that I can use that formula to convert between the two with relative certainty. (for example, when plotting HV vs HRC my fourth order polynomial trendline with an Rsquared of 1 is y=0.0001x4 - 0.0188x3 + 1.0768x2 - 20.709x + 350.69)
My questions comes up where I was hoping to make a window or box of some sort allowing the user to input a numeric value, then selecting the Input units and the hopeful output calculated units, and have the box spit back to the user the conversion.
We have our system set up with 2 folders one with blank forms and the other with these forms filled out and completed (records), is their a trick to removing the data from a completed form fast with out removing tables and text used to make the forms questions? If I could do this it would save time from opening in the blank forms folder then saving in the records folder.
View 4 Replies View RelatedI am trying to apply custom data validation for two cells. The first is data validation in cell B6. The only valid entries should be in mulitples of 100 (e.g. 100, 200, 300, .... etc). The second cell is B12. The only valid enteries should be any number above 100 and not = 100, 200, 300, .... etc). I tried using following formula in data validation for cell B6 but no luck. Couldn't figure out what to use for cell B12 =mod(B6,100)
View 5 Replies View RelatedIf I check the box labeled catagory name on the intial creation or within chart options on the right click menu each data point gets labeled with the name of the catagory.
I would like to label a significant event at one of these data points does anyone know how to do this? If this isn't a built in feature does anyone have a work around that would look good?
I'm looking at what appears to be a custom chart. It is a box divided into 4 equal quadrants. In each quadrant is a percentage with the total equaling 100%. In each of the quadrants there is fill equal to the percent that is numerically written in that quadrant. I'll post a picture with this, but if I was talking cells then....
A1 = 15% B1 = 66%
A2 = 4% B2 = 15%
The backround image behind the number is filled up by volume equal to the percentage indicated. They are also color coded with red representing the highest percentage quadrant filled.
To top that, there are little arrows along the sides of each quadrant indicating an average score.
My first question is WHAT TYPE OF CHART IS THIS THING!, and second can this be done in excel?
I have a custom range I'll be copying from one sheet (a single entry registration form) that I'm adding to another (an "all entries" sheet) with a button assigned to a macro.
[Code].....
I then loop through the range, copying the cell from Sheet1 to Sheet2.
[Code]....
All works perfect, but I need to manipulate the data a little. If B3 from Sheet1 equals a certain string, I want to manipulate the data From Sheet1 B6 & B7 to paste into Sheet2 Column 1, otherwise paste B3 into Sheet2 Column1.
Again, my loop and everything works if I put B3 into Column1, B6 into Column2, and B7 into Column3, but it doesn't play well with the reporting I want to do later from this sheet.
Data Example
[Code]....
What I'm trying to do is
if B3 <> "NEW TEAM" then put B3 (Joe Smith on Sparkles) into the new sheet column1
if B3 = "NEW TEAM" then put "B6 on B7" (Jane Doe on Fluffy) into the new sheet in column1 (where B6 and B7 are the strings, obviously).
I haven't figured out how, in my loop, to access the data in (myCell + 1) and (myCell + 2) while I'm on the first cell (myCell). I could do it by jumping back to the Sheet1("B6") and Sheet1("B7") but I'm trying to write reusable code, and this range might change, so I'm trying to be a little more flexible than hard coding in more cells.
I am trying to use Excel to create some XML output that is formatted specifically for an application that I have. The application expects to receive time data for two columns in hh:mm format, i.e. no seconds. The standard Excel data validation and also the XML time data type all expect hh:mm:ss.
In order to get around this, I have used string as the data type and I want to use data validation to check the user input conforms to hh:mm. I can't see how to write the formula directly, nor how to apply the validation formula to an entire column (as opposed to named cells or ranges), and although I could write a function using regex, I still couldn't use this because all the examples I can find relate to checking a specific named cell.
I am having trouble finding info on this. I am trying to create a custom list to sort data.
I only want to sort two of the letters in my first Level or Column. For instance I want to sort Column A Alphabeticaly as A, B, C, D, but once I get to E, have the rest of the list in a random order that is sorted by my next level. Is there a wildcard character that can be used to have my next sorting level take over once I get to E?
Can I mimic the following in Excel?
http://www.ted.com/talks/hans_roslin...ever_seen.html
It can be done in Google Docs.
It says Compiled Error: User-defined type not defined.
Code below
Sub Macro101()
Dim pp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
[Code].....
I have some data arranged in columns/rows as follows:
Location Name
--------- ------
United States Sarah Buchannan
United States Walter Smith
France Phil Barney
Italy Anna Wilson
Germany Philip Watson
France Neil Anderson
I want to have some function in my spreadsheet that will neatly present the names of the people at a given location in a separate part of the worksheet
e.g. a display of all people in France would have something like:
France
-------
Phil Barney
Neil Anderson
Ideally I would like to populate cells rows in a different column with the multiple results of the lookup ("persons at a given location") and that change in location for any individual would result in the list of persons at a given location being automatically updated.
Did alot of digging around and managed to construct a UDF that would display the results of the lookup in a single cell - it is however not very readable. But, I found out that a cell based UDF is not allowed to populate other cells other that the one where the function is entered!
I am having trouble creating a custom data entry form in excel. What steps would I need to take..
Attached is a example of the data, the Headers are in bold, the highlighted columns are to be drop boxes.
I have a line chart (multiple series) in which I am trying to display custom tooltips on the data points. The source for these custom tooltips are the Notes columns in the first worksheet. The code (which I've copied from an internet site) is working to some degree but I'm not sure how to modify it to pick up the notes column as the content for the tooltips. Also, the example I took this from only had one line whereas I will have 5 when the chart is complete.
View 6 Replies View RelatedI need a function/macro that will find all rows that have a specified value in column A and extract selected columns to a new spreadsheet. More, I need it to do it for every value in column A.
I would also like it to skip creation of new worksheet if value in selected row and column is null.*
I've been trying to combat this problem with advanced filters, which helped, but due to size of the data and range of values in column A it takes an entire day to process manually. Because the data is exported to another program after it's processed, it can't stay in the same sheet, also, linking back to the original sheet doesn't work because the data changes all the time.
I'm looking to create a new file from data in my table. I don't want to even imagine having to do this manually again...I'm optimistic there is a solution. All the data needed to create the file is in the table, but i need it stacked and organized in a weird way. It's almost to hard to explain...so I color coded an attachement that basically says it all. It's pretty much the same thing repeated over and over except the last 2 lines. It's just a really messed up organization. In the real version I need the new file in a new workbook. I'm extremely grateful to anyone who can automate this thing
View 2 Replies View RelatedI require a custom formula for in cell data validation of an 5 digit alphanumeric entry. The valid format is ANNNN (1 x alpha & 4 x numeric). Case of the aplha is not an issue.
View 3 Replies View RelatedI want to add some Data Validation for a cell and want it to validate that the entered data is in the format (DD/MM/YYYY HH:MM)
I guess I need to use a custom validation formula, however cant figure it out
I have an Excel worksheet that contains many data structures, each one consisting of an array of cells, say a3:z13, a14:z24, a15:z25 etc. I can code a custom object to easily access the data in the structure but only after I load the structure into the custom object in memory. What I'm looking for is a way to assign the structure to a custom object for accessing without having to load the custom object. Again I maybe headed in the wrong direction, it may be easier just to hard code it.
View 9 Replies View RelatedI have a data validation in a cell that works like this : if a control cell has the value "F" it validates the input with some dates in a custom list, if not it does nothing.
The formula looks like this :
=IF(F2="F",datelist,"")
It works fine but I also want it to display the drop down list when cell F2 = F, now all i can do is type a value and it checks if it's ok.
Can I do this without using vba ? I will send this to other persons and I don't want the macro warning to pop up when they open it.
I understand the specification of a Function procedure does not provide for it to change the contents of cells. However, I have the need/preference to be able to change cell data by using a custom function (instead of a Sub) because of its ease of use and execution of multiple what-if scenarios - displayed jointly.
Is there a way around this specification/limitation - any way? Are there different techniques (standard or non-standard) to achieve thisr goal?
How to sort a column of data based on a custom list with more than 255 characters.
I have created a named range with 40 entries and then added data validation in the cells of column D using the above named range. However, I want to be able to sort column D in the same order as the named range but the custom data sort lists are restricted to 255 characters.
I have a Drop-Down Menu that looks like:
BOS
PIT
COL
NYR
using List with source =Lists!$B$2:$B$9, which is just the validation list
My Table:
Team Player
BOS Bergeron
BOS Krejci
NYR Nash
... ...
My goal is to limit the end user from choosing BOS more than two times from the drop down menu
I took an Excel course in college but am a bit rusty. I have a customer database that I created and I would like to see certain things after entering a date range that I specify. I uploaded a small excel sheet to show my database and what I'm looking to do. Basically I would like to enter a date range and see these answers:
Total Orders in that date range
Total unique orders in that date range
List of customers (name included) who ordered more than once in that date range
The only columns of data I need to use are: firstname, lastname and orderdate.
Sort Example.xlsx
I have data in Sheet "Schedule" in range A8:C160 (including column headers), but note the length (rows) of the range is dynamic.
A8 = Customer
B8 = Load Time
C8 = Delivery Time
Columns B & C are formatted as h:mm:ss AM/PM, but Column B also includes text..."PRELOAD"
I would like a macro to sort the data based on column B, but the sort must follow a specific order, which is listed in Sheet "TimeSort", range A1:A50 (including header).
The custom order basically has "PRELOAD" sorting at the top, then sorting everything else chronologically starting at 6:00am.
The desired outcome would sort the range based on column B as follows...PRELOAD, 6:00:00 AM, 8:00:00 AM, 12:00:00 PM, 5:30:00 AM
Example is attached (desired sort is reflected).
I'm trying to take the contents of an excel speadsheet and write it to an html file with my own formatting. For example I would like the row:
|32|55|28|
to end up in the html file as:
<tr>
<td class="align-right">32</td>
<td>55</td>
<td class="">28</td>
</tr>
In theory it seemed fairly simple to loop through a sheet and write before and after each cell, but once I tried writing the code I realized I was in way over my head.
In VBA, True takes the integer value of -1. However, in Excel, it is 1.
I am writing a custom function that does arithmetic on a value. However, due to above, if the value is True, my custom function produces a different result than if I wrote an Excel formula to do the same thing.
Is there some way to identify the data type of a value passed into a function? Currently my function looks like this:
' Return the valueToIncrement with its magnitude increased by PctToAdd
Public Function INCREMENTPCT(valueToIncrement As Variant, PctToAdd As Variant) As Variant
INCREMENTPCT = valueToIncrement * (1 + PctToAdd)
End Function
Ideally I would like to identify whether the type is Boolean and adjust the value so as to give the same result as an Excel formula would give.
Below is a macro I recorded to help copy 'Paste Special - values only' data between two spreadsheets that have different colour and conditional formatting in cells.
However, if this is run and there is no data to paste, it throws up the End and Debug error.
Any chance that someone knows that if there is no data sitting on the Clipboard, then a msg box would appear saying "Please select the data you require to be copied" in the message box.
Sub data_input()
ActiveSheet. Unprotect
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B5:AG4804").Select
Selection.sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("B5").Select
ActiveSheet.Protect
End Sub
I have a table with data representing $income vs %Debt burden respectively. The data on the table per respective intersection provides number of accounts and total ($)value of accounts. I have tried to combine the two pieces of information into one column and have a diagonal line separating the two numbers. (sample table attached).
View 2 Replies View RelatedI have four cells c1 thru c4. The SUM of these cells must equal 1. I want to display an error message "sum of cells not equal to 1.0" but only when data has been entered in all four cells.
For instance =SUM(C1:C4, "<>1") should display the error message. Is there a way to write the formula with an IF statement and still use data validation with error message box?
Assumptions:
-Will ignore blanks
-Error message "Sum of cells are not equal to 1.0" until values in all four cells have been entered, not after a value is entered in a cell. (validate expression after all four cells have values or blank)
Scenarios:
c1 = .25, c2 is blank, c3 = .25, c4 = .50 TRUE no error message should displays
c1 = .25, c2 = .25, c3 = .25, c4 = .50 TRUE no error message should displays
c1 = .25, c2 = .30, c3 = .25, c4 = .50 FALSE error message should display after value is entered in c4
etc
What I don't want is for the error message box to display after .25 is entered in c1 because the user is still in the process of adding values. Can this be done using Data Validation in Excel 2010?
