I've created a user form to enter some values in a few adjacent cells. When the user clicks on a button, I want several other cells to be loaded with special formulas that refer to the new values. This happens on a row by row basis. First, a working formula in that cell which is AB5 is:
Code:
=IFERROR((IF(AK5
Say I have this data in worksheet
A
B
C
D
E
14/1/2012
1
2
3
4
15/1/2012
5
6
7
8
And this in worksheet 2
=A2
=B2
=C2
=D2
=E2
=A2
=C2
=D2
=E2
=B2
=A2
=D2
=E2
=B2
=C2
Now what I want to be able to do is when I add a new row to worksheet 1, in this case row 3, then the references in worksheet 2 either to automatically (or manually do it in one simple go) increase by one and now refer to the new row. So the references now become =A3, =B3, =C3 etc.
I have an excel sheet which is protected (mainly so that the users will not change any formulas) the excel sheet is basically a list
Just to put you in the picture
Column B Is PO Number
Column C Is Remarks
Column D Date
Column E Description
Column F Supplier
Column G Amount
I would like that when Column C changes to "OK Checked" column B C D E F G are protected and locked. But obviously for that particular row.
For example the input of Cell C15 changes to "OK Checked" then B15, C15,E15,F15,G15.
the macro must NOT be fixed to a particular row (in this case 15)
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 am running a research experiment in triplicate. Upon generation of data for all three, I create an averaged value of the three. I then take this value and do more calculations with it on a separate sheet. I wanted to know if there was a way in which I could quickly generate formulas based on a changing reference cell.
Example Data:
V1A 28
V2A 26
v3A 30
AvA 28
V1B 20
V2B 22
V3B 18
AvB 20
Vxy represents the value of y sample, x triplicate. V1A being sample A, first triplicate. AvX is the average of the three values.
I want to be able to generate a formula that references AvA which can be applied to the cell immediately below to reference AvB. A simple example would be to square the average.
I currently have a spreadsheet that refers to a different spreadsheet, with different expenses each month, with the months going across from A,B,C,D etc.
Because the formulae on the expenses sheet refers to cells such as O13 O15 O27 etc, at the start of the next month the 'O' has to be changed to a 'P' to give P13 P15 P27
This doesnt sound much but there are over 10 different spreadsheets that are all currently changed manually. Is it possible to have a function referring to a cell containing 'O' which when changed to 'P' changes all the formulae to refer to the cell next to the original.
EG Instead of O13 it would be [variable]13
Essentially this will mean that a master sheet will be able to change all the excel spreadsheets in one go.
i need a macro which puts the formulas into the cells as per attached.
the number of rows between the "beam" can be variable
the number next to the beam will be variable.
the number of "beam" rows is vaiable
my thoughts where to do a find"beam" and refernce the cells address the create formulas off those points but dont have the skill to code this
beam macro.xlsx
Is it possible to write a formula that skips x number of cells and have the formula autoincrement as usual. For an examples lets say I want to skip 3 cells and I have entered in the cells listed:
A1= "1"
A2= "2"
A3= "3"
Then I type in the simple formula in the cell
B1= "=A1"
and then drag down but it skips 3 cells so then
B5= "=A2"
B9= "=A3"
If it didn't skip then
B1= "=A1"
B2= "=A2"
B3= "=A3"
this is just an example as I will be writing more complex formulas and need some thing that I can write into the formula so that it skips.
Something similar to this B1 "=A1(skip3cells)"
I have 5 columns...I would like a formula to say if number in one cell and none in the other 4 cells to say "yes" otherwise say "no"
View 9 Replies View RelatedI am creating a fantasy football draft board using excel. To keep it short and sweet, each individual team (one per column) has a $200 budget to draft players. Each team must fill 14 roster positions (one per row), but here's the catch; you must spend at least $1 on all of the 14 players on your roster. So you have a maximum bid amount which is your remaining budget subtracted by $1 for every remaining spot you have left to fill.
I have already created a "remaining budget" field which subtracts each drafted player's inserted dollar amounts from the $200... easy. Beneath this, I'd like to create another cell per team that tells you your "maximum bid," which is essentially 200, -1 for every cell that remains blank. I have played around with the conditional IF formulas but cannot seem to have it account for multiple cells.
As a workaround, I have made columns hidden beneath the board which contain the conditional IF, ISBLANK fields which are -1 if true and 0 if false. Then creating the "remaining budget" field that subtracts these values from 200. This works, but it's not perfect, as when you have multiple players you still need to draft and you've exhausted your budget only saving $1 for each, it overstates your "maximum bid" by $1, or when you have $X left and you need to only fill one more spot, it ill tell you your "maximum bid" is $X-1.
I'm trying to create a "master file" that can be used later (a tool other uses can pull up) on with datasets of varying size. I'd like to "data dump" a table of individuals with subsequent columns of various characteristics, then automatically compute various things. So then a user can dump their data into my file and "get the answer" immediately. I've seen some stuff on running one calculation on a dynamic number of cells, but let me illustrate why this is different. I want to perform a dynamic number of calculations, see below:
Each row (record) is a person, with some characteristics (numerical). Ultimately I have a few columns on the end with some computations (Excel formulas) I'd like to do. I'd like to do this for each person (that is, each row) in that data that I dump in. But the thing is, ahead of time we don't know how many people will be dumped in, and that changes (because I'd like this to be a tool...). So how do I tell Excel to compute things if there is a person in that row, but do nothing and leave cells blank if there isn't a person? Ideally I don't have millions of ifs testing for blank cells either. I'd like to do this with Excel functions only and not VBA, if at all possible.
I have cells C22:C27 with a number that could change each week. How can I get a total of the number of cells that = 0,1,3,4,5,6,7,8,9,10,11,12?
View 3 Replies View RelatedI need to formula to count the number of cells that meet the criteria below,
Find the letters 'AT' in some part of the cell and a blank cell next to it...
Doing a different formula for each, doesn't bring out the correct figure.
Formula to pull the lowest number that falls in Cells I2:L2 and the number must be 0 or Greater. Some of the numbers might be negatives and I am trying not to pull those numbers.
View 3 Replies View RelatedI have a form in work which acts as a tick sheet for tasks complete on a construction site. When a task is complete I copy & paste from a key of dates i.e. week ending 11.05.15, this allows me to see what week a task was complete.
Generally I only marked off tasks which were 100% complete but my manager wants me to enter the % of the task complete also, i.e. 80% in the box.
So my question is how can I have the formula assume that any box with a date & shading is 100% and any cell with a number i.e. 80 is only 80% complete. I need it to monitor around 150 cells and give me the overall % complete based on what i have discussed.
I'm looking for the easiest way to count the number of occurrences within a cell range.
The formula that I'm currently using is:
=COUNTIF(D$5:D$8,"a*")
This counts the number of cells that start with 'a' and returns the sum. It seems to work fine, but when I try to make it look for more values in the range it gives me an error. For example;
When I want to find multiple values in the range and count them all, I use this formula:
=COUNTIF(D$5:D$8,OR("a*","b*","c*"))
How can I shift the range of cells I want to calculate an average from? For example I want to get the average of cells A1 to A10, so =AVERAGE(A1:A10) And next I want to get the average of cells A11 to A20, so =AVERAGE(A11:A20). But I don't want to manually change the data or delete rows all the time.Is it possible to do something like: =average(A1+10:A10+10)??
View 4 Replies View RelatedI got the following loop that is very slow. The main way (I think) to speed it up is to avoid looking at all the 65000 cells. The columns used in the calculations have data varying from a few hundred cells up to (rarely) dozens of thousands. I assume it would be better if I could replace the 65500 with something indicating the "end" of the data in the column. Also, is there another way to do this process without using a loop?
I am trying to fill in a number of rows with array formula's in the adjucent cells. I use the loop because the number of rows depends on a selection of another parameter which sets the row number before calling the loop.
I am wondering if I could have a formula be used against a certain row/cell containing a certain "phrase" or "number" for instance
I want E1 to read something like this = (row containing item "FF32105") (the given row from the previous statement) ( the input column ex. B) (the input value ex. *6)
Essential I want to be able to copy and past a sales forecast and have the formulas automatically (listed below the pasted forecast) calculate purchase needs. The issue is that if our forecast one month has a certain item and then the next does not then they will end up on different lines after the copy/paste. We have over 500 items so a manual adjustment would be time consuming..
Is there a possible way for the formulas to "find" the correct line to start calculating data?
I am creating a spreadsheet which creates a bulk of data from a front sheet.
The question is:
Is there a way to automatically copy a fixed formula for 100 cells using one fixed cell reference such as $A$1 and then automatically after 100 cells replace $A$1 with $A$2, after another 100 with $A$3 and so on?
Explanation with Example:
For example, Sheet1 contains the words "Green Tree" in Cell A1 and Sheet2 will then place "Green Tree" into 100 different sentences such as:
Plant a Green Tree
Grow a Green Tree today
This would be created with the formula ="Grow a "&'Sheet1'!$A$1& " today"
After 100 different variations using the formula I want to change that formula to reference cell A2 on Sheet1.
I know if I place "Red Tree" in Cell A2 and use the formula ="Grow a "&'Sheet1'!$A$2& " today" I can do this manually using find and replace for the 100 cells, but I want to do this for 100 different variations of Green Tree to create a 10,000 different sentences so I'd need to find and replace 100 times!
Having trouble getting my head around this one...
i want to enter hh.mm eg. 3.30 and then use it for sums...
I have 1200 doctor/patient records to input into an excel spreadsheet for import to an online EHR database. I can set up all the normal formulas and formatting but for the life of me not figure out how to create a custom formula to take the first letter of the patient first name and last name and add 6 figures to create a unique patient identifier.
ie. James + Smith+ random 6 figures = JS245318.
In my spreadsheet the first name is under Column 1, Last name Column 3 and the unique number generated in column 4.
I have a range of 30 cells in a row. From that row there are groups of cells that have data that might be 1-7 cells populated in a row and in between these groups are blank cells in the row. I need to see what is the highest number of cells in a continuous row that contain data, what the mode is for continuous cell counts, and what is the average value for the data from the continuous cells. The average is a nice to have but not entirely necessary as most cell data will be fairly similar and I can get a close enough average just by knowing the average number of cells that contain data in a row. I have to do this calculation for about 1000 rows of individual data so its important a formula fit in one row.
Almost need a count function to count starting with each cell and then stopping at the first no no value it finds. Then it would show those counts and I could run a formula to pick the highest count and also show the mode of counts.
I will let the experts at it!, my weak mind could not think this one through. I thought something to do with MATCH for a while but no go from what I could do.
I am looking for a formula or something - that when a reference number is used - it popluates cells from a list. Attached is a sample spreadsheet - 2 worksheets are being used - 1 is Purchase List and the 2nd is Fax Commitment. When reference no is filled in on the Fax Commitment sheet and it = the same reference no as on the Purchase List - I need it to populate the appropriate fields (in this case I have colour coded)
View 3 Replies View RelatedPlease take a look at the attached spreadsheet. in the ODDS column D, after download the some of the data has been changed from say 100-1 to a series of numbers how do i change them back i would also like to show the odds is they were 100-1 as just 100 and say if the odds were 13/2 then i want to show 7.5 basically i want th first number divided by the second.
View 12 Replies View RelatedCode be to change the number format of the range H21:H37 on my attached spreadsheet from the currency format with the $ symbol and 2 decimal places whenever cell H20 displays "UNIT PRICE" to a number utilizing the comma seperator and with no decimal places and no $ symbol whenever the cell H20 displays "SHIPPED"?
In the following sheet I'm tracking daily numbers against a monthly total. In the cell E4 for example I have a minimum per day number needed which is based on the monthly goal divided by the number of days availble to work. What I would like to be able to do is have the number auto-adjust if a letter (i.e. V=Vacation, S=Sick, etc...) is used in place of a number on any given day.
I want total in cell A3 based on the month number in A1, example given below.
2nd example if I put 5 in A1 the total should be 2500 in cell A3.
3ResultJanFebMarAprMayJun9002003004001000600800
I want to write down the number (149727273)
To become (1.49727273)
appending excel sheet number in a formula. Here is the example.
In an excel book, I have sheets 1 to 50. In each sheet i have a standard format to enter the details. And in one sheet I want to keep some summary with required cells. I made a table by applying formula, for example in cell A1 i kept formula ='9'!$D$9. In this '9' refers sheet9. In the similar way i want to capture the data from all sheets.
A1 cell ='9'!$D$9
A2 cell ='10'!$D$9
A3 cell ='11'!$D$9....etc
Is there any formula to append the Sheet number automatically (like dragging the shell) intead changing sheet number manually. because it is wasting so much time.