I have a column A with date formatted as dd/mm/yyyy. I then have a column B which is =A, and I reformat the date formats to a custom format DDDD which gives me the day of the week that the date relates to.
I then try and write an if statement
B1 always references the date which is stored as 40118, when I need it to reference the actual formatted value that I see in the cell e.g. Friday?
I have a workbook with a Summary sheet and then a series of sheets for each month/year. Onthe summary sheet I use column A for the month/year as a date formatted "mmm yy" ie the same as the sheet names.
in columns B,C,D I want formulae that sums or averages a fixed range on each sheet but I would like the formula to take the sheet name from Columa A rather than hard coding it.
So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the "Dec 04" from the text in cell A3
I have problem with a spreadsheet that I am trying to create.
I have a large sheet of data which is dumped in from another program. This contains our deliveries and orders etc.
Now for planning purposes, I would like to see how much of each item I have on order.
I can use VLOOKUP, but that will only give me the amount for the first order it encounters. But not the 3rd, 4th etc. I could use SUMIF but I need the dates as well. After doing some searching I think I have found a way of doing this: I can get the first easier enough:
=VLOOKUP(D$11,'purchase order'!$A$1:$K$6000,5,FALSE), this gives the first order than the another =VLOOKUP(D11,'purchase order'!$A$1:$K$6000,11,FALSE) for its date.
For the second column to check any other orders I thought I could find the cell referance for the first SEARCH: which is
A108: ="A"&MATCH(D11,'purchase order'!A:A,0).
Is there any way of using this Reference to start a new VLOOKUP. So the Lookup Range starts at this reference?? To make things harder it is on another sheet.
I have the following formula in cell L51 of all sheets calculating the volume depending on the monthly index that is chosen from the drop down menu in a particular sheet. =If(MIndex=0, SUM(D33:L50),If(MIndex=1,SUM(D34:L50),If(MIndex=2,SUM(D35:L50), 0))). I am getting the following message and I do not understand what it is about.
Microsoft Office Excel cannot calculate a formula. Cell references in the formula refer to the formula's result, creating a circular reference. Try one of the following
I am trying to do a "getpivotdata" formula on a cell in certain file that gets info from a pivot table on another file. However the name of the second file (the one where the pivot table is found on) might be different each time so I created a variable for the second file name but I canít figure out a way to use this variable in the get pivot data formula as I am not very experience with VBA.
Template = Application.InputBox("What is the name of the file ending with (.xls)") If Template = False Then cont1 = MsgBox("Please write the name correctly using (.xls)!", vbOKOnly) If cont1 = vbOK Then Exit Sub End If End If
I have a table of consolidated data that consists of the date at which certain payments occur and their amounts that other sheets need to reference to perform calculations. I am currently using the formula below to put the data under the correct date column. =INDEX(ImportedData!$B$10:$DB$10,MATCH(B3,ImportedData!$B$4:$DB$4,FALSE)). The problem that I have is that the INDEX range, currently B10:DB10, will need to change depending on the number of files that are consolidated ie with more files the row will increase.
How do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.
________A________________B_____________C_________D 1 Task___________Trigger_____________Days_____Due Date 2 Design begins__Proj OK______________10____10-Jan 3 Specs written__Design begins (A2)____5____15-Jan (D2+C3) 4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)
If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?
Attached I have a document where I am wanting all of the individual sheet names on the Total page. Rather than having to change each formula to match the sheet name I believe there is a way to reference the sheet name column (AO) in the formula so you can drag it down to fill in the columns. I am basic with Excel and do not know how to add this indirect function into my current formulas.
I have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.
12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.
I can assign a number of copies to print by adjusting ActiveWindow.SelectedSheets.PrintOut Copies:= , but what I want it to have the number of copies auto adjust to the value of a cell that contains a formula. I was hoping it would something as simple as Copies:=cell but no luck...
I have a column of numbers, on which i would like to perform subtraction, in a way that the cell in the (i)th row will be subtracted from the one in the (i+x)th row, while x is a parameter that the user can change as he wishes.
My problem is how to refer to the cell address and still use this conditional reference... If anyone has an idea how to realize this calculation (which should be peanuts in Matlab, but apparently more problematic in Excel),
I am trying to acccess a cell value from a seperate worksheet. The cell reference needs to be generated by a seperate formula. In this example
=DSInfo!C28 // provides the correct result i.e the contents of cell C28 on worsheet DSInfo =MATCH(A4,DSInfo!C1:C35,0) //provides the correct row number - in this case 28
However on trying to combine the two =DSInfo!C&MATCH(A4,DSInfo!C1:C35,0) // provides only a formula error. I'm guessing this is a simple syntax error on my part but after hours of trying various ways I'm still having no luck.
I'm basically looking for something almost like an inverse function to INDIRECT. This function would first look at a cell's formula as a text string, parse out the first valid cell reference in A1 format, and return that cell as a text string.
Detail: I have a spreadsheet with cells that point to other values. I would like to get only the row number from the first cell reference in the formula residing in a given cell. For example:
Suppose A1 has the formula =AL267. and A2 has the formula =SUM(AL94:AL235)
I would like a formula in B1 that returns the text string, "AL267" so that I would know this is the first reference.
Ideally it could be dragged down to B2 such that it returns the text string "AL94" (and not "AL235") because AL94 is the first cell reference in A2's
Currently I am copying the formulas after hitting ctl+` and pasting that text into a text editor, followed by text operations to manipulate the results into the desired values. Any solution that didn't involve going out to notepad.
I am having trouble using the MATCH function in my code. I am looking for a match in one worksheet from a cell in another. I have defined these cells and ranges as variables. However, it doesn't seem to be working with my application. I would like to find the row of the matching cell and use the output of the MATCH function to determine the next step in my code with an IF function. Here is my code. (NOTE: this is my edited code, I removed irrelevant pieces. The major problem is the MATCH function.)
What I would like to do is output the first 4 characters of a cell into another cell. eg Cell A1 has "1234 Sydney". If I just use the manual formula "=left(A1,4)" it would give me the correct output "1234" in say cell B2, but as a formula. As I need to automate this in a macro, how could you code this in VBA to output the String "1234" into cell B2(ie the actual output rather than a formula)?
I am having some trouble with the "mid()" function. I have it saved as a variable which pulls 6 digits from another variable. I was trying to paste from the mid variable into my excel worksheet but it keeps on telling me that there is an object required error.
The goal is to input the "mid()" output in my code into a cell in the spreadsheet.