I have an IF formula on one worksheet that refers to another worksheet (ex: =IF(Page1!$A13="";"";Page1!$A13). The problem is when I move data from one cell to another the formula becomes #REF. (ex: =IF(Page1!#REF="";"";Page1!#REF)).
View 6 RepliesHere is the scenario:
A B C D
BananasApplesOrangesTotal113=A1+B1+C1
I need to reference the formula in D1 with the cells headers names.
In a perfect world, it would take
=A1+B1+C1:
and produce:
=Bananas+Apples+Oranges
I'm trying to do a formula that references a cell and returns a different result dependant on the number in the cell being referenced.
For example I've said if A1 has a 3 in it then put the word TEST as the result, plus if it has a 4 put the word RESULT.
What I wrote as my formula is as follows-
=IF(A1=3,"TEST")+IF(A1=4,"RESULT")
It works fine when I only use one result but goes wrong when I add two. If I change the words I want to show to numbers it comes up fine but with words it just returns a Value error.
I have a spreadsheet with a User From that transfers data from the user form to a sheet each time OK is pressed. On my workstation, the form stays in the middle of the screen. On the PC where it is in production, it moves to the left of screen a little each time OK is pressed until it is hard up against the edge. We recently moved to Excel 2003, and I'm wondering if there is a setting in Excel that contols this.
View 5 Replies View RelatedI have tried numerous versions of macros I found. Most get the same error of; Range object error or script error.
Here is the formula: VLOOKUP(C$13,VESSELS_DATA,ROW(30:30)-12)
What I would like to do:
1) double click to on the cell with the formula
2) goto the worksheet "DATA_TABLES" that contains the Named Range "VESSELS_DATA"
3) go down the rows until the c13 is matched
4) offset column the same as in the above formula "Row(30:30)-12"
I have 2 tables, 1 beneath the other - table 1 columns represent date ranges and their values. Columns and their data are alternating color coding. Table 2 references, by means of array formula, this data - IS it possible to include the font color as a result?
BCDEFGH503/0310/0317/0324/03609/0316/0323/0330/03712348Prod113,91113,69713,58213,4849Prod21,9241,8943,1151,86510Prod31,2601,2401,2301,22121Age (Days)22Prod1211391123Prod22624Prod326
Formula in H22 = '{=SUMPRODUCT(((H$5-$D22)>=$E$5:$O$5)*((H$5-$D22)
I need to create a cell reference which will allow me to specify a cell in a table by value (using indirect) and pull the value from a cell immediately one row over for use in a formula. Say I have a table Containing column A and B; I want to reference the value of a cell in column A (e.g. "Large Widget") and receive (in the formula) the value in column B (e.g. "41 cm^2").
View 4 Replies View RelatedI'm an intern working at an aerospace manufacturing house and I need to automate a lot of the excel files I created last summer.
Anyway, here is my problem:
I've been making macros but I am worried about people moving the worksheets around or changing their names which will have the potential to mess up the macros. What preventive measures can be taken/ changes in the code? I feel like there is something very simple I am missing.
I am trying to design a spreadsheet for planning a project schedule. Down the left are the dates, across the top are the resource names and in the content are the activities being carried out.
Each activity I have is given a two letter prefix, e.g. TD = Training Delivery. I then have conditional formatting applied to each cell to change the cell colour based on the two letter prefix. In all there are six conditional formatting rules.
Now, my problem is that I need this sheet to be really fluid. I want to be able to drag and drop blocks of activities to a buffer area at the side and then drag them into another part of the schedule. However, when you drag and drop (effectively cut and paste) the activities around the conditional formatting moves with the cut content, whereas I want it to remain where it was. I guess what I'm after is a way of dragging and dropping values only.
The best I've come up with so far is an onChange macro that reapplies the conditional formatting to the relevant part of my spreadsheet, but I can't believe this is the most elegant solution. Is there perhaps a way of triggering a macro onPaste?
I have multiple dropdown lists (forms combobox, NOT activex) on a worksheet -- once the user selects them, a keypress on the downarrow will move to the next dropdown list.
is there a way of preventing this? instead, i would like to move focus to the next cell (same column, 1 row beneath the combobox)
I have a worksheet that I will be allowing other to access so that I can get them to input certain data. I will be protecting the sheet to avoid having them inadvertantly overwrite any of my formulae. So, I have a matrix on the left for them to input data into with calculations to the right that they cannot change. Can I prevent them from moving cells within the input matrix
View 1 Replies View Relatedhow can I prevent an entry in a cell even without formula lets say cells B1 , C1 , D 1 , E1
I want to prevent an entry in these cell and if you try an error message will display
I'm creating a maths workbook in Excel. I want the user to workout the answer in his/her head and enter the answer into Excel. The only problem is, of course, that there is nothing stopping a user from simply typing a formula to obtain the answer! I have tried all data validation, apart from Custom which I can't figure out. Is there a way to prevent a user entering a formula on a worksheet?
View 5 Replies View RelatedI've got an issue where Formulas Referencing Cells on another sheet are getting changed automatically to refer the the moved cell data. The formulas on a sheet (2) refer to specific cells on sheet (1) within my workbook. I update Sheet(1) data with a macro. When sheet (1) updates, the present data gets shifted to the right.
In other words, I need to prevent the formulas from changing to reference the moved data values and keep referencing the initial cell with the new values. xample of change that occurs with update:
Correct:
I am in need of 2 validation macros (formulas) to enter into the validation criteria formula box when creating a custom validation.
1. A formula that will generate an error if there is a space in the text entry. Example:060107_Halo3CG prev.mpg would be an invalid entry due to the space after CG.
2. The same formula as above but this one also needs a 50 character limit added to the criteria.
I have a series of worksheets, named, 'sheet1' 'sheet2' etc. through to 'sheet10'. A final worksheet, 'sheet11' has been produced that uses the formula: address=Sheet1!A1, address=Sheet2!A1, address =Sheet3!A1 etc. The same goes for other identical cells on each of the sheets. I am at the moment entering each cell on 'sheet 11' separately because if I copy the formula down it progressively changes the cell value and not the sheet number. How can I produce a formula whereby the cells remain identical but the sheets change progressively?
View 4 Replies View RelatedI have a row C1 - AN1 that has percentages in, these are named per month, ie C1 = Jan, C2 = feb etc.
In another cell M88 I take the average of the next 9 months so that in feb I need from Feb - Oct, in Mar in need Mar - Nov etc, can I get this to change automatically on the 1st of ervery month?
At the moment I have to do this manually but I have a lot of sheets that this is for.
A1 refers to B1 which contains a number or #VALUE!
What could I use in A1 to return a number and if B1 = #VALUE!, a zero.
Here is a google doc of my issue: [url]
An Excel sheet is also attached.
I believe you have permissions to edit.
In H1:H4 I have 4 different functions. When the user specifies which function to use in E1 (add, subtract, multiply, or divide), I would like C1:C4 to evaluate the values in Columns A & B.
My problem is that the formula only uses the values in A1 and B1. When I copy the formula down the column, it still evaluates for the values in A1 and B1, not in A2 and B2, A3 and B3, etc...
I am having difficulty expressing a formula so that if a cell is greater than zero, it adds the value of that cell to a String figure. I have got as far as the following, the 'problem' area is in red.
Sub Share_Sales3()
Dim Prompt As String
Dim Caption As String
[Code].....
How do I input an IF Formula to prevent Zeros appearing in the TOTAL cell ....
Example: TOTAL Cell (A10) =sumA1:A9
ALSO difference between two sums
Example TOTAL Cell (A20) =SumA10-A19
My problem is that when i used the forumla = SUM(Q11:AW11) to add up the range of cells it dosnt do it. It only added up 2 of the numbers in the range of cells. And missed out everything else. This forumla is being used to add up all the volunteer hours for volunteers for each month and for all the other months the formula worked fine but now its just being a pain.
View 8 Replies View RelatedI have attached a sample workbook which shows what I am trying to extract from sheet "Service Reminders 2014". In worksheet "Results" cells b11:e11 I am trying to extract the amount of vehicles with within age ranges provided that have a magic number attached which is pretty easy everything >0 is a proper magic number and also the amount of vehicles in each age range that have "Booked" associated within the range of "Service Reminders" Y2:AH5000, The formula works fine until I add the final criteria and then it give me a #VALUE!
View 14 Replies View RelatedI have a formula that looks up a chart showing scores referenced against each other as shown below. (Scaled down version)
******** ******************** ************************************************************************>Microsoft Excel - Table Template.xls___Running: 11.0 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1B1C1D1E1=
ABCDE12006-2007Altofts JnrsCarleton JnrsEmpire ColtsHemsworth Terriers2Altofts Jnrs- 3Carleton Jnrs12-3-4-0 4Empire Colts - 5Hemsworth Terriers -Results
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
The problem is when the score is i.e. 12-3 the formula is only looking at 2-3. Hence this actually then shows as a defeat. The formula causing this problen is an array and shown as
=SUM(IF(NOT(ISERROR(LEFT(resultsHome,FIND("-",resultsHome)-1))),
--(LEFT(Results!$B3:$K3,FIND("-",Results!$B3:$K3)-1)>MID(Results!$B3:$K3,FIND("-",Results!$B3:$K3)+1,9))))
Question. How can the formula be changed to reflect scores like 12-3 to be shown as a home win.............
.
I have a spreadsheet with a number of different formulae on it. I have a macro which I want to extract which cells are referenced in the formula in the active cell and then branch based on the which cell is referenced.
Is there a way to find the referenced cells without parsing the formula string?
I am trying to reference a date in a formula that points to a sheet name.
See Below.
A B
November 11, 2005 ='11-11-05'!$v$35
How can I automate the date to pull from column A and place that value in the date for column B? Column B is the name of a different sheet in the workbook.
I'm trying to create a list that references an existing data set where I have staff listed month by month and based on today's date, imports only if there is data in that column. I have figured out how to check if the cell is blank or not, but what I want to do now is change the cell that is referenced in the formula based on the date. Here is the format of the spreadsheet I'm working with:
A
B
C
D
E
F
G
H
[code].....
In this case, the "Team 1" and "Team 2" and "Team 3" references what team they are on that month. If it is blank, they aren't with the company any longer. The formula I am using is intended to import this data elsewhere, and is formatted like this:
=IF(ISBLANK(B2), "", A2) - My understanding is that this checks to see if B2 has data, and if it does, it inputs the employee name (A3) in that cell.
My ultimate goal is to be able to change the column referenced after the "isblank" calculation based on the date. So if today is April 2013, I want it to check B2, but if it's December 2013, I want the formula to check J2. Is there a way to do this? I don't mind if it's two steps (like if I have to put the date somewhere in the spreadsheet in order to run the calculation), but ultimately it would be the type of thing I could do that would leverage the existing data set so that I don't have to maintain two different spreadsheets of information.
I am after an automatic formula or function which calculates values for long lists instead of me dragging down the formula all the way to the end of the document.
Hence, I have a long list of data in columns A, B&C and I want the formula in column D to automatically be calculated all the way down when the list stops.
In cell A1 I have a number representing a column and in cell A2 I have a number representing a row. In A3 I wish to obtain the value from the cell referenced by these two numbers.
ie
A1 = 3
A2 = 4
A3 = value from cell C4 (column 3, row 4). How do I write a formula in A3 to look this value up?? I know about the range function in visual basic but I just want to be able to do this using a spreadsheet formula.
If C5 refers to the value in cell C100, C6 refers to the value in C110 ten rows below. C7 refers to C120, ten rows below that and so on. Is there a formula that you use within excel (not a macro) that allows me to copy the C7 formula to C8 so that C8 will reference C130?
I have tried using INDIRECT and OFFSET formulas but cannot do it without an absolute reference to a fixed cell, which defeats the purpose, since I can go into C8 and manually change it to =C130, C9 to =C140 etc.
Currently using Excel 2007 at work and Mac Pro at home. Thought about R1C1 reference, but don't even know how to change to that style on my Mac.