I'm trying to copy a file from one directory to another directory. However, the source directory, I'd like to have it so it's created from values within specific cells on a Sheet named "PULL".
When trying the following, I get an error permission denied.
It is suppose to be that if the employee is "FT" and has worked >=4 years the return is 15. But if the employee is FT and has worked 2 years but less than 4 years then it is suppose to return 10 (these are days off) Or if the employee is FT and has worked 1 year, but less than 2 then it should return 5 days off. And all the others in the column get no days off.
I have tried to do it with structured references and with cell references I get a column of zeros!
is it possible to configure Excel in order to save the Hyperlink path for a file with absolute path and non relative?
I notice that the hyperlink is ....pdf ry.pdf
if I change the position of the file excel there is a problem!
I would like to save es. d:invoichepdf ry.pdf
I am using an XY scatter chart that displays the planned path and an actual path as I go. The planned path is set up in 100' increments and goes 12000' and actual path varies along the way.
My data that I use is the Northing (ft), Easting (ft) and Vertical Section (ft) for both the planned path and actual path.
I was wondering if there was an east way to get that information from the chart or if there is a formula that would allow me to calculate distance between the two points by using the data I use for my chart.
I want to run a macro to separate File Name and Path from the given complete path
For Example
Code:
In Column A : Given Complete Path " C:MainFolderRecordsSubFilesFile1Record.pdf
I need it separeted like
Code:
In Column B :File Name = Record.pdf
In Column C :File Path = C:MainFolderRecordsSubFilesFile1
Is there any way to do this through a macro
I have a number of statements within the Sheet Event Code (Excel 2007). Three times lately I have added a column and had to go back into the code and find all of the references that needed changing to reflect the new column.
I have been working on this for a couple of days and even tried EE, but to no success.
I have read that Defined Names / Constants should be used as often as possible, but even trying that, the VBA code errors out or "hangs up". Even within Bill Jalen's book (VBA and Macros 2007), there is nothing that addresses this, especially using Intersect.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
On Error GoTo mEnd
Set rng = Sheets("Log").[F14:F10000]
If Not Intersect(rng, Target) Is Nothing Then
If Target = "" Then
With Sheets("Log")
I set up formulas to count text characters in a range of cells. I'm tracking attendance and payments for a small yoga studio.
All I need to do is count "Y"s for prepaid attendance and "DI"s for drop-ins. I have the formulas working but they are absolute so inserting a row will break my sheet.
=COUNTIF(E14:Z14,"*Y*")
=COUNTIF(E11:Z11,"*DI*")
I am using the dsum formula to sum some values...the formula in B2 is:
=DSUM(BaseSistemasFebrero,"vlfinf",OFFSET('Planes Entidades'!B$1,0,0,COUNTA('Planes Entidades'!B$1:B$49),1))
The Planes Entidades sheet the data is layed out like this: ....
How do I setup MyPath to a cell reference? In this case I need to set MyPath to the cell value of I1
Code that I have that doesn't work...
Sub SaveToFolder()
'Save File to Path
MyPath = I1
ActiveWorkbook.SaveAs Filename:=MyPath & ActiveWorkbook.Name
Windows("PE Console.xls").Activate
End Sub
I am creating a training records matrix which will be used by 30 or so locations. each one may want to save in a different location. I would like to save the chosen location as an entry in a cell so this can be accessed by other macros.
View 9 Replies View RelatedI'm trying to do a formula that references a cell and returns a different result dependant on the number in the cell being referenced.
For example I've said if A1 has a 3 in it then put the word TEST as the result, plus if it has a 4 put the word RESULT.
What I wrote as my formula is as follows-
=IF(A1=3,"TEST")+IF(A1=4,"RESULT")
It works fine when I only use one result but goes wrong when I add two. If I change the words I want to show to numbers it comes up fine but with words it just returns a Value error.
I have 2 inventory reports: what my store has and what my supplier has. I need to copy Tab1:K# to Tab2:T# provided that Tab1:A#'s contents match Tab2:A#'s contents. The A column represents the SKU of the item, but there is a difference in the amount of SKU's in each (my store sells ~6,000 items, supplier has ~10,000 items), so it's not as simple as sort by column A and copy pasting column K to column T.
For instance:
On Tab 1, A2's value is [1], K2's value is [9.38].
On Tab 2, A70's value is [1], K70's value is blank, but I need it to be [9.38], to match Tab 1's respective SKU.
I almost thought I had it figured out with VLOOKUP, but I cant seem to get it right... It doesn't reference the correct number.
Screenshots for reference
First tab, from the wholesaler: [URL]
Second tab, store's stock: [URL]
Assume I have a cell M24 with a formula like
=M10 + $H24 - $I24*0.35
As you can see B10 is a fix reference (due to omitted $) which should NOT be auto-adjusted but be kept.
Now I want to copy the formular to lots of cells below cell M24. therefore I mark cell M24 and click copy in context menu.
Then I drag/expand the blinking cell border to lets say the 20 cells below. As I result I expect e.g. in cell M25 a formula like
=M10 + $H25 - $I25*0.35
Unfortunately I got
=M11 + $H25 - $I25*0.35
So the fix reference is adjusted as well.
How can I tell Excel 2007 to NOT auto-adjust fix references in formulas?
I'm using Excel 2003 and I'm trying to make my LINEST function and the source cells for a scatter graph use the cell range specified in another cell in the document. I figured out that it was simple to do the following:
I250 contains: 10, shows: 10
I251 contains: ="I"&I250, shows I10
But I can't figure out how to do this in a larger function. I've tried a few things but none of them work. Here is an example where I want it to do a LINEST with y values in cells Ja - Jb and x values in cells Ia - Ib (where a and b are integers specified in cells I250 and J250 respectively):
Below is my code which isn't formatting the cells it's suppossed to. It looks like it isn't doing anything. I think the issue might be with the highlighted section of my code, but when I go to "Manage" my rules for conditional formatting, excel references the appropriate cell under the "Applies to" section. I am using relative cell references for for the majority of the rest of the code and this section follows a section that selects the correct cell for this conditional format.
Code:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=""ABS()>.005"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority[code].....
I have a worksheet (sheet1 in book1.xlsx) that references another worksheet (sheet2 in book2.xlsx) in a different file. The latter file, however, is stored in a directory that changes. Call it MYPATH for the time being. I'm trying to use a formula (not macros, which aren't allowed in this office) to reference the directory path using the dynamic name.
For example, in the first worksheet, cell A15 contains the word MYPATH. I'd like cell A1 to have a formula like this: ='C:&$A$15&subdir[book2.xlsx]sheet2'!B50
so that if I change the value of cell A15 in book.xlsx to ANEWPATH, the path reference will switch to the book2.xlsx in ANEWPATH, not MYPATH.
I have VBA code with array formula that looks up some values from series of spreadsheets. Which spreadsheet to use depends on the values in another column.
How do I make the formula the same for the whole range as it will substitute the necessary path into itself?
here is an example:
here is a code for VBA:
Range("I2").Select
Selection.FormulaArray = _
"=INDEX('1_Repl'!$C$2:$C$8,MATCH(1,IF('1_Repl'!$A$2:$A$8=H4,IF('1_Repl'!$C$2:$C$80,1)),0))"
Selection.AutoFill Destination:=Range("I2:I535"), Type:=xlFillDefault
******** ******************** ************************************************************************>Microsoft Excel - TEST_LOOKUP.xls___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutI4I5I6I7I8I9=ABCDEFGHIJK11st Spreadsheet 2nd Spreadsheet 2 3Number123 CodeNumberData2 41230.200.2 11230.22Code=1, so 1_Repl.xls 52340.10.10 12340.1 6345000 2345#N/Ahas to refer to 2_Repl.xls 712300.220 3456#N/A 8234000.15 3567#N/Apath has to be dynamic 91230.240.240.24 3678#N/AValue of G2+ "_Repl.xls" 103450.1300 11 1_Repl [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I have a few files all with the same name for a few different members of staff, so I save them in different paths to keep them separate.
eg
c:documentsdatajo blogsexcelfile.xls
c:documentsdatafred smithexcelfile.xls
c:documentsdatajane jonesexcelfile.xls
cells is the sheet contain the persons name
jo blogs
fred smith
jane jones
I want to be able to use this for others in the organisation and make it a simple as possible
Is there a way to use the cell contents in the path name (without opening the sheet first)
I know I can point to each cell in the other file and excel will use it but there must be a way of making this happen more automated by using the cell contents
The folder which contains the Excel file I'm working on is
C:Usersar3DocumentsKey Performance Indicators
however if I place =INFO("directory") in a cell I get
C:Usersar3Documents
Is there a function where I can get the full path of the workbook in the cell?
How to link an image to a cells value? The linked image is a range of cells and that range is A5:K30
I will take a macro if that is the only way.
linked images path = cell B2
B2 = ("P:path"&TEXT(TODAY(),"yyyy")&"_stuff"&TEXT(TODAY(),"mmm")&IF(OR(TEXT(TODAY(),"mmm")="Nov",
TEXT(TODAY(),"mmm")="Dec"),TEXT(TODAY(),"y")-1,TEXT(TODAY(),"y"))&""&TEXT(TODAY()-1,"ddmmmyy")&" stuff.xlsx")
I am trying to use the same VBA code for different applications using variables.
Basically I want to have a variables sheet that has the different directories based on the macro selection. My variable sourceloc would change based on the variable name on the sheet.
sometimes I would want it to equal what was in a1 and sometime I would want what was in a2
a1 cell contains "f:filezilla files
a2 cell contains "f:dmc files
This is the code I started and it gives compile error when ChDir = sourceloc is reached
Public sourceloc
Sub filezillasettings()
Dim sourceloc As String
sourceloc = ActiveSheet.[a1] ' a1 cell contains the directory name and folder name =
End Sub
I want to do a search for the amount of people of a certain age in a column, but I want to be able to vary the amount of cells I look in. So first I might want to look for people aged 15 in A3:A35 and then in A3:A55 to see if there is a difference.
Now the optimal way to do this, in my opinion, would be to have a reference that looks like A3:A(B1) and then have the number of the last cell I want to look in in B1, in this case either 35 or 55.
I'm using Excel 2000 and trying to use the datedif function. I've formated 2
columns as date m/dd/yyyy and left the formula column general I'm entering
dates
A1: 1/1/2002
B1: 1/1/2005
I'm entering the formula in C1
=datedif(b1,a1,"M")
I'm looking for the nmber of months between 2 dates
I get #NUM! for a result.
My macro uses the solver 100 times. Everything is working well, but it's a little bit slow because of all the cell references.
I was wondering if it's possible to use the solver with variables without cell references?
I'm looking to do something similar to a Paste Special -> Transpose, but rather than pasting values or formulas, I want to paste cell references to the cells that I just transposed.
E.G.
Sheet 1:
A1 = 1
A2 = 3
B1 = 2
B2 = 4
Sheet2:
A1 = Sheet1!A1
A2 = Sheet1!B1
B1 = Sheet1!A2
B2 = Sheet1!B2
This would typically be an easy exercise, but I have a set 205 rows long and 12 columns wide. A little long to do it one by one.
If I have values in cell B5:B8
B5=0
B6=3
B7=7
B8=10
I want to have a standing formula in Cells J2:M2 that will always reflect whats in those cells. I don't want to use transpose nor do I wnat something as simple as J2=+B5,K2=+B6,etc.
I would like to know if there is a formula that would add the values in a certain row to a cell
For instance:
I want J2 to reflect what is in B5 AND I want K2 To reflect what is in B5 plus a row which would be the value in B6. I have a reason for doing this as I am going to import 120
ranges beginning at a1 to I30 on each sheet while having the sheet I am importing to have these formulas starting at J2.
I tried using +cellnumber+row, but it just gave me the row number and not the actual value in that cell.
Here is what I have at present:
At the beginning of a loop, my cursor is positioned on a row. I want to test two cells on that row.
Depending upon the result of the two cells, I want to make the row a certain color.
After that, I then want to move down to the next row, test the same two cells in THAT row, color accordingly and loop again until I reach the bottom of the spreadsheet.
My problem is this:
If I can use a loop that lists various conditions, along with the corresponding statements that color the row accordingly, how can I when finished increment the cell references so that the conditions change to refer to the cells the next row down?
If a loop is not possible, any suggestions you have for solving this problem would be appreciated. I have about 200 rows so am trying to condense my code; this may be hampering my ability to figure out the solution.
I am trying to code the following in VBA. Excel Cells have formulae like: '=+BZ165-BZ163-BZ162-BZ160-BZ159-BZ157-BZ153'. Now I want to write code in VBA which will be able to give me the individual cells referenced in this formula: BZ165, BZ163 etc. These names can be stored in individual variables or arrays.
I will then use these for further processing. Note in cases where formula refers to data in another sheet like: '=Projects!P49' or data in another excel like '='C:Documents and SettingshoskopDesktop[Annual.xls]Quarterly'!BA$502': I would like to store the path , file name and sheetname in variables/arrays as well.
Any pointers as to how I should approach this problem?
Is there a way to use the open dialog box (like as if you where opening another workbook) and then with the selected item inster the path and file name into a cell.
View 2 Replies View RelatedI have a master workbook that is referencing data from several other workbooks whose file names are based on the years data was taken. In the master work book on any given sheet, I would like to be able change the data being used in the formula calculations by simply changing the year value in a single cell. By changing the date value, the formulas are directed from one data workbook to another.
e.g. in the master workbook in Sheet1, if you enter 2008 into cell A1, all of the formulas in Sheet1 now pull values from 2008.xls; if 2008 was replaced with 2009 in cell A1 on Sheet1 of the master workbook, then all of the formulas on Sheet1 would now pull values from 2009.xls.As I am completely new to VBA (bought my first book last night), I am looking for any help that is out there. I am not entirely new to programming (used C++ to write numerical simulations) but I have zero experience writing macros and the like.