Display Variable Name Not Number?
Feb 27, 2013
I am creating a very simple budget report for a tournament I am hosting. I want to specify whether payment was from Check or Paypal and want each one to resemble a value. This is because PayPal takes a service fee and when we get a 50$ payment it is actually 48.25$. So basically I have 4 columns for the revenue side of the budget report. The school that is registering, Payment type, Number of participants being sent, and total payment received from the school.
My hope is I can have Payment Type*# of participants to equal total payment, but not have a number being displayed for payment type. I would prefer to have payment type say PayPal or Check or at least P and C instead of 48.25 and 50 respectfully. I found a way to insert a name for a value, but when i type =PayPal the value of 48.25 comes up and i cant find a way to format the cells to display PayPal and have it just represent 48.25.
View 3 Replies
ADVERTISEMENT
May 12, 2014
I'm trying to create a completion statement on where I will add up the money my client has sent to me and also add up the money I need to pay out in order for them to complete their transaction. The end result I want is I would like to display whether my client has given me to much money and I need to repay them. Or if they haven't given me enough then I need to invoice them. I need excel to display for me after all the figures have been added and subtracted if the end figure is negative then display in D19 (balanced owed to you). If the end figure is positive (Balance required from you) display in D20.
View 12 Replies
View Related
Aug 8, 2009
On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.
View 4 Replies
View Related
Jan 23, 2008
I am trying to figure out how to make a userform to display the contents of a 2-D array which has a variable number of rows. I want the userform to height of the userform to correspond with the number of rows of data to display.
I don't have much experience with userforms, but here's what I was thinking:
VBA code which would find the # of rows of data and then adjust the height of the userform and the length of the lable (which the data would go in).
View 4 Replies
View Related
Apr 23, 2008
I'm trying to enclose a variable declared as string with a numeric value within parentheses. here's the code I have...
Dim strQuery As String, strPortIP As String
strQuery = Application.InputBox(Prompt:="Please enter the port or IP you want:", Title:="Query Input", Type:=2)
strPortIP = Application.InputBox(Prompt:="Is this a port or IP?",Title:= "Query Type", Type:=2)
If strPortIP = "Port" Or "port" Then strQuery = "(" & strQuery & ")"
I'm getting run-time error '13': type mismatch on the "If..." statement. I'm trying to set the variable strQuery to "(80)", for example, to do a Find within my data. Without the "()" I get ALL occurrences of "80" and that's not what I need...
View 7 Replies
View Related
Jul 9, 2014
I received a request from a coworker regarding custom formatting some numbers in his spreadsheet. Those numbers are serial numbers of 20 characters long. Sometimes in my files I use this custom number formatting ###0 and its enough for the data I handle. But when I tried to use it in his spreadsheet, the following shows:
8456891070060510000
The cell must look like this: 08456891070060510302
The reason to have it like this is due to a Delivery Program requirement to deliver Set-top Units for repair. The Delivery Program do not recognize other format than the above. My coworker takes the data from a spreadsheet, and the spreadsheet needs a custom number format to display the correct number.
find a custom number format to be able to display as my coworker need it??
View 6 Replies
View Related
Aug 25, 2006
Designated Cell = 7
1 10 .034
15 25 .072
35 45 .089
Output Cell = ???
I am trying to find a formula for the output cell. Suppose the designated cell = 7 due to formulas above it. So that number is always given but can change. I then need a formula for the ouput cell that says if the designated cell is greater than or equal to 1, and less than 10, then the output cell should equal .034. So suppose the designated cell = 18.2. Then I would want the output cell to be .072 since it is greater than or equal to 15, and less than 25. The template I am using has 23 rows of numbers like above. I didn't know how to do it for 23 rows though. If/Then doesn't work because it is 23 statements long (well over the seven limit) and the vlookup only works when the cell is equal to a number and not a range.
View 9 Replies
View Related
Mar 11, 2008
I am using a variable named " Totals" as a range type to refference the range in a formula. It works the way I have it.
Dim Totals As Range
Set Totals = [U37: AE37]
Now instead of the absolute refference, I would like to change the row refference by an offset of my current row, using a formula with a varriable. The columns stay the same.
View 3 Replies
View Related
Dec 23, 2013
I have an excel file with a large amount of employee data in it and want to create a search facility that will run on variable search functions and display the information on the screen
I want to be able to enter variable search functions as follows:
Employee Number; shows all information on employee
Division: shows all employees in division (possible from a list of all divisions)
Appraisal Eligibility : Applicable shows all the applicable employees
Job Role: shows all the employees with the same job role (possibly from a drop-list of all roles)
I know its easier in Access, but all records in Excel as a legacy and don't have time to create an access database currently.
View 1 Replies
View Related
Jul 23, 2014
I have a column of numbers ranging from -500 up to 50,000... How do I get it to only display numbers between 0 and 1000?
(I already have a formula attached to this column)
View 5 Replies
View Related
Nov 5, 2008
I have a column that is updated on a regular basis and I want the last entry to be displayed in a specific cell.
View 3 Replies
View Related
Feb 10, 2009
I have a count for each site for certain cloumn headers.
But i want to collate these so that if there is a 1 in the column the it will output it with the column header. But there are 10 column headers and I would like to get a result that has all the columns with 1 in.
eg:
a b c d e f g h i j k l m n o p q (Organic Suites) (Inorganic Suites)
sitea 1 0 1 0 1 1 0 1 0 1 1 1 0 0 0 0 (O1 O3 O5 O6 O8 OS) (I1 I2)
View 6 Replies
View Related
Feb 11, 2009
I need a simple formula that would save me some annoyance. Basically I have like 20 columns filled with various numbers. At the last column I like to take the last number in that row. However each row "last number" is in different column. So how do right formula that would basically take the last value in that row?
I have illustration here.
column 1 column 2 column 3 column 4 Final Value
ROW1 3 5 7 7
ROW2 2 2
ROW3 1 4 4
ROW4 8 8
What I want to do is write formula in Final value that will take the last number in the row.
View 3 Replies
View Related
Jan 12, 2012
Is there an excel format...custom maybe that if I enter 19,500 it will display just 19.5 or just 19?
View 3 Replies
View Related
May 27, 2012
I am looking for a simple formula that would show a minimum number of '1' if the two cells that I am multiplying together are less than '1'.
Example would be the total gasoline for a round trip is $58.65 at .24 round trips. I want it to show a minimum of '1' round trip at the cost of $58.65 and not a fraction cost of $14.08.
I am entering this formula on an iPad's Numbers app (formulas are basically the same)
View 2 Replies
View Related
Dec 13, 2013
The following displays a name and number as the Chart title with VBA...how do I change the code so that is it name plus letter...instead of being Group 1 , Group 2 etc..etc.. it will be Group A , Group B etc..
Code:
.ChartTitle.Text = "Group " & lng + 1
View 3 Replies
View Related
Dec 5, 2006
I work in a finance department and we have MANY numbers consisting of 7 digits. Is there a way to enter in the entire number, but only display the first 4 digits?
View 9 Replies
View Related
Feb 19, 2007
I want to know how to display a "+" (plus sign) in front of postive numbers in my workbook.
View 3 Replies
View Related
Sep 22, 2009
I'm working on a spread sheet to identify the to ten numbers from a list. I can find thes numbers and use ( =MAX('MHF2'!B:B) )for the function to do so. I'd like to dispaly the data in the cell directly to the left of this number... How do I do this?
11302
21000
3484
4440
5428
6419
7367
8333
9315
10258...........
View 2 Replies
View Related
Jan 28, 2013
I have a sheet, along the top is the Months of the year, to the side I have a list of products. At the bottom of each month in a cell I want to display the name of the product that has sold the most for that moth, is this possible:
I can display the highest number i.e Highest item sold in January was 108, but I want it to write the product name.
View 3 Replies
View Related
Jul 3, 2013
I have a few cells that contain decimals that do not appear as decimals. If the number is 50.1, it only shows up as 50 in the cell. I've tried formatting it to a number with 2 decimal places and to scientific and when I do that the number displays as ##. When I do general formatting it only appears as 50.
I used a formula to pull the number into a different cell, however, and in that cell it shows up as 50.1.
Is there anything I can do to make it show up as 50.1 in the original cell?
View 2 Replies
View Related
Dec 1, 2009
I am trying to display the row & column number in a MsgBox. Therefore, my MsgBox should display something like: MsgBox "Apple is in cell A1"
View 5 Replies
View Related
Sep 16, 2013
i have a sample of data in a column,
.....A
A1 20.5
A2 -20.1
A3 19.99
A4 -20.12
A5 20.15
A6 -20.15
In other columns of the same book i have the values;
...............C ..................D ....................E
................x ................CDF ...............1-CDF
1 .......-20.782066 ......0.000302 .......0.999698
2 .......-20.689948 ......0.000311 .......0.999689
3 .......-20.59783 ........0.000321 .......0.999679
4 .........20.505711 ......0.000331 .......0.999669
5 .........20.413593 ......0.000341 .......0.999659
6 .........20.321475 .......0.000352 .......0.999648
how to pick the closest value to any number in A from column C, and then depending on whether its positive or negative, display the corresponding value from D or E.
So if we had -20.77 in col A, it would pick the closest number from col C (In C1) and then display the value from D1. Similarly, if we had +20.4 in col A, it would pick the closest number from col C (C5), and display the corresponding value from E, E5.
View 6 Replies
View Related
Jan 16, 2014
I have a column of numbers that I am trying to get to display based on the value. (The field is formatted as percentage and must stay that way).
I want any number that is < 90% to appear red, but any number => 90% to appear black.
View 4 Replies
View Related
May 24, 2014
I'm trying to analyze the pricing we get from our shipping courier. Although the courier gives us incentive discounts, they also have a minimum charge that supersedes the discount. In other words, suppose the "Minimum charge" is set at $6.24. If the published "list rate" on a package is $10 but we get a 50% discount on all shipments, instead of paying $5, we pay $6.24. Basically, between the post-discount amount and the "minimum charge" we pay whichever is greater/higher.
Now, to the Excel part: I have cells with simple formulas applying our % discount to our carrier's published list rates to calculate the resulting price (i.e, where cell A1 contains published list shipping rate and cell B1 is our discount %, so in cell C1, I have a formula: =A1*B1). I would like know how, if the result of the formula in cell C1 is less than $6.24, that the cell could display $6.24 rather than the actual result?
View 2 Replies
View Related
Jun 8, 2009
I currently have the following in a cell:
=BDH(AHD!A3,"px_last","-5cm","-5cm")
This currently displays as -32 pulling this number out of bloomberg.
I now want to write a formulae in this cell that will display this as 32c if it is negative... and 32b if it is positive.
So if the number pulled out is negative I want it to have a c after it (and please have it delete the minus sign) and if positive, then have a b after it.
View 9 Replies
View Related
May 31, 2009
I have two columns of cells that need to be reformatted: B and C. In the B column excel is reading the cells as time (0:41:55.0), I need it to display those cells as a number like this: 00 41 55.0. In column C the cells look like this: 41 : 09 : 47.3. I need the C column cells to look just like the formatted B column cells (41 09 47.3) Also, even though they have the spaces, the numbers should be read by excel as one whole number (i.e. 41 09 47.3 would be used in a calculation as 410947.3)
View 3 Replies
View Related
Sep 25, 2012
I have find the maximum value from a range by using:
k = Worksheetfunction.max(range("c5"c25")
where k is a variable
Now I want syntax to get the row number of k variable.
View 4 Replies
View Related
Apr 13, 2014
I write a function like below
Function ABC()
dim row as long
row=???(how should I input?)
...
...
end Function.
the formula in "A3" cell is ABC()
When the ABC() run, How to let the function ABC know the row? (answer is 3)
View 10 Replies
View Related
Jan 20, 2013
In my spreadsheet I have around 30 macros... and the majority of these macros make reference to the last row of a worksheet (in my case the last row happens to be 4867). There are some 50 or so references to this last row (albeit in different columns such as '$A2:$A4867' or '$CQ2:$CQ$4867' but in any case, this number '4867' occurs rather a lot.
The last row number only ever changes when I add more rows to the spreadsheet every 3 months or so, and when I do this, I just simply go through each macro carefully and Ctrl+F and replace all mentions of '4867' with whatever the new last row number is. I want to make this a little more efficient by using a name such as 'MyLastRow' and use that in all the macros and formulas so whenever I add new rows to the sheet, I just need to type the new row number in one place, and all the macros will adjust themselves accordingly.
I have tried defining a name for the last row (calling it 'MyLastRow' and giving it a value of '4867' but I cant make it work in formulas even simple ones such as =SUM($A$2:$A"MyLastRow"). I think Im on the right track, but am struggling to find resources and good examples to guide me....
My spreadsheet is extremely large (621 columns x 4867 rows), so I am looking for a very efficient way to do this using non-volatile functions and not using named ranges as I understand these can slow down performance as they use the volatile OFFSET function... and besides, with 621 columns, I do not wish to have 621 defined names!
View 9 Replies
View Related
