In trying to make an alphabetized record of all inventory for work, I ran into a snafu when creating shortcuts for other users to the different letters.
I hyperlinked something like a table of contents together and hyperlinked them to the cells, so clicking on B in the table of contents takes you to cell B64. I want to future-proof it with absolute references but haven't been able to.
I tried right clicking and typing in the cell name--but when I add more cells, instead of moving to B72 like it should, the hyperlink stays in B64, which is now part of the As.
I also tried: =HYPERLINK(A$64,"B") as well as =HYPERLINK(B_Start,"B") by naming the cell. The name went to B72, but the link stayed in B64
I'm creating a chart that will show a running average of a set of values. First column has the values and the second is a running average. Here's my sample data:
My formula for the second column is as follows, =AVERAGE($G$1:G1), =AVERAGE($G$1:G2)... and so on.'
My problem is when I try to run the same formula on a different worksheet to put on the current worksheet, I get an error from Excel that the formula is invalid. Here's the formula I use, =AVERAGE('Team Stats'!$A$I1:AI1), =AVERAGE('Team Stats'!$A$I1:AI2), ...
Is this a bug with Excel? Is the format wrong? I've tried just using the function wizard and it tells me that the absolute reference is invalid.
Have a query that pulls general ledger data. Named Range SALES defined for Sales Account numbers. What is the best way to always have SALES (which pulls as negative number) converted to absolute values?
I have in my mind that it would be some sort of worksheet change event . . . but I don't really grasp the byval/byref TARGET concept by itself, let alone whether it can work on a named range.
I have a simple count formula which is suppose to be absolute. =COUNT($L5:$V5) and works till I add a column via a macro. Macro adds a new column at column M then inserts data. My problem is when I check the formula after macro it changes to =COUNT($M5:$W5) I thought using the $ sign means Don't change?
This code will take the value of the active cell (which is a filename) and open the corresponding PDF document of the same name.
Sub OpenPDF()
Dim fName As String Dim fExt As String Dim fPath As String Dim fFullPath As String fName = ActiveCell.Value fPath = "M:Books" fExt = ".pdf" fFullPath = fPath & fName & fExt ActiveWorkbook.FollowHyperlink Address:=fFullPath, NewWindow:=True............
I have a few thousand files that I'm trying to hyperlink into excel. The files are named with a date at the end of it. I want to be able to hyperlink to that file just by looking up the name without the date portion. So basically I'm asking if I could hyperlink a file with only a part of the name, and yes the names are distinct even without the date. I now am loading all of the file names into a spread sheet via a VBA code, however with almost 10k files that process takes longer than I'd like.
In my restaurant we have a master computer upstairs that collects all of the register sales info. Then we have a computer downstairs that my employees sign in and out on.
This excel timesheet keeps track of the amount of labor hours being used per day. Is there a way of doing this without putting the timesheet on a web page or putting my sales info on a web page?
I'm having trouble creating a hyperlink that will automatically take a value from a cell and link me directly to a file of the same name across the company server.
For example in column B I have a number along the lines of
43FG2345678
In a folder on the server I have a word document that goes by that exact file. I need to create a hyperlink that looks up that value and searches for that specific document in the location file, and then directly open that file.
The value in column B will change with each line so as I copy the formula/hyperlink down I want it to automatically go in and replace the text and then lookup the file.
I have tried the concatenate function within the hyperlink but not had much luck.
I have a problem in that in using the paste special on some links I created using HYPERLINK/VLOOKUP formulas the hyperlinks themselves have gone (ie nothing happens if you click on the data).
What I want is a macro that can turn a cell's contents into a hyperlink (i.e the location and friendly name are the same) by means of a simple keyboard stroke. I tried doing this using the macro recorder, but it didn't recognize when I was trying to paste the text into the Insert Hyperlink address bar and it just recalled the name of the address in the VBE window as if I'd written it in manually.
I have just started to learn how to use vba in microsoft excel. Over the past few days i have been creating a drawing register and have incorporated multiple routines based on searching this forum. Up until this point i have foud everything i needed on this forum, but now i cannot work out how to creat a routine that does the below.
The setup *I have a folder called Zircon Plant *That folder contains 3 folders 01. Superseded, 02. PDFs and 03. Documentation (the 01. Sup...etc. is the actual folder name, the 01, 02 and 03 isnt distinguishing the 3 folders) *The Register is located in 03. Documentation *The PDFs i want to hyperlink to are located in 02. PDFs
What i want to do *i have 2000 rows of drawing names *the drawing name in excel is spread over 5 side by side cells on each row (rows 21 - 2020) so for example the first row the information is in cells (H21, I21, J21, K21 ,L21) *the cells i want to contain hyperlinks are V21 - V2020
so what i want is when excel opens up (i already have another script running at startup so i will be placing this after the other startup routine i have) i want it to search in folder 02. PDFs using the contents in (for example the first row (21) and colums HIJKL contain (H21)4CP(I21)-(J21)D(K21)-(L21)55000) each row and combine those cell contents to find the file name 4CP-D-55000.pdf. I then want it to go down every row until row 2020 untill it has added all pdfs.
(The second row is (H22)4CP(I221)-(J221)D(K221)-(L21)55001.....file name would be 4CP-D-55001.....third row would be 4CP-D-55002 etc...)
If such a pdf with that name doesnt exist, i want the cell (for example v21) to read "PDF not available"
based on a search here i have been able to do this for 1 cell, but dont want to do 2000 seperate routines. the current routine i have for it to work on one cell is
' Select PDF range ActiveSheet.Range("u21").Select ' Promt PDF Hyperlinking ActiveCell.Hyperlinks.Add ActiveCell, "I:DraftingAs Built4CP - PinkenbaE - ElectricallZircon Plant02. PDFs4CP-D-55000.pdf"
I also have multiple sheets. the first sheet is D - Documentation. I will eventually copy this routine 4 more times as i will be able to work out how to apply it to the other sheets
how to get either the absolute row or the absolute column of a cell.
Suppose the active cell is H27. If we use:
ActiveCell.Address we will get $H$27
How can we get either $H or $27 only assigned to a variable let it be MyVar.
Please keep in mind that converting the $H$27 to a string and then making string manipulations is not accepted, unless of course it's the only way to go.
Also it would be nice if I get answers on how to get the relative address column/row portions only, too. Like H or 27.
Another tip is that using .Column returns a number, not a letter.
Coding for absolute value. The code will enter the formula into the cell. The first set of code works but the user now requires the absolute value. The second set of code is my attempt but does not work.
Code: With Cells(LastRow + 1, 6) 'This works, not absolute value .Formula = "=SUM(F2:" & "F" & LastRow & ")" End With
Code: With Cells(LastRow + 1, 6) ' My attempted at absolute sum does not work .Formula = "=SUMPRODUCT(ABS(F2:" & "F" & LastRow & ")" End With
column headers are people in my department spread verticall goin down column A on my lead sheet. I am trying to report metrics for each person in their row.
I am trying to link the metrics for reporting from 5 seperate weekly batch reports that represent 5 tabs within the same workbook. The problem is that I have the total information for each metic in column H of all 5 tabs, but all metrics for individuals are reported vertically. (H6, H7, H8, H9)
Thus how do I get the following: =SUM('Week 1'!H4)+('Week 2'!H4)+('Week 3'!H4)+('Week 4'!H4)+('Week 5'!H4)
To Equal: =SUM('Week 1'!H5)+('Week 2'!H5)+('Week 3'!H5)+('Week 4'!H5)+('Week 5'!H5) When I copy the formula across the row for each individual
My column to stay constant, but my row to increase by 1 as I fill the formula across my spreadsheet.
I was wondering how I can replicate Excel's function abs(), which simply gives an absolute value of some number. Is there a simpler way to do that rather than creating a loop to test a condition? For example, I have a line:
w_1 = Range("E2").Value / Range("E3").Value
w_1 can be either positive or negative depending on the sign of E2 and E3. But I need w_1 to be always positive, no matter what signs E2 and E3 have.
However, I want the formula to count values when ABS($AM:$AM)=ABS($AM15) but I get an error if I try to put ABS($AM:$AM) as my range. Is there anyway to set my range as the absolute value of Column?
Using the attached example, how do I get Excel to display in cell F8 the correct SUM of cells F4 & F5 without having to manually enter the SUM in column F. I need F8 to the be the SUM of the amounts that are displayed.
I have a list of values in column A, and want to display in column B, in the cell where the cursor is when the macro is started (let's call it Bn), the sum of values (An:An-11), where n can be any row where there are values to be added. The following works fine.
I am using name manager to identify variables in formulas in order that I can be consistent with calculations. The problem is each line item have different variable amounts. In other words if I am calculating cubic yards of concrete I used (Length*Width*Depth)/27. Line Item number one is (100*3*2)/27 based on my dimensions given. Line Item two may be (50*2*1)/27 based on different dimensions. The Name Manager has Length, Depth, etc. as absolute references. What I am trying to do is copy each formula down the sheet but each time I do the new line item always calculates from the reference cell in absolute mode. Is there a way to copy this formula all the way down the sheet so it will pick up the different dimensions keyed in each time using the Namae Manager Variables set up?
Line 1 (100 x 3 x 2)/27 = 22.22 Cubic Yards Line 2 (50 x 3 x 1)/27 = 5.55 Cubic Yards
I tried multiple variations but have not succeeded.
My goal is to show negative as positive and positive as negative for place rank such as if someone moves from the #5 spot and then is #1 (wins the race) the change is a positive.
Cell A1 is 0.25 right? Right. Now insert a cell (or row) between cell A1 and A2. Then cell A1 changes to =A3/MAX(A3:A6). I don't want this to change. I still want to formula in A! to be =A2/MAX(A2:A5). I've tried using the "$" absolute character and I've tried using the absolute reference in the R1C1 reference style both to no avail.
I'm using the following formula: = LOOKUP($H5,I27:I32,J$27:J$32)- SUMIF(H5:H18,H5,J$4:J4). It's returning an incorrect value of "0". If I click in the formula bar, then click on the "fx" button & bring up the "Function Arguments" window. This gives a formula result of "4" (the correct value). Why is the cell value not the same? Are the formulas unreliable or volatile
