Each week I get new weekly data-metrics. I have a series of formulas that figure LAST 4 weeks average, LAST 8 weeks, etc etc. How do I drop in a new week of data and have my formulas adjust? For instance, my formula figures last 4 weeks average of May Wk 1, May Wk 2, May Wk3, May Wk4. Now it is June Wk1, so I want to figure (without changing any formula) May Wk 2, May Wk3, May Wk 4, June Wk 1.
View 1 RepliesI would like to select a item from a list and have a text box display data from the next column (corresponding row) Also, I would love if I could type something into a second textbox and have that copy onto a third column (again, corresponding row) Also, if the add comment command button could transfer that record to the "commentted items' sheet. I have attached an example.
View 2 Replies View RelatedI've got a data dump that must be edited/converted prior to pushing it into a different main frame system. There can be NO decimals in the end result. Here's the challenge:
______Col A____Col B___Col C___Col D____Col E
Row1__TOT_____1Q_______2Q_____3Q______4Q_____
Row2__20.00____5.00_____5.00____5.00____5.00___
Row3__26.00____6.50_____6.50____6.50____6.50___
Row4___0.00____0.00_____0.00____0.00____0.00___
Row5_139.00___34.75____34.75___34.75___34.75___
Row6__13.00____3.25_____3.25____3.25____3.25___
Starting on Row 2, there's a break down of 4 quarters of data divisible by 4 using the whole number TOTAL of Parts in Col A.
The analyst must first break it down into 4 parts (as done in Col B through E). Then, round up and down appropriately to ensure that NO DECIMAL data is entered into another main frame system.
Can't copy one formula downward because if it ends in .50, the up/down sequence is diff from how it would be rounded if it ended with .75.
Need vba to be smart enough to evaluate "IF" and apply "THEN" according to what it sees in Col B's data and convert B, C, D and E appropriately. (I will tie this to a button within a toolbar to run the macro/vba when the conversion of B thru E is needed.
EXAMPLE:
IF B's data ends in: xx.00 or xx.50, then convert those 4 quarter's data accordingly:
ROUNDUP, ROUNDdown, ROUNDUP, ROUNDdown, (this one doesnt really matter but works)
IF B's data ends: xx.25, then convert those 4 quarter's data accordingly:
ROUNDUP, ROUNDdown, ROUNDdown, ROUNDdown,
IF B's data ends: xx.75, then convert those 4 quarter's data accordingly:
ROUNDUP, ROUNDUP, ROUNDUP, ROUNDdown,
Using ROW 6 as an example for how to handle xx.25's:
B6 would convert up to 4
C6 would convert down to 3
D6 would convert down to 3
E6 would convert down to 3
TOTAL after conversion = 13
Using =A7&","&B7 to add data from two columns into one, separated by a comma. What about multiple columns, still separated by a comma? See Example in attachment.
View 2 Replies View RelatedI want to total cells that have data in them across a row. I have a name in Column A and then in Columns C-Z I have data. In Column B I want Excel to scan across cells C-Z and if a cell has data in it (it will be text) I want it to add one to the total in cell B. Would CountIF do this with text involved?
View 7 Replies View RelatedI have a External Data Table, but I wanted to add a extra column to it for comments. I just need to be able to write down what I did with that particular order or things like that. I did make a column, but the problem is whenever I update the table the comments column fields stay static I mean they don't move with their row. So it ends up the comment belonging to a row is now on another row.
is there a way I can tell excel to move the cell with the record or something like that.
I have two columns D and E that contain dates and values. Column D has the dates and Column E contains the corresponding values.
I need a macro to take all of the dates in column D (it is pulled from a different macro so the length of the two columns varies every time but starts at D2) and paste the first value in W1, the second value in AE1 , and so on (every eight cells).
I need to do the exact same thing with Column E except start at Y1, then AG1, etc (still every eight cells).
I will try to post an example later.
I have a userform that I'm using to add data to a worksheet, with the following
Private Sub CommandButton1_Click()
Dim OutSH As Worksheet
Set OutSH = Sheets("Sheet1")
OutSH.Cells(nextrow2, 1).Value = Surname.Value
OutSH.cells(nextrow2,2).value = ID.value
OutSH.cells(nextrow2,3).value = Date.value
......
I need to ensure that duplicate entries are not made for the same person on the same date. The ID is unique to each person.
IF statement that can check for a duplicate and then come up with a dialouge box with some custom text, and then exiting the sub?
I need to use SQL decode functionality in Excel. I tried but Excel won't accept decode. Is there a way to get the same effect in Excel?
Example: quite often, people use letters to represent numbers, like 1234D = 123.44, 5432G=543.27 in text files. To converter from letters to numbers, I can use decode (substr(A1,5,1), 'A', 1, 'B',2,'C',3,......,'{', 0, null).
Not sure how Excel handles this?
Using VBA... I have Column N (eFIUnitRebate) which I want to swap with Column M (FFUnitPcnt)
Now with Column M as eFIUnitRebate and N as FFUnitPcnt I want to create an extra column (adjUnitCost) where Column N is and push FFUnitPcnt to become column O, etc.
Here is how the whole process should work:
Swap M and N
Insert new Column in N
Insert new Column in P
Insert new Column in Q
Insert new column in R
There is more information on the sheet after these columns so for all column inserts it needs to push whatever was there over.
So the layout is as follows:
J
K
L
M
N
O
P
Q
R
Units
UnitCost
UnitRebate
[Code] .....
These columns are thousands long, so I would like the formulas for them to be applied to the whole column as well.
I have a standard multi-page excel workbook in Excel 2013. I have built a user form that will allow for user input into my main excel page. This inpu includes many different variables, including one called "Paint". The value of paint can currently be selected from a drop down box that pulls its values from a spreadsheet in the workbook (Paint!A$1,A$50) but here's the issue. I have over 50 possible values for paint and it makes the drop box time searching for the right one kinda hard.
Here's what I want to do: Have a button beside the paint drop-down that says "Search", When the users clicks it it opens a search dialogue of some kind that will return the closest values to their search. When they click the value they want it gets entered as the selected value for the "Paint" drop-down box.
Here's my questions:
-Is this even possible ?I know enough about VBA to know that this is probably difficult in which case id still like to try.
-If it is possible. Are there perhaps any places you could point me to that would have some sort of starting point for me to work from. Remember I got the other part already I just need the search functionality.
I have sheet1 along with three columns as below
Result column will show the result after sum ** column A & B
[Code] ......
This is simple that i will add this formula =A2+B2 in Result column cell C2.
But i want to perform this addition action on Sheet2 and return the result in column C2 ** Sheet1.
What formula should be populate in Result column ** Sheet1 that could catch addition formula from Sheet2 dynamically and display the result ** column A & B in Sheet1 .
And what type ** formula should be use in Sheet2 to add two columns dynamically.
The same concept that we are using in any functions like as below:
Sub Function Plus (value1 As Double, value2 As Double)
Plus = (value1 + value)
End Sub
I have a column of numbers, for simplcity lets say they are 1,2,3,4,5. The numbers change automatically, so the column can look like 1,1,1,1,1 or 1,5,3,3,2 etc. I would like to know which command can monitor this column and return a true statement if two or more of those numbers match the number I specify.
View 3 Replies View RelatedOne of the useful functionality was the Excel-Addins , now I would be glad if someone could help me by posting the latest Addin or Analysis tool-pack. A setup file would be of great help as some of my formulae are suing such functions which ach are not provided by the reguale EXCEL such as "QUOTIENT" and "NETWORKDAYS"..
There's just one more request , I had a very good font by the name "POOR RICHARD" in one of my files, however after formatting and re-installning I do not have it anymore.
By typing either first letter or first two letters in cell A2 the list box should bring up matching street names. Also listbox should manually allow to select required street name by scrolling down.
View 3 Replies View RelatedI have a hyperlink in a cell that is put there by copying and pasting it from an e-mail message. The hyperlink has a nice friendly name that is not the actual URL. Let's say that the hyperlink is in Sheet 1 A1.
In Sheet 2 I have a cell that says C3 = HYPERLINK('Sheet1'!A1), however this doesn't import the URL with it. All it does is make a hyperlink with the nice friendly name that goes nowhere.
How can I reference Sheet 1 A1 and have the result in Sheet 2 C3 be a clickable URL that works exactly like the original that is sitting in Sheet 1 A1?
I have tried the following code :
Function hyp(r As Range) As String
hyp = ""
If r.Hyperlinks.Count > 0 Then
hyp = r.Hyperlinks(1).Address
Exit Function
End If
If r.HasFormula Then
rf = r.Formula
[Code]...
However it does not work for me. All it does is deliver NAME# as a hyperlink which cannot be clicked. I am very new to putting code into Excel so it is possible that I do not understand how to make a user-defined function work. I don't even understand how the code itself works.
I am looking for a solution by a macro or a formula to get the number of occurance of a particular word in all the worksheets of a xls.
View 4 Replies View RelatedI feel like I have lost some functionality due to merged cells. For a worksheet change event I have the following:
ElseIf Target.Value = "" Then ... blah blah blah
For a normal cell, every time a cell is deleted (in the appropriate target zone) then it runs the code I have in the subsequent lines
Except it doesn't work for merged cells, unless I manually go into the cell, backspace all of its contents and then press enter
Is it possible that if i press tab in excel my active cell offsets by a row and two negative columns.
View 1 Replies View RelatedI'm looking for the functionality in Excel where you can unhide/hide a group of columns or rows by clicking the plus or minus sign below the ribbon but above the spreadsheet itself. I forget how to do it, but it basically groups the columns together and hides or unhides all of them with one click.
View 3 Replies View RelatedI've attached an excel invoice template I've been making for our company. I'd like to protect all the cells except A12:H42, C8, B9, G4 and I44. The problem is, if I protect the worksheet (with these cells unlocked) then it won't let me run the macro (for inserting new rows for extra products).
View 5 Replies View RelatedI am presently designing a feature in excel through macros where I have created a box and named it box1 on sheet1 and when I click on the box1, then I want to go to sheet2 in the same workbook to specific row automatically.
View 7 Replies View RelatedI am currently using a bunch of msgboxes to tell the user if a specific error has occurred. I want to get rid of the ugly gray box and customize my own. I am having problems setting up the code as I would like it to function like msgbox does where I can supply a header and message string and then it displays in the userform. I tried coding it kind of how I would pass variables to a function and it doesn't seem to work.
Userform:
Code:
Private Sub userform_initialize(msg As String, hdr As String)
MessageBox.Caption = msg
myMessageBox.Caption = hdr
End Sub
Sub:
Code:
Call myMessageBox("Hi", "hi")
I have a spreadsheet with macros and conditional formatting assigned to it. What I want is to be able to copy only the displayed contents, without the macros and formatting instruction, to a separate workbook for reporting purposes.
View 2 Replies View Relatedhow to create a simple form that would search a sheet for a word and display the list of resulting rows - something similar to Adobe Reader 7 Search functionality ?
If I was having administrator privileges on this PC, I would simply install Cygnus and use its "grep" command on a CSV file but without that possibility, it's rather cumbersome to search an Excel sheet when you need to do it repetitively, and I need to locate a specific row each time without knowing the exact content of the cell (only searching for one or another word I know the cell contains).
I wish to have a column of values. It will be dates. There will be a value in each cell (hours worked by employee) I want to know only the total hours for the last 30 calendar days. I still need to see previous values, but I just want to total the last 30
View 9 Replies View Relatedi have two columns
column1: status
column2: total time of status
infinite long
now what i need to do is count the total time a specific status has.
f.ex.
column1: status1, status2, , status1
column2: 0 days 4 hours, 1 day 0.5 hours, 5 days 3.5 hours
how can i make a formula that looks up
if (in column1= status 1)
time= column2
the result i need is:
status1 = 5 days 7.5 hours
status2= 1 day 0.5 hours
i thought about a countif over column1 but i dont know how to add the second column
I am trying to open an Excel Workbook which in turn will automatically run a macro on loading which is a Userform. The Userform will contain 3 boxes, just some simple text boxes.
I don't want the Excel Workbook to show in the background, so I have opted to go for a VBScript to call the Excel Application and open the spreadsheet...
I have a current .vbs script which is as follows...
Code:
Option Explicit
On Error Resume Next
ExcelMacroExample
[Code]....
This works fine, there is no workbook showing and the userform opens as expected. The problem is, I lose the functionality to tab between the boxes. When opening the Userform directly in Excel,
I've always found these forums a valuable resource while searching for answers to coding questions. My question today deals with international distribution of Macros.
I've written a macro and its been distributed worldwide. It works just fine on the multiple instances where people have installed it here in the US. However, two of my colleagues in Mexico and Brazil are recieivng Type Mismatch Errors. Here is the code that is crashing:
'Making sure I'm on the right page
Sheets("Summary").Activate
'resetting variables to the top of the page
Let intsummrow = 7
Let intsummcol = 1
' Loop to find the last column
Let vartemp1 = Cells(intsummrow, intsummcol).Value
Do Until vartemp1 = ""
Let intsummcol = intsummcol + 1
Let vartemp1 = Cells(intsummrow, intsummcol).Value
Loop
The precise type mismatch is occuring at:
Do Until vartemp1 = ""
vartemp1 is a variant variable which at this point is storing the cell contents
intsummcol is an integer variable storing the column number
intsummrow is an integer variable storing the row number
And through my follow-up testing I've confirmed that in my US based copy cell A7 does have text in it.
What's the fastest way to display the sum of each column in one row. Here's a picture of what I want to do: http://www.filefreak.com/ppublic/33833/excel%20help.bmp now I want the sum of each column to be displayed in for example row 10. I have tried manually clicking "autosum" (sp?) but that will take a lot of time as I have about 400 more of these.
View 2 Replies View Related