This may be an obvious one, I have received a spreadsheet with cell references or references to values that I am not familiar with, is anyone able to shed some light on how to interpret these references or at least find out where their source is within the spreadsheet?
View 3 RepliesI've been working on a spreadsheet and these forums have been a great help. I'm now at the very last section and, surprise surprise, it's also the hardest!
I'm creating a stock trade recording sheet. I have a userform ask the user to enter a date, a time, the number of stock purchased, and the price of the stock. These are then entered in a new row.
Now what I want to do is have summary cells which say how many stocks were purchased and the total profit made for each day. Since each time is given its own row, I can't know in advance which rows to sum over. I also don't know on which days a trade was made. So a summary cell should only exist if a trade was made that day.
Could I do something like.... check if the date matches then sum over all the values for that date? So if column A has the dates, can I say "Search which rows in column A have this date" then "for those rows, sum column C"?
Also, how would I create a a row for each traded date's summary cell and enter the date in it? I've attached a spreadsheets which manually demonstrates what I want to do (no macros) and a spreadsheet with what I have so far (basic macros).
Any and all help much appreciated, I just need to get my head around creating and dealing with variable ranges. Is that a really advanced task? I don't think this is a one line solution so please bear with me while I make mistakes!
I am trying to use an absolute cell refernce on tab 2 that refernces tab 1.
When I insert a row on tab 1 (tab name is week 1)the refernce changes. For
example cell A4 the formula is ='week1'!$a$4
when I insert a row on the week 1 tab it changes tab 2 formula for cell A4 to
='week1'!$a$5
What can I do to make it not make that change but still be able to insert
rows?
I am typing a number into Cell B4 (ex. zza4582) this number is a serial number that will be in many differnt cells in column "A" on a different sheet "Template Paste". On each row with that serial number I need the information from "A-I" to be copied in the main sheet starting on row 10 and continuing on from row to row depending on how many times the serial number is found.
View 10 Replies View RelatedOn a workbook named "Main Book" I have my whole inventory list on this sheet.
totaling over 200,000 items with 4 colums per row
Material # - Description - Family # - Price
(seeing how there is only 65,536 rows I have this list across over 20 colums)
So what I want is to have a VLOOKUP referncing this workbook
example:
material # / description / price
H047071504 / plate 1" mt box / $650.29
H152369500 / burst disk 5000psi / $45.65
manual enter / auto fill / auto fill
In a different Workbook when I type the "Material #" I want the next column to automatically fill in with the information (Description/Price) referenced from "Main Book"
how to set up an equation for an unknown variable in a formula equal to 0 (an Even-Money Probability Formula for Risk Arbitrage assessments).
The formula is as follows, where I am attempting to solve for Pe:
(Pe*EP)+(P2*EL)=0
Pe=even money probability (placed in column B1 in Excel)
EP=expected profit if deal closes (placed in column B2 in Excel)
P2=probability of deal breaking up (=1-Pe) (placed in column B3 in Excel)
EL=expected loss if deal breaks up (placed in column B4 in Excel)
B1*B2)+(B3*B4)=0
So, once again, I am attempting to solve for Pe, or column B1
B1=(-(1-B1)*B4)/B2
say, EP (or B2) =$2.00 and EL (or B4)=-$4.00
B1=(-(1-B1)*-4)/2
B1=2*(1-B1)
i am trying to remove the unknown character and extra space from the name. Though i use formula as trim or proper(trim), it is not removing the Unknown character / extra space. I have attached the few name as sample. Formula to remove these Unknown character / extra space, double space, special character from selected cell?
Note : I am using ms office 2013
I am using a spreadsheet written by someone else that contains a type of cell entry I am not familiar with. These occur in several tables. The first 3 columns of each table contain numbers or basic formulas. These are followed by two columns in which every entry appears to be the following:
{=TABLE(,B33)}
Despite the same apparent "formula", the number displayed in each of these cells is different. If I click on the formula display box (to the right of the cell address box) to edit the "formula", the brackets disappear.
Can anyone tell me what sort of beast I am dealing with here?
I'm attempting to create a macro that will look at the total in column (K) and send an e-mail to two different addresses, depending on the amount. If the amount is over $10,000 then one address (over@macro.com) if under, then the other (under@macro.com)
My main problem is that I never know what cell the total is going to be in.
I currently have all quotes going back to the person that sent in the request, no matter what the amount. Their e-mail is in the sheet.
So what I think I need to do, is find the last cell in column K with data and assign it a variable. If that variable is over $10,000 then I can send the e-mail to a hard coded address. If it's over, I just use my original code.
I'm trying to define the last row in a column which is determined from a find command. It's quite a large sub, and the rest is working properly, so I'll just post the (still rather large) troubling section.
'startingPoint is declared as a range. Destbook, reporttarget and FullControlName all exist.
'this sets StartingPoint to a one cell range in a sheet named from a custom class in a workbook named from variable destbook
Set StartingPoint = DestBook.Sheets(ReportTarget.Name).Cells.Find(what:=FullControlName, searchorder:=xlByColumns)
WriteCol = StartingPoint.Column
'this changes the range to include the whole column of the existing range
Set StartingPoint = Range(StartingPoint.EntireColumn.Address).....................
I have a set of data in column R, with an unknown number of rows that looks like this
Days Late
-28
150
3
16
41
.
.
.
and I have written a script to add an adjacent column "S". I want to fill column S with conditional values based on the value of column R, sorted into categories such as "On Time", "Less than One Month Late", etc. Here is what I have so far, it doesn't work:
i need the same result as in table bellow (yellow column)
I need tipe "Voice" if it word conteind in text
I used ( =IF(D8="VOICE*","voice","data") )
But it doesn`t work
Using web queries i get in a cell ie:
3¾
or
1¼
The last part of these "numbers" (3/4, 1/2, 1/4) is text that i want to convert into numbers (0.75, 0.5, 0.25).
If i isolate this text in a cell (with the right() formula) the code() formula gives 63 as result for all the above texts.
I may solve the problen storing these texts (3/4, 1/2, 1/4) in separate cells (pre-fixed in some cells) and then for my new data (from query) do some search/find .
Is there a better way solving this using a different way-macro?
is there a way to use LINEST in a way in which the x values are unknown and the y values are known? The opposite of how the function usually runs...?
View 2 Replies View RelatedI'm modifying a template that originally shipped with Excel 2003 (I have not upgraded to 2007). In the template, there is a pop-up box (not a dialog box) that shows up when I'm on certain cells. I've attached an image of it. It's the yellow box containing the words "Company Information..." etc. I cannot find any way to remove it! It's not a comment, and selecting it doesn't allow you to edit it. What is it, and does anyone know how to remove it?
View 2 Replies View RelatedTrying to sum up a column that has an unknown length. This is a canned Excel form that is saved within a software system and is used to create customized documents. Usually, I would enter the table name.field name in a cell to pull the data from various parts of the system.
In this case, that data resides in AA234 of my customized Excel sheet. When the user retrieves this doc from the system, the data could run several rows, starting from AA30 to AA255. So, in my "Total" cell, I've entered =SUM(AA1:AA255), so it will just add up whatever is in the column, regardless of how long it runs upon document generation. When I try to pull the document, I get "=SUM(#REF!)" in that field instead.
Need to sum through an unknown number of row generated by advanced filter at run-time
Formula at C16
Data starts at row 57 (from copy/paste - advance filter)
Last row unknown
Criteria to match in A16
Data to be evaluated in B57:B (row unknown)
Found this formula on microsoft.public.excel.misc
=SUM(A57:INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))
Which I modified formy start row
is it possible to creaate a macro to highlight a range that starts at a1 to a? and there could be blanks amongst that range. the range will be unknown
I'm trying to set a range to a particular cell in Col D but I don't know the row number because this can vary depending on the amount of data entered. At present my code looks like this
Dim MyCount As Integer
Dim r1 As Range
'No of rows in Col D
MyCount = Range("A2:D2", Range("A2:D2").End(xlDown)).Rows.Count
Set r1 = Range(Cells(MyCount, 4))
The problem is with my line
Set r1 = Range(Cells(MyCount, 4))
The colours are just to mark the ranges
As every month is different the number of sheets adding up to the Red sheets(week total)
will change and the same with the Blue sheet(month end total)
Is there a code I can run for this summing up to be done?
Yellow is where data is entered
Red is where the Yellows range sheets need to add up before it
The Blue is where all the Red range sheets need to add up
Is it possible to display a dialog box or msgbox that doesnt have an OK button ?
i.e I want a message that comes up on the screen that says "Links Updating...Please Wait" which then automatically changes to "Links Sucessfully Updated" on completion...I dont want the macro to be interrupted by the msgbox/dialog...
I have a spreadsheet (attached) with historic weather data for a certain location.
Column A (Weather Day)
1-Jan
2-Jan
3-Jan
...
...
31-Dec
Column B (Record High)
28.4
39.2
37.4
...
...
39.2
Column C (Record Low)
-36.4
-38.2
-32.8
...
...
-36.4
I have a user defined/input "Entry Date" & "Exit Date"
The code to return the max is as follows: {=TEXT(MAX((Weather_Day>=Entry_Date)*(Weather_Day<=Exit_Date)*(Temp_Record_High)), "0.0") & " áµ’F"}
This code works perfectly fine for all input dates and returns the Record High between any Entry/Exit day.
The code to return the min is as follows: {=TEXT(MIN((Weather_Day>=Entry_Date)*(Weather_Day<=Exit_Date)*(Temp_Record_High)), "0.0") & " áµ’F"}
It's identical except MAX is now MIN. However, the returned MIN doesn't always return the correct value. It is either correct or returns 0.0.
Examples would be:
Entry Date: 4-20
Exit Date: 6-25
Both values return correctly
Entry Date: 4-21
Exit Date: 6-25
Record High returns correctly, Record Low = 0.0
Entry Date: 4-21
Exit Date: 11-7
Both values return correctly. However, with 4-21 as the Entry, 11-7 is the first Exit date to return a correct MIN value.
I have my VBA Codes set in a workbook (Production) that my supervisors open to calculate production. So when they open the workbook (Production) I have a button that they push that starts the code however I need to put something in that activates the other open workbook, The problem is that the name of the workbook that they run the code on can change.
View 2 Replies View RelatedI need to autofill a range of formulas (A2:O2) but the actual range to fill up depends on the range of data on sheet1. I've managed to use:
View 4 Replies View Relatedis there a VBA way to determine an unknown path in which a KNOWN WB is located !?
Assume a WB named: 1X1.xls Located at C:TEMP
[In case the user uses more than one partition and/or more than one HD - it might alse be located at: D:TEMP
The known open command for a known file AND Path is:
I need to know the function to copy an unknown amount of rows. After a certain row it should copy everything.
So like after row A6 it copys every row below it with data in them then I want to paste it in another worksheet. I dont want to just do
Rows("6:65000").Select
Selection.Copy
If someone could point me to a good site that explains alot of useful VBA commands that would help me alot. Most of my problems come from just not knowing the full function names.
I need to put a value on every line having "file:" in the second column. The value requested is a sum of the numbers in the forth column following this line until the next "file:" line.
I know it is not trivial, but sure it is possible.
57
file:
HIRES-~1
#VALUE!
58
208
1
1
[Code] ...........
I have a column that begins in the same place all of the time (Cell C6) however the length of this column can vary. How can I copy the column using VBA?
View 2 Replies View Relatedi have a list of countries in a column and have to go through this column ensuring they are in the correct format. I'm struggling to figure out how to iterate through each row of the column, test it's value and then change if necessary...
for example
column a --> column A(modified)
Usa --> USA
Ukraine - Mobile (Umc) --> Ukraine - Mobile (UMC)
Uk --> UK
Uruguay --> Uruguay
so values in column a becomes like those shown above.
I have two access databases which export results to two excel files. I am trying to compair the two excel files (generated by access databases) to find the common data in the two files. I am using Vlookup function.
The data looks like this
File 1
Serial No Fault
40293 A
40294 B
File 2
Serial No Solution
40293 Procedure 1
40294 Procedure 2
The result should be
Serial No Fault Solution
40293 A Procedure 1
etc...
But the column serial no in file 1 has some unknown characters like 40293followed by a small square or a vertical line. The datasource for the access file that generates file 1 is actually a lotus notes database. I think thats what causing this. Is there a solution to clear these spurious characters?
The VLookup function works fine if I manually delete those characters.