I recently added a formula to a spreadsheet with som complicated formula. It worked ok and I saved the sheet. Now it takes 5 minutes to open the sheet and when I try to do anything,like delete the inserted column the program locks up giving a no responding message. I can do without this column if I have to.
View 4 RepliesH4 is a date/time stamp I have saved as a macro. Returns 12/28/2011 10:47:00 AM.
I4 is the same macro and returns 12/28/2011 10:48:00 AM
J4 calculates the difference between the two (I4-J4), but only recognizes business hours and business days (Monday-Friday, 8:00 am to 5:00 pm)
I only want J4 to calculate if I4 is NOT BLANK.
These are in a table so J4 is trying to calculate when there is data in H4, but not I4, and returning a large number like 981583.22
When I try to apply IF(ISBLANK) logic to the formula in J4, I get an error that it exceeds 255 characters, even though it works fine if I am not trying to put the IF(ISBLANK) logic in.
Here is the formula in J4. I want it to automatically calculate if there is data in I4. Otherwise, I want it to return 0.
=IF(AND(INT(H4)=INT(I4),NOT(ISNA(MATCH(INT(H4),HolidayList,0)))),0,ABS(IF(INT(H4)=INT(I4),ROUND(24*(I4-H4),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(H4+1,EndDt-1,HolidayList),0)+
INT(24*(((EndDt-INT(I4))-
(H4-INT(H4)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
MOD(ROUND(((24*(I4-INT(I4)))-24*DayStart)+
(24*DayEnd-(24*(H4-INT(H4)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))
I want a formula to do this... (x1*z1+x2*z2+x3*z3+....+xn*zn) where n is finite.
x1=a1
x2=a1+a2
x3=a1+a2+a3
xn=a1+a2+a3+...+an
z1 = 1.05^1
z2 = 1.05^2
z3 = 1.05 ^3
zn = 1.05^n
is this possible?
I have been trying to create a formula that will save me DAYS of messing around at work.
What I am trying to achieve is to have a sequence of numbers as follows:
BNA01A01 to BNA01A09 then have it change to BNA01B01 to BNA01B09.
This needs to be repeated for all letters to BNA01I09.
Then this sequence needs to be repeated to BNA12.
The last thing is for me to be able to change the formula in order to implement the same sequence on a separate sheet for BNB01A01 - BNB12I09 to BNL01A01 - BNL12I09
I want to set formula to cell S1 via vba.
This is the formula: =IFERROR(VLOOKUP(H3;'[VATCompanies.xlsx]1'!$A:$B;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!$D:$E;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!$G:$H;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!$J:$K;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!$M:$N;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!$Q:$R;2;0);I7))))))
How do I apply a formula to every cell in a given column?
I'd like my workbook to store this formula:
=c<current_row> + d<current_row>
in every cell of column P as a default.
This means that even if I do "insert row", I wouldn't have to copy this formula into the new cell.
I'm using the record macro function to make a code that is concerned with formatting information generated in excel by another software program.
My original idea was to replace the information in column F with the concatenated info of column F-L, but I couldn't get this to work either. So now I'm looking for a work around for my work around.
I can get the macro to work just fine with "Macro 1" below, but unfortunately when I use the entire column range, it populates thousands and thousands of rows below the (around 50) lines of information I have. However, the populated information in my sheet is completely variable - it could be 1 line or 5000 lines. These will also be printed, so I cannot just cap the range at 5000 because then it would print all of the blanks.
I tried several work-arounds after googling. I tried to leave the blanks and then delete them once they were populated - messy, and it crashed excel. I tried End(xlUp) and I tried using lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
how to only copy the formula in Column M to the last row of information (which would be the row with the last value in Column A).
MACRO 1:
Columns("M:M").Select
Selection.FormulaR1C1 = _
"=CONCATENATE(RC6, "" "", RC7, "" "", RC8, "" "", RC9, "" "", RC10, "" "", RC11, "" "", RC12)"
Range("M:M").End(xlDown).Select
Columns("M:M").Select
Selection.Copy
Columns("F:F").Select
[code].....
MACRO 2 (Only the Changes):
ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Select
Range("M" & lngLastRow).Select
Selection.FormulaR1C1 = _
"=CONCATENATE(RC6, "" "", RC7, "" "", RC8, "" "", RC9, "" "", RC10, "" "", RC11, "" "", RC12)"
Range("M" & lngLastRow).Select
Selection.Copy
I have a sheet (Sheet 3) that is pre-populated from another sheet (Sheet 1), and the user is allowed to update the numbers (for forecasting).
I have a column ( Total Hours) that I use to total the new numbers in the row.
If the user inserts a row, the Total Hours formula does not follow. This is the Total Hours formula that I am using SUM Formula
(=IF(SUM($G30:$AP30)=0,"",SUM($G30:$AP30))
How do I (Can I) get the formula to cascade into the new row.
I am needing to write a line of VB code for a macro that will insert a VLookup formula into a cell where the "named" table_array can be a variable. Example of what I am looking at below.
Worksheets("active Worksheet").("active cell").Formula =VLOOKUP(G2,variable,6,False)"
I need it to be imputed in the active cell of the active sheet with the variable able to be gathered possibly from a cell reference. Say the cell c3 on the active sheet says V080606, the formula imputed would be =VLOOKUP(g2,v0806,6,false).
I have a formula in my spreadsheet that shows dates for servicing of products. Column F show's Date Last Serviced and the adjacent column (G) then shows Date Next Service. When there is nothing on the F column's cell then the date that appears on my spreadsheet is 31/12/00. It looks messy and I would rather have a formula to make it so that if the F column is blank, then the adjacent cell in G column should stay blank also.
To automatically calculate the next service due date, I have variants of this formula that creates the dates in G column:
=DATE(YEAR(F4)+1,MONTH(F4),DAY(F4))
I tried adding as an extra part, for example:
=DATE(YEAR(F4)+1,MONTH(F4),DAY(F4))&If(F4 = 0, "", F4)
It always brings back the answer of 366, and does not make the cell blank...
[please refer to attached sample data]
The formula in K3 is
"=J15/10"
This formula calculates the average mailing return rate of all the mailings added together. J15 is currently white text to hide it from the end user.
Everytime I add a new row of data I need the formula to update itself automatically. So for example, the next time I add a row of data the above formula has to change to
"=J15/11"
Or if I were to add three rows of data the formula would have been
"=J15/13"
I'm having trouble getting my random number generator functions to use my variables that are defined by text box inputs. I can mouse over the variables in the code and they have the correct values, with the excpetion of where they are listed in the functions. I'm sure I'm missing something simple, thanks in advance for any help!
Private Sub cmdPopulate_Click()
StdDev = txtStdDev.Value
RandomNum = txtRandomNum.Value
Mean = txtMean.Value
lambda = txtLambda.Value
OriginCell = txtOriginCell.Value
If cmbDistributions.Value = "Normal" Then .................
Workbook 1 has 2 spreadsheets. Spreadsheet 1 contains Item and Pass/Fail Columns. under the item column is the serial number of the item tested. the Pass/fail column has the serial number duplicated if it failed tested. what is the formula is to have spreadsheet 2 pick the items from the pass/fail column on spreadsheet 1?
View 4 Replies View RelatedI have attached a document paralleling a document I am working on. The dollar amount in each spreadsheet represent sales. I have entered in values into the candy, soda, and chips spreadsheet. I have also linked values for candy into the total spreadsheet. My question is can I somehow type something or drag the formula down to populate the other cells in the total spreadsheet?
The idea I am thinking but which I don't know how to implement is to list all the items (as in column G) and list all of the relevant cells (e.g. B1 in the Candy spreadsheet) as in columns H and I (Note that all items will have the same cells but the cells will have different values...e.g. all three items have a cell B1 and B2 in their spreadsheet but these cells contain different values). I then try and fail to create a formula in cell B3 of the Total spreadsheet. I am trying to create a formula of the following nature:
='(Spreadsheet Name From Column G)'!(Cell Name From Columns H and I)
The Second half of the formula doesn't really concern me (i.e. the cell name from column H and I). However I am perplexed as to how to achieve the goal in the first parentheses above.
I have a number of array formulas that refer back to the table in the top left. The array formulas are in Columns I and L, and cells N200, N203, and P203.
Normally when I add a new line to the top left table (usually by clicking on the empty box below the previous final line and typing the date), the table expands by one row and the array formulas all adjust to match, i.e. all the A180/C180s in the formulas become A181/C181s, etc.
However every once in a while (maybe once a month), the arrays simply refuse to update, and I can't figure out why. When they fail, they all fail, it's not hit or miss. You'll see in the attached sheet that though I've added a line to the table (A181) and data to that line, all the arrays still read through A180/C180.
Why this randomly fails to update, and if it's something I'm doing incorrectly?
And that leads to an associated question. In the past when this has failed in this manner, I've laboriously gone through and changed all the cell references manually, in every single individual cell. (After doing it once, the arrays usually magically start auto-expanding again.) I'm certain there is a way to make those formulas refer to a named range, instead of all being manual references, but after spending an hour on it, I keep getting #VALUE errors.
I've been using [URL] ..... and pages like it as a reference without success. Obviously, I would prefer that the formulas auto-update without issue per my first issue above, but in the event that they don't, it'd be really nice to just change the ranges in one location and have that propagate across the sheet.
I have a formula that I want to insert using a macro, so how do I iterate the * values in this line?
ActiveCell.FormulaR1C1 = "='Sheet1'!R[*]C[*]"
I'm using a template that has formulas using a $ sign to attempt to stop the ranges they refer to changing. The problem is, when I use the template (which involes Access importing some data and adding columns to the sheet the formula refers to in the process) the rows referred to in the formula change in line with the number of rows of data that have been imported. Only the row numbers change not the column headings. So for example:
=( SUMPRODUCT(((Workings!$H$3:$H$1000=Explanation!B9)+(Workings!$E$3:$E$1000="Buy")*(Workings!$J$3:$J$1000))))-998
becomes
=(SUMPRODUCT(((Workings!$H$511:$H$1508=Explanation!B10)+(Workings!$E$511:$E$1508="Buy")*(Workings!$J$511:$J$1508))))-998
I am trying to get VBA code in Excel to copy formula in specific cells to copy the cell below when an entry is added to a database.
View 3 Replies View RelatedI need to create a Spreadsheet where a certain Value is added to the from of the data I enter into the cell. For example: All of my MAC Addresses start with 00:80:64. I want to be able to just type in the last three entries into the cell and have Excell automatically add the value 00:80:64 So I want to be able to add a specified value to ebvery entry in a collumn.
View 1 Replies View RelatedI want to pull data from my excel file (using VBA) into Ms Word. I created a template in Word and wrote a macro to do this, it worked, however, anytime a new row is inserted or deleted in my excel spreadsheet my macro produces wrong results in my Ms Word template because the cell position has shifted, thereby producing the wrong result. How do i make it that my result remain the same when new row is added to my spreadsheet.
Example:
Excel row1: vicky 528
row2: sam 532
row3: john 092
row4: Own 211 word template: 092
This is what happen next:
Excel (New row added) Macro: ThisDocument.ScreenedPatients.Caption = wb.Sheets("CSAs").Cells(16, 1)
row1: pat 542
row2: vicky 528
row3: sam 532
row4: john 092
row5: Own 211 resulting Word template(running macro): word template: 532
But I want John to automatically go on the Word template without going into macro to change it all the time when a new row is added.
The attached is a race keeper's scorecard.
Tab 1 lists all participants and race times.
Tab 2 generates a printout to post on a wall.
The spreadsheet works great, BUT, I forgot I needed to add a condition of DNS (Did not start) and DNF (Did not finish).
Once this is added, of course, it breaks everything done so far (damage being done in column M on tab 1 and all of tab 2).
My ideal state is that a DNS or DNF can appear in column L on Tab 1. . . and these participants fall to the bottom of the list generated on Tab 2.
I know there are miracle workers out there who can make this happen! Again, hoping for no code, and no manual manipulation if possible (end user is not Excel savvy). Must be compatible with Excel 97-2003.
I'm trying to create a formula that will added the correct amount in the correct cells, I have create a dunny sheet in trying to achieve this. If Cell B8:B11 = ABS or Dum that any points won should be added to Cell L8:L11 right now its adding it into K8:K11. If Cell B8:B11 = is Blank any player points should be added to cells K8:K11. I'm using this formula throughout cells K8:K28 =IF(J8>J25,1,IF(AND(J8<>0,J8=J25),0.5,0)) Any thing in red is incorrect anything in blue is what I'm trying to achieve.
View 4 Replies View Related[Excel 2003] I have 2 spreadsheets: one to summarize data from a 2nd detail spreadsheet. I'm analyzing work order information for a service operation.
I'm using dynamic name ranges, as follows, for the detail:
WO_Num =OFFSET('WO Tracking Log'!$A6,0,0,COUNT('WO Tracking Log'!$A:$A),1)
Other detail data is defined as these examples show:
GM_X =OFFSET(WO_Num,0,8)
OpenDate =OFFSET(WO_Num,0,1)
All detail data begins in Row 6 in the detail spreadsheet.
In the Summary spreadsheet, it appears to make a difference where my calcs are located in order for my COUNTIF's to work correctly. As long as I keep my summary calc (to total the number of work orders in the detail) in Row 2 of the Summary, it works fine...but if EITHER I move my calc down a row OR if my detail drops down a row because a row was added above (where I have just header info), my summary totals change?!?! I don't understand.
Here are two examples of the calcs I'm using in the summary:
=COUNTIF(WO_Num,">0")
=COUNTIF(GM_X,"X")
Can someone tell me what is going on? What I'm doing wrong?
I have a set of spreadsheets (Circa 50) that each have around 50 tabs of information on, one set of spreadsheets are from 2012 and another are from 2013.... basically i need to write a formula that goes in to every tab in each spread sheet and basically tells me the entries that are in the 2012 sheets but do not appear in the 2013 sheets. The results of this are the most important thing. All of the names that i need to search are in Column A of each spreadsheet and tab .
View 3 Replies View RelatedWhat I am trying to to is if I enter a number between 6-14 the next cell puts in the dollar amount 650.00 if I enter 15-29 it puts 605.00 if I put 30-49 it puts 575.00 if I enter 50-100 it puts 525 and if I enter a number over 100 it puts in 480.00
View 3 Replies View RelatedI have a rather odd question, is there a way to count how many formula's are being used on a spreadsheet?
View 9 Replies View RelatedI am creating a spreadsheet and have run into a brick wall. Row 1 contains a list of dates Mon-Fri as far out as I could go. Rows 4, 5, 6, and 7 contain a code. Using those, I want to figure out an if statement that will place a 1 in the cell if the date in row 1 falls between a start and finish date. The data sheet looks like this:
A2- Code
K2- Start Date
L2- End Date
This is the formula I tried, but it does not work completely for me:
=IF(AND(K$1>=Data!$K$2:$K$1000,K$1
I am trying to set up a cell (E10 on my attached spreadsheet) to act as a key that would collect all of the PO#'s from a seperate column (cells A12:A27 on the attached sheet) and wouldn't repeat any of the repeat PO#'s. I am trying to do this because I currently save each order form by the PO #('s) and at times I find myself typing a long string of PO#'s for the book name. It would be much easier if this key cell would reflect all of the PO's for me and I could just copy and paste the contents into the save as box. This brings up another problem- when I try to copy and paste the contents of the key cell I have now, I just get the formula, not what the formula returned.
View 10 Replies View RelatedI'm trying to set up an inventory spreadsheet for my work, but i'm running into a snag.
What I want to do is have three columns that interact with eachother.
1. Units in stock
2. Reorder Level
3. Reorder Tolerance
4. Status
What I want, is to have the status column react in one of three ways. Either "In Stock", "Reorder Needed" and "Out of Stock".
So if units in stock is at 110, with a reorder level of 100, and a tolerance of 10%, I want the Status to show "Reorder Needed" with a red cell color. But if stock is at 111 or more, I want it to be shown as "In Stock". Zero should be "Out of Stock".
Currently I am wondering if this is possible. I have a formula like,
=Total!O8+'Less-1.7'!O8+'Overall'!O8
In total, I have three spreadsheets by the name of that. Then I added a new spreadsheet called DummyTest. I wonder if I can make it so that this DummyTest spreadsheet will automatically go into the formula,
=Total!O8+'Less-1.7'!O8+'Overall'!O8+DummyTest!O8
My intention is to use the INDIRECT formula to put the spreadsheet name in a cell (refer to this topic that I posted earlier today - [URL]. The only problem is to get the formula to automatically add a new spreadsheet into it.
Note: The cell reference O8 is standard for all spreadsheets.