I have the formula in cell A2 =average(A100:A1100). However the range is not always the same so need a formula that will allow me to enter '200' in cell B2 and that will make the formulae in cell A2 calculate =average(A200:A1100). Likewise, if '300' is entered in cell B2 the formula in A2 will calculate =average(A300:A1100).
View 8 Repliesif i obtain an eqn by plotting a graph in excel, etc,
y = 1.22952117E-06x3 - 1.08197863E-05x2 + 9.97531712E-01x + 3.99014495E-01
and i wish to subsitube a cell value from another sheet into the 'x' in the above eqn and i need to do this 20 times.
I have five workbooks, one for each week of the month, and in a cell M9 in workbooks 1 to 5 I reference the value of a cell in another workbook called counter.xls.
The cell I need to reference changes each week by one row.
At the start of a month I need to have a macro that will go and find the first empty ...
In cell A2, I have the number 10. In cell G10 of a different sheet, I have a number I want in a third location. Is there a way to do something in the third cell like =G(A2) so that the value of cell A2 is entered into the formula, referencing the value of G10 in the third cell?
View 2 Replies View RelatedI want to create a formula that will refer to the cell I want by adding a number to the cell reference, not to the contents of the cell. For example, lets say that the cell C11 holds a value of 22. I want to add 4 to C11 to get my formula to reference cell C15, I don't want it to add 4 to the contents of C11 which is 22 and would give me a result of 26. I have a large spreadsheet and I can't just fill down the way I have it now and I don't want to have to retype the formula every time.
View 2 Replies View Relatedi have a group of numbers in A1:C2 and I need a formulas to find the closest number above and below a reference number in cell A3.
so if my numbers were 12.52, 12.02, 12.98, 12.64, 12.64, 10.83 and the reference cell was 12.62 the formulas would return 12.52 as closest below and 12.64 as closest above.
Column A contains numeric values that refer to a row number in column B. I need the value in column B to be outputted in column C. eg. if the value in Row 1 Column A = 55, the value in row 55 column B will be outputted in column C
View 3 Replies View RelatedI 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 am trying to drag down a formula (see below). the cell reference A6 and E6 is not updating to A7 and E7 etc etc when i drag the formula down using the corner thing. it remains constant at A6 and E6.
=HYPERLINK("#INDEX('Shipping Tracker Week X'!$F$4:$F$3000,MATCH(1,('Shipping Tracker Week X'!$B$4:$B$3000=A6)*('Shipping Tracker Week X'!$D$4:$D$3000=E6),0))","Link 1")
im pretty sure the problem comes down to the hashtag and quotation marks that i added, as the formula properly increased the cell references when i drag down without the hashtag and quotations. however, i sort of need it because adding those seems to be the only way i can get the hyperlink to link me over to the other sheet and to the correct cell containing the hyperlink.
part 2 of problem: the hashtag and quotations is currently a workaround method for me. the original intention was for the hyperlink to directly link me to a website, which is referenced from a separate sheet. however, without the hashtag and quotations, i get a "Cannot open specified file" error.
I am doing a vlookup on a cell range where the value I'm trying to lookup (a date) exists, but it's not an actual number in the cell...it's a reference to another cell with that value (somewhere completely different).
So, I'm trying to vlookup(date(1/1/2011),A1:A12,2,false) to get the B column value.
A B
1/1/2011 #
2/1/2011 #
...
12/1/2011 #
However, the A column is not the actual date. It is a reference to another cell somewhere completely different that has the actual date 1/1/2011.
When I do a vlookup trying to find 1/1/2011, it can't see it there unless I overwrite the reference in A1 (for instance) with the actual date.
Can I do a vlookup and keep my cell references?
I have something like the following:
If Worksheets("RawData"). Cells(1, nColumn).Value = Worksheets("RawData").Cells(12, 16).Value Then
..do code (I know the cell location of interest at this point)
End If
Then I End up needing To Do something Like the following
ActiveChart.SeriesCollection(1).XValues = _
Worksheets("RawData").Range("L1:N1")
“L1:N1” above is a sample. Given the code above it I have the cell location in question but it is in the format such as .Cells(12,16). That doesn’t do me much good when range wants something like “L1”. I don’t know how to convert that (12,16) to a L16 for example.
I'm trying to count the number of non-empty cells beneath a reference cell and would be most grateful for a few pointers. The code I currently have (that doesn't work) is:
View 2 Replies View RelatedI am building a Workbook which takes data from SheetA and inserts it into SheetB.
Part of the data is only entered when a positive value exists.
I then do an export from SheetB.
The problem is that I need to get the column number and pass it to the cell reference based on the field name in row 1.
Dim sFindstring As String
Dim rFindcell As range
Dim iR As Integer
Dim iC As Integer
sFindString = " Find this string in the cell"
'Using cells find the findstring
Set rFindCell = Cells.Find(What:=sFindString, After:=[A1], LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
'OK so look here
iR = 3
'I am trying to pick up the column number
iC = rFindCell.Column
rFindCell throws an object or with block variable not set error. Is there some property that I need to set.
need to do to the below code so that when i drag the formula down it changes the sheet number....sheet1, sheet2, sheet3 and so on but keeps the cell reference the same?
View 4 Replies View RelatedI have an excel database where I register cases. I have in it a button that creates a folder with and ID nr that is in column A (I create new ID nr in the next row, when I press the button it will create a folder with that ID nr and inserts a blank word document in it). We have a template that we copy to the folder (depending what type of case). The idea would be that once the template is filled in and ready to print, It would take the values from the ID nr and a reference number a few cells to the right. Is it possible to tell excel to open the word document in the folder and create a PDF version with the ID nr and reference number. (there are only 2 templates, so the macro would have to look for one of the two in the folder) The names of the templates are: "Standard" and "Other". I guess the best way to start maybe this would be that I select the cell with the ID nr and then press a macro button to have this done. One thing that needs to be done, is to put a copy in the same folder and another in a second folder called "Binder" in my documents folder.
View 1 Replies View RelatedHow 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?
How can this be done?
E.g. say I want to create a list of cells each referencing to the first, second, third sheet, and so on.
Say, on one sheet cell A1 references to the second sheet's A1, cell A2 references to the third sheet's A1, and so on.
Ideally I'd love to be able to write something like
=Worksheet(1)!A1
=Worksheet(2)!A1
=Worksheet(3)!A1
and so on.
I have a table that displays data from another worksheet. This is what the cell reference behind the table look like:
View 2 Replies View RelatedI 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.
EXAMPLE:
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 have a workbook with 1000+ worksheets, all of which have 3-letter names. On a master sheet, I would like to make a query of how many non-empty cells there are on a subsidiary worksheet. This works:
Code:
=COUNTA(ABC!A:A)
What I'd like to do from time to time is input in column A a varying set of 3-letter worksheet names, say
AAB
ABC
CDE
And have a formula in column B that converts this to
=COUNTA(AAB!A:A)
=COUNTA(ABC!A:A)
=COUNTA(CDE!A:A)
I've learned that simply substituting the cell references A1, A2, A3 for AAB, ABC and CDE doesn't work. What do I need to do to achieve this?
Let's say I've ended up with the number 8 in Cell D4 for example. Is there a formula that can return the letter "G" (The 8th Column) so I can use it in future cell references ? If so, let's say we store that in Cell B5. How do I now refer to a cell in a chosen Row of that same Column by reference to Cell B5 ? For example if I want to refer to Cell G33 can you refer to this Cell in some form like Cell(Contents of Cell B5;33) ??? Don't want to use R1C1 type references if possible.
View 9 Replies View RelatedI have data coes that need to be converted, basically need to remove 1st and 12th digits, 12th digit only, or 11th digit. I have built spreadsheet with a mid sub formula to do all 3 separately, but cannot figure out how to combine the formula to do all three.
I am attaching the spreadsheet,
Following code doesn't work
Dim Value1 as String
Value1 = Substitute(Cells(4, "A"), "A", "", 1)
correct syntax to assign the result of substitute function in the Variable?
I have a formula who examines if they are the same. The last number at first quote is not a problem (the 45). The problem occurs only and allways when the horse's name have an apostrophe " ' ". In first example apostrophe is different from the usual. If i manually delay it and replace it - type keyboards apostrophe everything works fine. Obviusly its a symbol. I want a small macro for replacing all these symbols at, lets say column B ,where these names are located. How can i use substitute at this case?
I understand that Microsoft left Filesearch out of VBA for Office 2007 because it was buggy. Sadly, however, I still need it. It would help if they placed a comment in VBA help that stated that it was discontinued. As it is, they make it look as if Filesearch is still available for use. OK, I'll stop ranting now.
After reading several earlier posts, apparently I have to use the Dir function. I learn visually and cannot figure out how to use the function from the VBA help file since there are no examples. Does anyone have an example of code using the Dir function to insert filenames in an array? Once I see the code, I'm sure I can adapt it to suit my needs.
I have a whole bunch of dates in a column that look like this:
5/01/1998 when it is supposed to be 5/01/2098 is there a character you can use when you are using the find & replace mode i.e.
*/**/20** I know it is not the asterisk but I believe there is some other character that can be used and it won't change any other of the numbers except the 19. I tried reformating and it does'nt work because of the way they sent it to me.
Is there a way to use something like the SUBSTITUTE function when sending an e-mail using a macro?
Here's my ....
I've got a workbook with multiple formulas e.g.:
t=S/D
TCh=Ch*(S^2/2D)*(D/Q)
TC=Co*(D/Q)+Ch*((i-D)/2i)*Q
which are built by implementing the symbols:
t, S, D, TCh, Ch, Q, TC, Co, i
I'd like to substitute the symbols in following sequence:
t = a
S = b
D = c
TCh = d
Ch = e
Q = f
TC = g
Co = h
i = x
Questions:
1) Which formula could substitute the symbols automaticly?
2) If there are up to 100 formulas in the workbook in the range of A1:E100,-
would it be possible to to set the substitution for the mentioned range?
See the attachment.
I would like to take a string such as R0-H6-D2 and return a number (1-4) based on one of four values for R, H and D in a separate column for each. The attachment should make what I am trying to do clearer.
Substitute.png‎
I have a text field (description) and in the description i have a product code S followed by 7 digits and then in the process of pasting into excel i have lost the space after this code and before the next text. E.g. "Ballpoint pen S1234567With Free Delivery" should be "Ballpoint pen S1234567 With Free Delivery".
I dont know how to say =if("S" followed by 7 numbers,subsitute ..... etc)
I understand how to use IF and substitute. its the 7 numbers part i am stuck on.
I could do it in access with the wildcards but excel is different.