I have this list wherein the rows might extend beyound 100.
At AG23&24 i have some comments
Can i somehow implement such that even when i scroll down teh rows this too will be appearing, as we see sometimes navigation toolbars appearing in websites when we go down or move up.
I want to know if this feature can be implemented in excel.
I have attached the spreadsheet for reference. All other sheets pull information from the WHITE sheet.
I want the columns for unit price, discount and total to be set to two decimal places but I also don't want it to show anything if the field has a 0 value (so if i only have a few items I don't get lots of o's).
To get the field to not display a 0 if it is empty on the WHITE sheet I have been using "0;-0;;@" which I found on the net. If I use this then it negates the two decimal places.
This also includes the WHITE sheet.
I have a calculated field - if it is 0 I would like to now show the 0 - just a blank cell - can I do that?
View 1 Replies View RelatedI have a data base in a worksheet which is genateated automatically with formula's, I want the a row after the last to be a SUM that moves up or down depending of the amount of the rows.
View 9 Replies View Relatedformula to tell me 9 values. These values are on 9 separate tabs/worksheets. They are always at the bottom row in a specific column - but the row changes every week. I have a totals tab in this workbook. I would like to stop manually going to each tab and pressing Control+End to see the total and manually going back to the totals tab to enter the value. Is there a formula to find these 9 values automatically and display them on my totals tab?
View 6 Replies View RelatedI am trying to display text in the value field within a pivot table. I do not want a count or any number to display. What I am basically trying to create is a weekly schedule for nurses, with the dates on the top and times along the side, with "call type" and client as the text data to display with the value field.
I have attached an excel file with all the data on Sheet 1 and my attempt at a pivot table on Sheet 2. Perhaps there is some way to write a macro to display the data in a similar fashion, without using a pivot table?
Clients and Caregivers - All Schedules-2.xls
right I have 5 columns
A
employee id
B
Date exposure
C
Total Exposure
D
First Name
E
Last Name
I want to be able to filter by employee id I'm using this formula below
=SUMPRODUCT(('Excel Exposure Grapth'!B$2:B$4000<>"")*(MONTH('Excel Exposure Grapth'!B$2:B$4000)=1)*('Excel Exposure Grapth'!C$2:C$4000))
but problem I'm getting it count all dates even after the data is filtered
For Example
if I had 2 employee's
EmployeeID 1 had 6 dates in January
EmployeeID 2 had 10 dates in January
if I was to filter to only show EmployeeId 1 it will only show 6 dates but the formula above counts everything even if filter applied
I want to do a pivot table with a range a1:j(variable)
The variable row number is held in M2
I would like to do a pivot with range
'Data Input Used for Pivots'!$A$1:$J$[m2]
I have a table of data which I am analysing in a Pivot Table. For the majority of the data, the Pivot works very well: however I have a small issue, but it subsequently means the Pivot is useless.
Within the table array that I am referring to, there is a column of data of "Days per employee for a given period". The rows of data within the table array relate to every absence entry per employee, but this final column of data always contains the same figure (although can differ from employee to employee).
When I put the data into the Pivot, I can summarise the absence(s) as a simple sum. However, this final column of data should not be summed, since it is already the sum figure.
This figure though needs to be part of the Pivot, since I need to report on the percentage of absence days per type over the given period. So, the simple representation of =absence day(s)/worked days does not work... well I cannot get it to work. Additionally I have tried features like % of, but nothing.
I am using excel 2007.
I'm making a Pivot table and i'd like that the different field will be displayed on each line.
here is an exampe of what i have and what i want to have
______Group____Average age
Man_____1_________16
_________2_________22
_________3_________25
_________4_________33
_________5_________48
Woman__1_________16
_________2_________22
_________3_________25
_________4_________33
_________5_________48
Result needed
______Group____Average age
Man_____1_________16
Man_____2_________22
Man_____3_________25
Man_____4_________33
Man_____5_________48
Woman__1_________16
Woman__2_________22
Woman__3_________25
Woman__4_________33
Woman__5_________48
Where can i found the option to edit? if it's possible.
I have an xy scatter plot and I'd like a way to put some verticle lines / cursors on the plot. These lines could be moved along the x axis (right and left). Where these lines cross the curve, i'd like the x and y value of the data point on the curve to be given in cells within the spreadsheet.
If this is not possible, is there a way to get the values of the data points in the curve by selecting them. I know you can hover over the data point and it comes up in box, but i'd like those values to be actualy transfered into the spreadsheet.
The location of the data point will vary, so the system needs to be dynamic, i.e, I need to be able to select the datapoint then extract the x and y value.
I need to be able to make an accurate stopwatch in Excel, I want to be able to time a load of Ice Hockey players doing sprints using a laptop on the ice. Excel is perfect cos I can then have a list of players, and just click the button next to the name for them to start..then stop the time. and the Dt is there loaded.
My ultimate goal would be to then modify the code and have have a hardware circuit connected to the parallel port so that the timing could be donw with an external circuit triggering the stopwatch. I'm lookinf for something accurate to hundredths of a second.
Is this possible with Excel and VBA?
I have a userform that allows users to review, input and edit data on a spreadsheet. (thanks to Leith for getting me started on it a while back)
I want to be able to assign names to columns so that they can be rearanged in the worksheet without interfering with the VBA/macro/userform.
For instance currently the VBA for the row = "R" (equating it to whichever row the form is currently on)
I'm having trouble creating a macro that will look at a single URL, and based on what text is in a cell, replace that name and put it into a specific location in the URL. Then the URL will open and the rest is a macro I've written already. This will then be done as many times as need as long as there is text in the column.
So I'm looking to declare the text in these cells as different variables, then have something call these variables into a specific location in a URL, then have that URL (which is a worksheet location) open up. This is because I have many URL worksheet locations on an external website, I'd like for my macro to look at the cells in column A, check "A2" and whatever that name is, and have it placed into the URL. I assume some type of loop would be useful, since I do not want them to open up all at once, but one by one based on the text or variable in the column. Then the URL will open.
Then I can run my macro code which will include copying and pasting, closing and saving. Then go the next cell, check "A3" then repeat the process. The URL for all the worksheets are all the same, except for one specifc name, which is what im looking to replace one by one.
I have a total line that gets moved down with each additional newly inserted row from a vba macro. I need the total line to sum all rows in column "B" beginning with the row immediately above the total line and continuing to 30 lines above that.
View 2 Replies View RelatedI'm looking to have a row at the top of a worksheet which I can type in, so that only the rows below which contain that information will show up. For example, say I have the following 3 rows, 2 columns each:
Cat Feet
Cat Head
Dog Feet
I'd like to have an additional row so that if I typed in "Cat" only the "Cat Feet" and "Cat Head" rows would show up. Likewise, if I typed in "Head" in the proper column only ""Cat Head" would show up.
I have the following code in a macro to open up a find dialog box, but it does not seem to work. I am getting the following message when I try to find something:
Microsoft Office Excel cannot find any data to replace. Check if your search formatting and criteria are defined correctly. If you are sure that matching data exist in this workbook, it may be on a protected sheet. Excel cannot replace data on a protected sheet.
I checked the data I am trying to find and replace and it is correct.
I'm having problems implementing a Select Case statement. Keep getting case without select case at Case condition2.
Code:
Select Case Condition
Case condition1
If Cells(rsRow, rsCol).Value = "" Then
Cells(rsRow, rsCol) = TextBox2.Value
[Code] .......
In the following extract, I would like to have today's date placed into Col K when an entry is made into Col J.
Entry into Col J is from a validation list of 3 options. What I desire is that when one of these options is selected ("Complete"), then I would like today's date to be written into Col K.
Currently Col K has a validation list which comprises dates from 1st of each month from July to June.
I need a fixed date as text preferably, so was thinking the VBA function "Date" on a Change Workbook Sub.
The information is currently part of a Table in Excel 2007 that has about 500 records.
IdeasList
*
J
K
2
Status
Month Completed
3
In Progress
*
4
Complete
Jul-13
5
*
*
6
*
*
Data Validation in Spreadsheet
Cell
Allow
Datas
Input 1
Input 2
J4
List
*
=ValidationList_Status
*
K4
List
*
=ValidationList_Months
*
I have a macro that imports a report. If the Charge Type in column A is BTOREPLX, I need to prefill the field next to it in column B with the text "REPLX", e.g. "REPLXCDROM". I've attached an example of the report.
View 5 Replies View RelatedIn building my pivot table my data that I want to show in the column area is showing up as rows stacked on top of each other. In the column section I'm trying to show Total Budgeted Amount next to Total Actual Amount but on the layout it's showing the two stacked on top of each other is there some kind of hidden key that I'm missing?
View 3 Replies View RelatedI have a simple formula ='info page'!b2&'info page'!b5 in places that on my sheet adds a company prefix to a item number, prefix in b2 and item in b5, we have a client that we cant use prefixes but now when I leave the field blank its giving a zero and thats not good either. I'm pretty sure an IFERROR or something will work but can get the syntax right.
View 5 Replies View RelatedI have a tab called 'Tab1' in which I have column called 'Col1' and 'Col2'.
I have another tab called 'Tab 2' with columns 'ColA' and 'ColB'. When I enter a text in Col1(Tab1) I would like that text to be compared to data in 'ColA' from Tab 2 and when match found then get the corresponding value from ColB and autopopulate it in Col2 of Tab1.
in coverting these fields into a date field.
Example 91306 to 060913
I have encloed the file.
I am trying to count the number of times that data is in a field and in any field the date is greaert than today's date. This date field could be blank and that is where I am having the problem.
Example
See Attached
I want to concatenate two Cells into a single cell BUT have the first field left justified and the second cell right adjusted.
A1 = "John Williams", A2= "Single"
A3 = "John Williams Single"
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.
I'm currently using an IFERROR, VLOOKUP formula to display an availability date for a product.
Atm, it reads some like this
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 2 Replies View RelatedI'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