I have a requirement to enter 25 formulas into 25 different cells. The formula is this:
[Code]......
The only thing that changes is the cell reference at the end which goes up in increments of 20,000, i.e. R20000, R40000, R60000, etc.
I have this set up and it works fine. My question is whether there is another quicker way rather than have the above code repeated 25 times?
I successfully built a multi- cell array that sorts a bunch names into ascending order. But now when I need to use the same array formula again, I get the wrong results in the array formula cells and I can't figure out how I'm entering the array incorrectly. The error I get in each of the 7 cells is the identical number, "1".
Here's the deal. Cells B10 - B16 contain names and numbers that I need sorted in ascending order. Then, I highlight cells C10 - C16 and enter the following formula using shift-ctrl-enter: ...
I have a spreadsheet with multiple formulas in several columns - my staff will be using this spreadsheet to enter data throughout the year so it will be constantly touched by several users. Some of the columns are hidden, but I worry about someone sorting something funky or deleting or adding a row. Any way to protect the columns I have formulas in while still allowing them to enter data in the rest of the spreadsheet?
View 2 Replies View RelatedBasically I have a template from two different worksheets and I am making one sheet the "master sheet" and the other sheet is filled in automatically from the master sheet. I understand how to get this basic function to work so here is my real problem...
I would like my sheet to look like this repeated 2000 times...
'sheet1'A16
'sheet1'A16
'sheet1'A17
'sheet1'A17
etc.
Every time I try to drag the formulas down to go 2000 rows it starts doing this...
'sheet1'A16
'sheet1'A17
'sheet1'A18
'sheet1'A19
etc.
How do I get this formula to repeat like I have shown in my desired example?
I have a number of worksheets and need to move data from the same cells of each work sheet to a summary worksheet. I manually entered the cell id's for the first sheet.
Example =Jan!M6, =Jan!N7, =Jan!O9 etc.
I know I should be able to automatically enter these same cells for the remainder of my worksheets with out manually going in and typing each one (there are a lot of entries.) I just can not remember how to do it. The next sheet is named "Feb" and "Mar" etc.
Is there a way to use multiple formulas for just one cell without damaging the others?
View 9 Replies View RelatedI have compiled a spreadsheet and it has columns for the days of the week, Mon through Fri. I have written a formula in R9 which will add a range of comments in the Recommended Actions column (R) depending on what is entered in I9:M9. So far so good
Unfortunately when I enter anything in I9, then enter something else in J9 the "Recommended Actions" just adds another comment after the previous comment in the same cell as the formula in there references I9 & J9 & K9 & L9 & M9.
Can you think of a way to set it up so that when I enter something in I9 then enter something else in J9 it changes the "Recommended Actions" from the old comment to the new comment or deletes the old comment before adding the new comment?
Formula:
=IF($I9="OK","Working ok",IF($I9="No Modulation","Check Profile and modulation",
IF($I9="FLD","Check pulse unit and meter operation",
IF($I9="No Data","Check Comms",IF($I9="Low Pressure","Check pressure on outlet",
IF($I9="High pressure","Check outlet Pressure",
[code].....
I have a list of numbers that I want to identify the highest and second highest numbers and create a mathematic result.
For example.
I lets say I have 15 stocks "B19:P19" I have X amount of money to invest "Q21" I want to invest 85% of Q21 in the highest of B19:P19 and 15% in the second highest. If there are 2 highest that are tied I want to split the 85% of Q21. SO far I have been able to identify the Highest and show it at 85% of Q21 with this =IF(B19=(MAX(B19:P19)),(Q21*0.85)) but I am not sure how to get the rest of it to do what I want.
I am creating a holiday chart for absence, I have put the following formula in one cell
=COUNTIF(F4:NF4,"H")+COUNTIF(F4:AJ4,"HD")
Now when these letters are typed in to the range they calculate "H" and "HD" to be 1 - is it possible to have "H"=1 and "HD"=0.5 to then give a total in the desired cell?
Basically "H" is 1 day holiday and "HD" is (0.5) half day holiday, I would like the total from a range to appear in one cell
How would I write a formula if I was trying to get the cell to count all expired certifications (based on a range of cells and their expiration dates) as well as all blank cells? This is what I have so far and all it's doing is displaying "FALSE"... =COUNTIF(D2:D95,"
View 5 Replies View RelatedI am working on a massive Pivot Table but in order to compile it I have to copy data from many worksheets into one. My problem is that each worksheet contains about 11,500 formulas with references to other workbooks and cells. However, none of these is locked for cell reference (meaning none has the $ sign for row or column) so copying and pasting obviously screws it up since the reference is changing but I need to have the links active in the pivot table.
It would obviously take me a year to add 440,000 individual $ signs. Is there any way, a trick of sorts, where I can just highlight the entire worksheet and add the $ sign to each and every cell reference automatically?
I would like to put the following two formulas in one cell, so if one condition is met, it returns 1:
Code:
=IF(IF((AND(MATRIX!C4=0.795),1,0)
Code:
=IF(IF((AND(MATRIX!C4>=-0.094,MATRIX!C4=0.795),1,0)
The formulas work fine as single formulas in one cell, but I can't figure out how to do both in one cell. I tried this but I get errors.
Code:
=IF(((AND(MATRIX!C4>=-0.094,MATRIX!C4=0.795),1,0,IF((AND(MATRIX!C4=0.795),1,0)
I've also tried using the OR function, get errors. I'm not sure which to use, IF or OR, and I can't get the syntax right on either one.
I actually have six formulas for each cell, but how to do two, then I'll know how to do all six.
I currently am using Data Validation drop-downs (which are identically referenced) in a number of cells (From J10 to J19). Each entry refers to a Crew departure &/or arrival time (based on an Aircraft schedule) and crew Subsistence & allowance ($17 per day). Referenced from “K10:K19” is USD currency:
DAYS:CURRENCY:
“J10:J19” “K10:K19”
“FULL-WEEK”(References 7 Days) = “USD 120.00”
“SATURDAY (DEPART)”(References 2 Days) = “USD 35.00”
“SATURDAY (ARRIVE)”(References 6 Days) = “USD 100.00”
“TUESDAY (DEPART)”(References 5 Days) = “USD 85.00”
“TUESDAY (ARRIVE)”(References 3 Days) = “USD 50.00”
I am trying to establish a way to specifically; select a particular day in the Data Validation drop-down menu (J10:J19) and a formula automatically converting the result to “USD Currency” for each of the 5 alternatives?
So in other words, each data validation cell will have 5 matching formulas pertinent to each specific orientation (Day)?
E.G.Select “FULL WEEK” from the drop-down option and “USD 120.00” is revealed / converted?
Select “SATURDAY (DEPART)” from the drop-down and “USD 35.00” is revealed?
... “SATURDAY (ARRIVE)” = “USD 100.00”
... “TUESDAY (DEPART)” = “USD 85.00”
... “TUESDAY (ARRIVE)” = “USD 50.00”
The closest I have managed (with no real success) is as per the following formula:
IF(T26="FULL_WEEK",X26,IF(T26="TUESDAY_(ARR)",X27,IF(T26="SATURDAY_(ARR)",X28,IF(T26="TUESDAY_(DEP)" ,X29,IF(T26="SATURDAY_(DEP)",X30)))))
I would be so incredibly grateful if somebody could help me (in laymen’s terms)?
I'm looking for a way for Excel to combine formulas together into a more compact form for me.
Attached is a very simple version of what I want, my actual formulas are very complex but I'd still like a way to have them automatically combined into a single cell.
Alternatively, what is a good way to break down a very complicated one-cell formula and still keep things organized?
Here is the one-cell version of a formula I work with.
[Code] .......
That formula is difficult to work with in one cell, so on another sheet I have it broken down into parts so that if one part isn't doing what I want I can change it. This takes up a LOT of space because of the other steps I do (the above formula is just one step) and I'd like to combine it together.
Attached File : Excel Formula Combination Question.xlsx‎
I am trying to make a button that will re-initialize the workbook: clear (delete) all unlocked cell's values. I have the following
View 5 Replies View RelatedI am adding a sheet with a graph I made. There are two sheets, the first with the graph, the second with tables. What I would like to do is populate the graph based off what is entered into the tables. My main goal is to get the cells in the cells in the graph for each day to fill in color between the given time frame entered in on the table. Obviously I would be rounding up or down to make the time end in ":00."
The color I would like to fill the graph with is shown on the graph sheet. Also, if anyone can actually get this to work (I honestly wouldn't know where to start) Is there a way to have multiple colors and a possible overlay of colors?
Lets say on tuesday I have 10:00-15:00, and 12:00-18:00. Can I have two colors populate the cells and a thirs color populate the cells in the overlay which would be 12:00-15:00?
Is there a way to unfix multiple cell references from formulas in a range of cells at once?
View 2 Replies View RelatedProblem-1) i want to round off the digit in the same cell i,e. Formula in F12 should be included in D12.
Problem-2) i have entered a sample account.in this i want to post the data entered in day book into their respective sheets of jaya and supervision automatically.
My Workbook contains several UserForms in which employees will be entering hours and activities on a daily basis. In the UserForm, they select their department and enter hours for each specific type of work/activity that shows up for that department in the userform. They click the submit button and all the data (date, name, department, type of work and hours) is added to a database table in another sheet, which in turn feeds a number of reports.
I need to be able to have anywhere from 20 to 100+ employees enter their hours on a daily basis, many of them at the same time, all from different locations (within the network of the client's system), using the same UserForms.
We've experimented with Google Drive, but that seems not to be the solution. It kind of works, but is not secure (it installs a copy of the entire workbook on the users system and then synchs the data back to the server upon "save") and way too cumbersome. I've heard of SharePoint, but not every client has that installed (and frankly I don't know how it works).
I've already entered a variation of the formula below into 180 different columns. The only variation is where worksheet 322 is referenced. Each column references a different worksheet.
Formula:
I have at least four other tables to build of the same size, and they're each going to use this same formula with an additional IF formula housed around it. Ideally I would be able to copy the table, then run find/replace, where I could substitute = with =IF(new formula, and then run find/replace a second time and sub ))) with ))),more new formula). The problem of course is in between those steps lies a formula error preventing me from running the second step. The only alternative I can think of is to build the new formula, copy it into a word document, and run find/replace 180 times to tailor the formula for each column. That's what I did to build the first table.
I am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:
=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$292
What I want to do is extract the file path from the above formula and make it a composite of several cell references.
So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:
_MONTH =11 November
_YEAR =2013
_JOBCARD ='S:PUBLICProductionJob CardsMOLDING
_PATH =_JOBCARD & _YEAR &"" &_MONTH
I tried several versions, I am hoping for something like this:
=('_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$228*2)+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$262+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$292
In some of my formulas, I need to re-enter the contents of the cell in order for a formula to compute.
For example, I am doing some math on a cell whose original contents are "10:21:00 AM" The formula returns a #NUM!. If I retype 10:21 in the cell and hit enter, the formula will now calculate properly. I have reformatted the entire column several times so that it is a custom hh:mm format. The original content also shows as time so I don't think that it is a formatting issue.
My worksheet has about 20,000 rows and this only happens for about 100 of the rows. It is pretty time consuming to manually go fix each one. Any ideas what is going on here?
Earlier this morning I had a telephone call from a customer who uses a spreadsheet I created.
She explained that once she'd entered information into a cell [and clicked elsewhere] she couldn't then add to the former; rather, she had to click on the cell and type it all out again.
I assumed that, as an inexperienced excel user, she was unware of either double-clicking the cell, or selecting and pressing F2. I explained these methods to her.
She later emailed me with this:
"Further to our phone conv this morning I have just tried double-clicking on cells but it deletes everything in the cell rather than lets you edit what is there – F2 does the same thing. This applies to any cell, whether it is text, date or number."
I've been using excel for a while now, and I've never come across such a thing before.
Has anyone else? Could it be to do with her own excel settings? (other customers have had no such problem)
What I'm trying to do is enter a value/text into a cell - say (A1). After I enter the value/text I would like to have a new tab populated with all the information on the current sheet and the tab name referencing cell A1. I tried running a few different scenarios using macros and have come up short. I want to be able to do this multiple times with different values/text. For example if I enter B606123 into cell A1 I want a tab to be created named B606123. Then later I might want to add B606096 and have that created without effecting B606123.
View 3 Replies View RelatedI want to convert a range of cells to "number" format from text, and then refer to them in a formula that's somewhere else. However, if I do the format from VBA, I can't get the change to be reflected without the following
View 4 Replies View RelatedIn one column (Column A) I have a column of data which contains long descriptive text - i want to extract a specific piece of text from this descriptive text and enter it into the next Column (Column B).
E.g. Cell A1 = "Hello Buddy, how are you?" - i want to extract the word Buddy and enter it into cell B1
then A2 into B2, A3 into B3, etc, etc,...
Is there a simple macro out there that can do this?
i have 5 lists of names which i want the sheet to enter depending on the cell value of C1. C1 is a drop down of 5 different departments so when C1=department 1 for instance i want A4:A25 to automatically update the list of names that i have against that department.
im sure its fairly simply but i just cant get any formula i use to work!
I have been looking for a way to enter the folder where the excel file is located into a cell.
I have seen you can add the whole path (=CELL("filename") ), however I am only wanting the 'last' folder it is in. For Example:
C:docsfolderdataClass D4John WayneProfile.xlsx
Would enter "John Wayne" into cell. The cell is namely the "name" cell.
I work in a school and a teacher is wanting a couple of different excel files for each student (600 students) I am hoping that I can put the files in a folder with the students name as the folder. That way at least its only 600 names and not 600x7! The other details are similar on a lot of the files such as class (this is also one of the folders in the path).
I have worksheet that contain values in column E and G and the total in column K. E and G are unlocked for manual input and column K contains the formula for the total and is locked. From time to time a certain row will not be applicable and before I started locking the cells in column K i would just put "not applicable". Now that it's lock I can't do that. My only option now is to put O in column E and G but I would rather put "not applicable" in column K. Is there a way around this. I was thinking of something along the line of a macro that would unlock the worksheet input the text and re lock it. The macro would have to lunch when a locked cell is selected or have a button that would launch the macro an would prompt for which cell the text would be entered. Is this possible.
View 7 Replies View Related0: i have a sheet 1 protected and few edit range (Col A,B,C,D,E and F).
1: I have a validation on multiple adjacent columns (like Col A, Col C, Col E).
2: I need a code where if i select value in col a (cell A2) cell B2 should get current date and Time and both the cells (A2 and B2) should be locked. when i select data in cell C2 , D2 should get current date and time(Cell C2 and D2 should be protected).
3: User can enter data in A3 and B3 will get current date and Time (now A3 and B3) should be locked.
4: I need a function where user can call it and unlock the locked cells and edit the data and re-lock the cells again.
apart from the Col A,B,C,D,E and F, other columns and cells remain locked.