Cell A1 can have only 0 or 1. Cell B1 can have any numeric value. Cell C1 takes the value of B1 if A1 is 1, otherwise it is 0. So, C1's formula can be either "=A1*B1" or "=If(A1=0,0,B1)". Lets say this formula is continued down to the 65000th row. My question is which formula is faster? The first one or the second one?
View 2 Repliesif I could find a good book on formulas, I might be able to raise the bar on my questions.
View 3 Replies View RelatedIm currently using a formula in my report to track training completed by employees. The formula looks for the employee number in the report and the course name and marks Y to say if completed or N to say if not completed.
The level of the employees dictates what courses they need to complete i.e if its a store manager they need to complete all courses where as a bronze employee only needs to complete the first three courses.
At the moment i drag the formula up to the course they need to complete. I would like it if i could add something to the existing formula that looks at their level and then automatically only populates the formula up to the courses they are required to do.
Its very hard to explain so i have added an example:
One of my coworkers is trying to create a document that has 3 columns in one part of the spreadsheet, but further down on the page, he needs to have 4 columns but wants them to use the same amount of space as the three columns. I have looked around and don't see how that is possible.
View 2 Replies View RelatedIs there a way to store two pictures in a hidden sheet (Sheet1) and have an Image ActiveX box in all visible sheets, and use a macro to auto select an image to occupy that ActiveX box?
View 2 Replies View RelatedI would like to build on my Excel VBA but not sure where to start. P.s I want to aim towards reporting side of things so make reports look good, copy data from sheets onto another sheet, formatting all done through vba, macros
View 8 Replies View RelatedI have an excel document displaying graphs that i would like to link to from a menu page (via a number of buttons). The problem is that i have about 12 of these graph sheets (along with 8 other sheets) and i'd rather not have them cluttering up the worksheet tabs list at the bottom of the screen.
Does anyone have any suggestions about how to handle this? Is a macro to open a hidden sheet and then close it once it is navigated away from a reasonable option? Or does Excel protocol suggest that they should remain visible, despite their cluttering..
I'm using a variable to loop down a column of data. Each time it loops the variable may or may not perform an operation - it will if there is something in the cell but won't if the cell is empty. It writes the answer of the operation to an ajoining cell, before looping again.
The issue is that if the row cell is empty, what's written to the ajoining cell is the pre-existing value of the variable, because its value hasn't changed.
how best to 'empty' the variable each loop to ensure either that the correct value or nothing at all is written to the ajoining cell?
I have a database file where users name can be located in 2 neighbor columns. Against each user name (in next 2 cells on the right) there is a evaluation (good or bad).That database will be growing and new additions will be added each month. That means one user can appear several times on a different row with different evaluation.
In the same file I have a separate sheet where I need after running a macros to have aggregated list of users (in one column) and against each user I would like to see how many how many good and bad score he have. It will be good if users are sorted in alphabetical order.
I am providing an attachment with example file.
In creating a chart that calculates home prices I need to be able to just enter "Good", "Fixer", "Remodeled", etc...and have that automatically use one of the numbers that I have set and add it to my total. I have created a box with variables for "Good", "Fixer", "Remodeled", etc...How do I make it so I can put any of those variables into the condition of the house row and make it add the dollar amount specified into the totals box? This is the very simple code that I am using to find the totals so far: =SUM(C5:J5)
Here's what I need:
Also, the plug in values in the first picture WILL CHANGE and I don't want to have to change the formula. So I need something to point to those cells.
I have a very large inventory spreadsheet (30,000+ lines) where finished goods are listed on one line and the raw materials (parts) that make up that finished good are listed below. After the last raw material for a finished good is listed, the next finished good is on the next line (and so on). First, I need to sort the lines to eliminate certain common raw materials. Next I need to take the lowest raw material inventory level and have that as the default inventory level for the finished good.
View 2 Replies View RelatedI am trying to use a button on the top of the sheet to add a line from wherever they are in the worksheet with the formulas on the above row. Example: 0=formulas
0---------------0---------------------0--0
they are here beneath the line
they press the button on the top of the sheet
0---------------0---------------------0--0
the row is created above the current cell their in with the exact formulas intact.
I am trying to have the sheet protected (so the formulas are not seen) but in order for the user to add a line they have to copy and paste the formulas above to the new line which can't be done when formulas are protected.
I have a simple spreadsheet that will be used by others for records management (rehabilitation case management). I have entered the code for most of the 'stuff' that I need to make the workbook easy and simple to use, and get the data back to head office each month.
Because the users will be emailing their records and I haven't yet worked out a way of sending just the "open cases", I need to keep the workbook/worksheets small (and to maintain good programming).
In column L, I have copied this formula from L2:L501:
=IF(ISBLANK($K2),"",VLOOKUP($K2,LOCSTAT!$A$1:$N$299,2,FALSE))
Column K is where the case-managed person works and so column L returns the geographical location value. Obviously it's blank until the case is opened by starting a new record and entering each field with data. For example, the user can select " Finance" from a validated drop-down list and the adjacent cell (to the right) will look up the value and return "Parramatta".
How do I translate the cell formula to some VBA?
I suspect that the static VLOOKUP range in the formula (LOCSTAT!$A$1:$N$299) will need to be dynamic range as well - I can follow the instructions for a single column dynamic range, but I am also getting lost working out how I can create a dynamic range for all data on a dedicated worksheet.
I'll be able to translate a solution to other cells as well
I have this macro that adds whatever formula that is in L1 to the same cell down 66 rows. But it doesn't work like the way I want it to because it's stopping when it sees empty cells...so the problem is that my colum is actually completely empty...so how do I get it to stop when I want to?
Sub every66rows()
Dim i As Integer
i = 1
With Range("L1")
Do Until IsEmpty(.Offset(66 * i))
.Copy .Offset(66 * i)
i = i + 1
Loop
End With
End Sub
I am looking to create a userform to add new rows to a sheet, and continue the formulas in certain columns.
The user starts by entering a number in the text box tbRowAdd. When the user clicks on the button called btAdd it first checks to make sure the number entered is equal to or between 1 and 1500. If this is not the case it should display a message box saying "You must enter a valid number from 1 to 1500 in the tex box."
If the number is valid it should then add that amount of rows below the current active cell in the worksheet ReturnData.
It should then continue the formulas in the A, B, C and F columns. The formulas are as follows:
A: =IFERROR(IF($D6="", "", ROW($A6)-ROWS($A$1:$K$5)),"")
B: =IFERROR(RANK($C6,$C$6:$C$99986, 1),"")
C: =IFERROR(IF(OR(AND(ReturnData!$D6>=Search!$E$1, ReturnData!$D6<=Search!$E$2),OR(Search!$E$1="", Search!$E$2="")), IFERROR(SEARCH(Search!$E$3,$E6,1),"")-(-IFERROR(SEARCH(Search!$E$4,$F6,1),""))-(-IFERROR(SEARCH(Search!$E$5,$G6,1),""))-(-IFERROR(SEARCH(Search!$E$6,$H6,1),""))-(-IFERROR(SEARCH(Search!$E$7,$I6,1),""))+ROW()/100000, ""), "")
F: =IFERROR(VLOOKUP($G6, EquipmentData!$B$3:$C$1048576, 2, FALSE),"")
Each number in bold should match the row number (I dont know if this happens automatically or requires coding).
I have a spreadsheet with customised formats and formulas and the problem is that when add a new row I have to go through and manually copy all the data to the new row and as there are many columns in the spreadsheet this takes forever.
Is there anyway I could automatically copy all the formats/formulas when I insert a row?
PS: I do not want to copy any data into the cell only the formatting and formulas
delete rows without affecting formulas
In general, when you write a formula:
In Cell A1:
a huge spreadsheet that is taking up way too much time. It starts out with 6,000-7,000 rows, but then, she does Subtotals & it grows to nearly 10,000 rows! Then she has to deal with each & every one of the Subtotal rows, by hand. Here is a small sample of the spreadsheet, after the Subtotals have been applied:
Manifest Dttm
Manifest No
Carrier Scac
[Code].....
This sheet has columns A-Q. The key columns for our purposes are K (Weight In Lbs), L (Pickup Charge) and M (Consolidation Charge). We need to add information to column R on each SUBTOTAL row only! On each row there will be an amount in EITHER column L or column M, but not both.
1) If the amount is in L and K is LESS than 488 then R needs to show 44.39.
2) If the amount is in M and K is LESS than 124 then R needs to show 3.82.
3) If either
a. The amount is in L and K is 488 or HIGHER or -
b. The amount is in M and K is 124 or HIGHER
THEN R needs to show a formula to do this: (L+M)/(K/100)
Is there a way to either put a formula in just the Subtotal rows to accomplish this or (preferably) have a macro enter either the 44.39, 3.82 or the formula? I was able to come up with a formula that gets the correct amount in col R. Here's my formula:
Code:
=IF(AND(M3=0,K3123))),SUM(L3:M3)/(K3/100))))))
delete rows of a report which contains $- or zero value but not row with Beginning Balance. Data begins from Column B to Column E. Some records may need to be deleted completely from Account Number down to Subtotal rows plus one empty row, if the Subtotal amount is $- or zero. Below is the sample data and how the finished sheet should look like. The report will comes in thousand of line. I don't have knowledge in programing but believe it can be done through VBA.
Account Number
211-00-5936-00-001-3-00-2-00
Trans. Date
Doc. No.
Trans. Line Comment
Actual Amount
[code]....
if Subtotal is $0, then delete the row starting from Account Number to Subtotal plus one empty row
211-00-5936-00-002-3-00-2-00
Trans. Date
Doc. No.
Trans. Line Comment
Actual Amount
[code]....
I have a spread sheet that I have locked with the expection of certain cells. I also have left the ability to insert and delete rows. The cells that are locked have functions in them run in sequance.
Is there a way that when the new row is inserted its copies the formula from the row above automatically but also adjust for the new row and adjusts the rows below it automatically?
For example if I insert a new row between rows 2 and 3 below.
(orginal layout)
a1 (unlocked) b1 (locked)(function is =sum(a1:b1)
a2 (unlocked) b2 (locked)(function is =sum(a2:b2)
a3 (unlocked) b3 (locked)(function is =sum(a3:b3)
a4 (unlocked) b4 (locked)(function is =sum(a4:b4)
(layout after inserting row)
a1 (unlocked) b1 (locked)(function is =sum(a1:b1)
a2 (unlocked) b2 (locked)(function is =sum(a2:b2)
a3 (unlocked) b3 (unlocked) *inserted row*
a4 (unlocked) b4 (locked)(function is =sum(a4:b4)
a5 (unlocked) b5 (locked)(function is =sum(a5:b5)
this is what i want the end product to be.
(layout if formulas are copied and adjusted after inserting row)
a1 (unlocked) b1 (locked)(function is =sum(a1:b1)
a2 (unlocked) b2 (locked)(function is =sum(a2:b2)
a3 (unlocked) b3 (locked)(function is =sum(a3:b3) *inserted row*
a4 (unlocked) b4 (locked)(function is =sum(a4:b4)
a5 (unlocked) b5 (locked)(function is =sum(a5:b5)
I have 100 excel file in a folder such as File1, File2, File3, File4 etc.There are some figures and reports on these files. format of all the excel files are same except values. And I have a master file.
As is:
On my Master file, I have n number of formulas in Row 10,which gets data from the above excel files.
eg. Master file Sheet1 Row 10,
I have formula like this from Column B
='[File1.xls]Sheet1'!$A$1 [File1.xls]Sheet1'!$D$1, ='[File 1.xls]Sheet1'!$A$1 [File 1.xls]Sheet1'!$M$1 etc.
In Row 11,
='[File2.xls]Sheet1'!$A$1 [File2.xls]Sheet1'!$D$1, ='[File2.xls]Sheet1'!$A$1 [File2.xls]Sheet1'!$M$1 etc.
If there are 1 or two files then I can type manually in each rows, But I have more than 100 files. So i need a macro which copies the formula from Row 10 to rest of the rows, If there are 100 files with different names in the folder, the formulas have to be copied to 100 rows in the master sheet.
if the file name changes on the formulas I will get the data correctly.
I have a large amount of data on a sheet which is being looked at by the following formula
{=AVERAGE(IF(LEFT(RAW_DATA!$C$4:$C$10000,LEN(Dashboard!$I$10))=Dashboard!$I$10,IF(RAW_DATA!$A$4:$A$10000=$A2,RAW_DATA!$J $4:$J$10000)))}
However, i don't need to keep the oldest data so I want to write a macro that deletes some of the top rows. Doing this in the normal way causes the formula above to change. I.e deleting one row cause the range to become $J$4:$J$9999 and so on.
Is there any way of deleting a row without affecting the formulas that are looking at it?
I currently have formulas in a column setup (equations are not actual)...
Cell B1: =IF(A2>A3,"Y","")
Cell C1: =IF(B2>B3,''Y",''")
Cell D1: =IF(C2>C3,"Y","")
....
I want to switch these to one long row from one long column so that they span from Cell A1, A2, etc.
When I special paste or straight paste it obviously alters the formulas because I am also unable to cell lock the current formulas.
I am wondering if there is an easy way to convert my formulas, currently along a column, to a row? Or if there is also a way to cell lock a large amount of cells at once?
I have some mails in a colum and i would like to put with a formula into a cell.
For example, in column I have:
mail1@hotmail.com
mail2@hotmail.com
mail3@hotmail.com
mail4@hotmail.com
mail5@hotmail.com
mail6@hotmail.com
And into anything cell i would like to put with the coma:
I want to copy an active cell in row 2 (I dont want to fix a cell as it can be any cell at any time). I want to copy it all the way down to a cell where the next row after it, is completely blank on all the 256 cells.
View 6 Replies View RelatedI have a problem with deleting rows that contains invalid formulas, these formulas are generated randomly daily in rows that contain no data and refer to empty cells in the same row.
Is there a code to delete rows an empty row in that by data it is empty, but not so because there is a formula?
currently this code of mine doesnt work on the zero data but formular-ed cell.. cause counta on the cell gives me 1
Dim i As Long, EmptyR As Long
EmptyR = ActiveSheet.UsedRange.Rows.Count
For i = EmptyR To 1 Step -1
If Application.CountA(Cells(i, 1).EntireRow) = 0 Then
Cells(i, 1).EntireRow.Delete
End If
Next i
How would you copy a row's formatting and formulas but not value. for example: A1 1 B1 =a1+2
I would like the copied row to be: A2 (blank) B2 =A2+2
Formula: B2+C2
In columnD I want to reference the "formula" and have it calculate based on the values in whatever row references the formula.
As it stands I can only get the formula to calculate within the same row.
I have a spreadsheet with a lot of financial information going down a column (about 500 rows down). Most of the cells are just typed in, but certain rows have sum formulas in them. Is it possible to copy values from another workbook, paste the values in, but somehow keep the formulas that are running through?
View 1 Replies View RelatedI am looking for some VBA programming that will allow me to use 1 button to consolidate rows (delete them). The problem I am running into is that the rows I want to delete have formulas in them so I can use "find all blank cells & delete" type of macro.
Capture.PNGCapture1.PNG
Attached are a couple of brief images on a small scale what I am looking to do.
I want a user to be able to have this sporadic list of choices, consolidate them so the are all one right after each other, then export to a word file for editing later on.