Automating Weekly Formula Changes

Dec 11, 2009

The attached workbook is a follow up report that gets run every Friday. There are 1092 cells that contain formulas that reference a file that was created on Wednesday of the same week. eg.

View 5 Replies


Automating Fill Down Of Formula

Oct 20, 2006

I have a report which has several tabs of data input which is automated.

There are then several columns of formula which I manually fill down at the minute, is they an easy piece of code to automate this?

The attached example may help, on the tab data source I want to automate the copying down of the formulas in columns D&E.

Can this to be automated by the button on the tab front page?

View 3 Replies View Related

Automating Comparing Cell Values And Then Processing A Formula

Apr 10, 2007

I have the need within a spreadsheet to automatically compare the value of 2 cells and if they match exactly to move the cursor to a group of cells. If they do not match then move to another group of cells. The only way that I can see to do this is via Macros. However I still need to do the automatic compare first.

View 9 Replies View Related

Weekly Stock Without Listing All Products Weekly

Jun 19, 2007

In the attached workbook, the CASTINGS worksheet has a SOH ( stock on hand) column.
It looks up the SOH from the SOH worksheet (column 3) for each product on order for that week.

But how do I make week 2 use the SOH figure minus the previous weeks order, IF there was an order?

And so on until week 52.

The problem I have found is what if that product wasnt ordered for the last 4 weeks? Maybe I need a temp worksheet with all the products listed for all the weeks and a running total of SOH weather it is ordered or not?

View 5 Replies View Related

Formula For Inserting Weekly Dates?

Mar 7, 2014

I have a simple time sheet that I want to insert weekly dates by the date I have. I don't know the formula for that. I've attached the time sheet. TimeSheet2.xls

View 10 Replies View Related

Formula To Calculate Overtime On A Weekly Basis?

Apr 21, 2014

I am looking for a formula to calculate overtime on a weekly basis, entered daily, on a sheet with multiple weeks. It need it to calculate the overtime each week.

View 8 Replies View Related

Excel Formula - Get Result 8.21 In Weekly Total

May 3, 2012

I have a table and I would like to get result 8.21 in weekly total. It can get with calculatar like this.

Before Decimal:

After Decimal:

When we substract 121-100=21 and divide 100 with 50=2

Now, we add 6+2+0.21=8.21

result should be 8.21, how can we get this result.

Feed Consumption Weekly


View 4 Replies View Related

Index / Match / Sum Formula To Show Weekly Figures?

Jan 7, 2014


attached is a returns sheets that a qty is logged against part number every week. Some of the data I can use pivots on ( no problem ), but I have an issue on the returns tab where I think index/match / sum or something else will be better. At the moment because of the layout of data I have to drag each week number into the pivot value, do a greater than zero filter then summarize as a sum (yuk) I just cant get my head around index/match /sum. It would be great just to be able to enter a week number, and all the warehouse, part numbers and qty came through. There is a short explanation on the returns tab of what im looking for.

so in essence I want week numbers across the top, but be able to enter a year on the returns tab that looks at the week number. I don't know enough about the formula to change.

View 14 Replies View Related

Formula: Automatically Update The Date On A Weekly Basis

May 5, 2006

Need the formula to enable a cell to automatically update the date on a weekly basis

View 2 Replies View Related

Formula For Weekly Total But Not Showing Until Present Month Is Entered?

Jun 7, 2014

Every week, in row 3 i enter the number of boards cut. In row 9 i want a running total, but until this weeks total is entered, i dont want anything to show. This is the data for a bar chart which i print off every week. Next week is week 3 and i will enter the data in cell d3. When i do i want cell d9 to show the new total of B3,C3,D3. And so on each week.

View 6 Replies View Related

Formula To Convert Monthly Data To Weekly Data

May 8, 2014

I have 12 observations per variable over 2013. At the end of each month I gathered the average value of X of said month. What I want to do is convert these observations to a list of weekly observations, where each week in January gets the value of the January observation


Value 2345678910111213

And get it like:

Week Value
1 2
2 2
3 2
4 2
5 2
6 3
7 3

and so on.

Since I've got a lot of variables I'd like to use some kind of formula.

View 11 Replies View Related

Automating Brackets

Mar 18, 2004

I found a template at it's just a bracket. I feel it has some underlying functionality, as they have a template in visio as well, but I can't figure it out.

So, what I'd like to do is have a workbook with say 100 worksheets. Each worksheet will have a person's bracket. Each person will write in all their picks. Their will be a master bracket where the actual winners will go, as the results are posted. When the master bracket is updated, Excel will flip through each worksheet, checking to see if the win in the master bracket matches the individual bracket. Points will be awarded or not.

Can that be done? I've also done a google search and can't get anymore help. I know that the web has multitudes of free versions, which I'd use, the only problem is that people would hesitate to register. doing it in excel, on the office system, there's no need to register, your cpu login takes care of authentication.

View 9 Replies View Related

Automating Graphs

Jun 30, 2006

I'm trying to make alot of graphs(400) and I'm not really sure where to start but here with what I've got so far.

Sub macro2()
Dim x As Long, t As Long, y As Long, z As Long
y = 3
z = 5
x = 4
While x < 1000
With Worksheets("cabernet (2)")
t = Cells(x, 1).Value
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("cabernet (2)").Range("By:Hz"), PlotBy:=xlRows
ActiveChart.Location where:=xlLocationAsObject, Name:="Cabernet (2)"
ActiveWindow.Visible = False
ActiveChart.HasTitle = True
ActiveChart.charttitle.Text = " " & t
Windows("complete Favorite Genes.xls").Activate
y = y + 3
z = z + 3
x = x + 4
End With
End Sub

I don't really know if putting a variable directly into a range works, so far it doesn't.

As you can tell I need a line graph of the range B3:H5, then the next is the those same columns but 3 rows down, so the next graph is at B6:H8. I'd also like to through in a title here, but I may just be playing with fire. The title is located in column A starting at 4 and corrosponds with each graph, so it too goes is increments of 3, i.e., the first title is at 4 the second at 7 the third at 10. Somebody help me out this isn't working very well at all.

View 5 Replies View Related

Automating To Export

Sep 16, 2006

Private Sub Database_Click()
Application. ScreenUpdating = False
masterfile = "S:OfficeMaster File.xls"
Answer = MsgBox("Do You want to export to Final Database?", Buttons:=vbYesNoCancel)
If Answer = vbYes Then
ThisWorkbook.Worksheets(2). Range("q9").Copy
End If
End Sub

for some reason, it doesnt work as it said the masterfile.worksheets is not recognized, but as i go to the immediate thingee, i type in ?masterfile, it give me the exact address, i had been working on this thign for hours, and have no idea what i did wrong.

View 3 Replies View Related

Automating Xirr

May 7, 2007

I have a very peculiar problem. Each month I have a set of financial products for which I calculate the IRR values using XIRR, as the cash flows are uneven. Imagine this, Product names in ColumnA, the cashflow dates in B, and the cash flows with transaction types in ColumnC and finall D has amounts in negetive and positive. Each month I add new cashflows for each product as rows.

Manually I scroll down to select the range for XIRR (both date and amount)
Offlate this work has gone really high in terms of values, and manually cacluating XIRR takes hell lot of time for all the products (currently 80 odd)

View 9 Replies View Related

Automating Data Processing

Jul 25, 2008

I have a large number of sheets with data in the following general format:

x ..... y ............ z etc

A Unique value Unique value
A Unique value Unique value
B Unique value Unique value
B Unique value Unique value
C Unique value Unique value
C Unique value Unique value
D Unique value Unique value
D Unique value Unique value
E Unique value Unique value
E Unique value Unique value

In seperate sheets, I want the following:

x y1 y2

x z1 z2

And so on for a number of columns - I always want column "x" with another column, however I want the two values for the same value of x to be next to each other, not above one another.

View 11 Replies View Related

Automating The Removal Of Certain Data

May 6, 2009

Just wondering if any one can help me come up with some code to remove some lap time in my document ....

View 14 Replies View Related

Automating File Name Via Inputbox

Jun 11, 2007

I am trying to do is have the code display an input box asking the user to type in the file name, or generate this automatically? to an existing folder in a drive

My master file, imports data from another source (excel file) pastes it in the necessary columns at the moment, however the file is run every Monday so the file name is generic, last week was 10723, this week will become 10724 when the data is run next week,

Can I do what I want to? Via either an inputbox for the file name, or by using something so the file name becomes 107 & Weeknum(today()-3)

View 9 Replies View Related

Automating % Weight Calculation

Mar 16, 2008

I have 1 spreadsheet in which I need to calculate the % weight of some numbers. Columns A to G have data I do not need for the calculation.

Column H has the numbers of whose % weight I need to calculate. The problem is that the no. of rows containing the numbers can change from week to week and I am trying to automate the calculation of the % weights.

So I want try to use column G (as a reference) (like go at the bottom of it so I can know how many rows of numbers there are) and then calculate the % weights.

View 9 Replies View Related

Automating With Internet Explorer

Apr 30, 2008

I am trying to use Excel VBA to conduct a transaction automatically.So far,I managed to get all the required input in.Now,I need to click the "Submit" Button.

I was thinking of using the InternetExlorer.Document.Links(x).Title to search for the link of the "Submit" button and then use the .Navigate method to proceed to the link.

But when I wrote and ran the :


i get the following mesage:

"Run Time Error 98

A property or method cannot include a reference to a private object ,either as an argument or as a return value "

View 9 Replies View Related

Automating Copy And Paste ...

May 16, 2008

I have a spreadsheet which i work on everyday, basically I have a formula which needs to be copied in the second column depending on the range in the first column.

Ex : one worksheet will have A1:A100, the B column should be copied to B1:B100, if the next one has A1:A200 then B column should be copied to B1:B200, how to do this with VBA?

View 9 Replies View Related

Automating And Looping A Web Query

Mar 4, 2009

My spreadsheet is linked to a stock data web page using the little documented .iqy function. I have set cell A1 to the web page ticker symbol, so that whenever this cell value is changed the data in the spreadsheet is automatically updated.

My problem is in several parts:
1. Read TickerSymbol.csv file which is comprised of just ticker symbols,
2. Insert (TickerSymbol.csv) symbol 1 into cell A1,
3. Save the results to a file c:XXXSymbol1.csv,
4. Insert (TickerSymbol.csv) symbol 2,
5. repeat, repeat

View 9 Replies View Related

Automating Adding Worksheets

Jan 23, 2007

I have created a workbook consisting of a 'master sheet' (this sheet is a worksheet in itself but also summarizes the totals from the subsequent worksheet) and subsequent work sheets. I wish to be able to add worksheets as needed while having the 'master sheet' still updating as you add sheets.

View 9 Replies View Related

Automating Saving Tabs Used For Billing?

Sep 15, 2012

I've been experimenting with automating saving tabs that we use for billing into a .prn file. It works great on my workstation but when I run it on a coworkers workstation the file gets output to the Windows 7 Documents Library, not the workbook's file path. I want it to Save in the same file path that the workbook is saved in already.

Just to clarify I have many sheet tabs, and these are all saved in the .xlsx file. I was previously clicking one by one and doing save as, other file type, prn... but I've automated that by recording a macro and hacking away at it. specifying the workbooks file path correctly? Here's my code, truncated to just show the name of 2 of the tabs:

' This Macro saves the sheet then exports all tabs as a PRN.


View 2 Replies View Related

Automating Count Of Various Text Values

Mar 11, 2014

I have 300 cells filled with around a dozen different names - i.e. the same names crop up multiple times. What I need is automatically creating a list of all the names that appear, ordered by how many times they appear and with the number of times they appear next to them - i.e. ranking the names by their frequency.

View 3 Replies View Related

Automating Outlook Calendar Invitations

Apr 7, 2014

How to automate outlook 2010 calendar and email integration.

I would like to create a button that sends out calendar invites to a large number of recipients with different specific meeting details (date determined by spreadsheet and other details standard). I'd expect that this would populate a specific shared calendar (not my personal calendar) for managing these appointments. It would be good to avoid duplicate correspondence (e.g. via drop-down list or checkbox).

I've created buttons testing a few codes provided in forums, but have not been able to make these work correctly.

Attached is a simple excerpt of the spreadsheet I'm seeking to automate.

Test example.xlsm‎

View 1 Replies View Related

Data Validation - Automating Process

Feb 12, 2014

I'm currently trying to automate the process of creating Data Validation.


I have Grades and Names List.

" GradesList " Range has values "One, Two, Three, Four "

I'm trying to map Names List to GradesList using INDIRECT().

Debug the function DataValidation() to understand the issue better.

View 7 Replies View Related

Automating Repeated Copy/paste

Nov 23, 2009

I've data in a sheet (case1) which I would like to paste in a new sheet (case2) in the shown format. Is there any easy way of automating this? I tried recording a macro & tried somehow to automate this task but gave up after a few tries?

View 8 Replies View Related

Automating Multiple Cells Using A Stat

Sep 24, 2008

I have a row with 5 numbers, B1:F1.
A1 is my muliplier cell (percentage).

I want all of the above to be variables (no formula in these cells) and controlled by a different cell (G1).

What function would I insert into G1 to be able to control the figures in B1:F1 via A1.

View 9 Replies View Related

Automating Work Task Using Validation?

May 25, 2012

i have a validation list in A5 pointing to F6:F, i need the refmask_no to change according to the validation list in A5:A.

So if i choose from List LEADCASH the refmask_no= '100007995' etc

How can i automate the changing of refmask_no?

Sheet1  ABCDEF4Validation ListCorr _acc_no  Mapping 5LEAD CASH ABNAMROrefmask_no = '100007997'  
Corr_acc_noLead Set6    '100007995'LEAD CASH7    '100020741'LEAD CASH ABNAMRO8    '100020645'LEAD CASH AUDNZNR9 

View 1 Replies View Related

Copyrights 2005-15, All rights reserved