This IF formula does not work on the last part of this formula
IF(E10>50,"$50.00",0)))) and I think there is a conflict with the
IF(E9>199,(E9*0.01) but I cannot figure out what the problem is.
=IF(E8>0,"$0.00",IF(E9199,(E9*0.01),IF(E10>50,"$50.00",0))))
I am want to copy a formula across several work sheet and have the formula always take data from previous work sheet.
2) I am working with this formula =C12+INDIRECT((MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)-1)&"!"&"C12")
and it comes from this thread http://www.excelforum.com/excel-gene...orksheets.html. I have included a worksheet attachment that has explanations
i need to work out a formula for my spreadsheet which I use to work out cutting lists for timber frames. I need it to work out if the width of a job is for eg 2400mm i need to work out how many timber studs I need so the space between each stud is between 400mm and 500mm and this will need to work for a range of different sizes of frames. I have it written at the moment and it just devides the width by 400 and gives me a amount of studs but it would work much better if it could space them between 400 & 500.
View 4 Replies View Relatedcalculate a formula for the following: 1 per 25 for the first 50 and 1 per 50 for the remainder exceeding 50 ad infinitum (always rounding up).
View 6 Replies View RelatedI want to replace a part of a formula with its value.
eg say I have a formula =vlookup($a55,$a$2:$e$50,column(),false).
I want to replace column() with its value. There will be plenty of cells with this formula What can be the best way of achieving this.
I am trying to create my household budget in Excel, but there is a sub-category.
I need a formula which will calculate the following:
Under the "Daily living" section, I have typed the names of the sub-categories, eg: ,
Groceries
General Household
Cleaning
Education/Lessons
Cash
I want it to find all totals next to the word "groceries" in the main part of the spreadsheet, where I type in my credit card statements according to date.
In other words, I want it to add up all my entries/totals next to the word 'groceries' and show the total up at the top of the document, in the "amount" column so I can see the monthly category totals at a glance.
I am trying to modify a cell formula with the Replace function.
Code:
The Formula in the target cell =
='C:Documents and SettingsHoliday macro[Holiday2011.xls]Year 2011'!C5
I am using the below code but it returns just the end portion ='Year 2012'!C5
where I expect just the year to change
='C:Documents and SettingsHoliday macro[Holiday2012.xls]Year 2012'!C5
Where last year = 2011
Yr=2012
Range("C3").Formula = Replace(Range("C3").Formula, LastYr, Yr)
I have a dump which looks like below sample:
3
3
2
2
3
3
2
2
[code]....
The actual dump will have around 200 - 500 rows of data.
From the above I need to manually group them which are similar.
E.g. the data of (3, 3, 2 , 2) in row 1 & 2 are matching, so they will be group 1 & row 3 will be group 2, row 4 will be group 3 respectively.
The sum & sort did not work for me as sometimes the data with the same range are not in same order.
E.g of the output below for the above data:
Group No.
1
3
3
2
2
1
3
3
2
2
[code]....
I have the following formula in my workbook;
=SUMPRODUCT((A2:A8702"")/COUNTIF(A2:A8702,A2:A8702&""))
The data in the workbook is imported automatically and the range 'A8702' is different every time the data is imported, although is always in column A.
Can I use the value of a cell to determine the range?
So I have used this to get the ast used row number;
=MAX(ROW(A:A)*(A:A""))
I need a cell formula (gets put in by macro) that returns part of a workbook name.
The worksheet name will always be different, but will contain either
SD1, SD4, BDO, BD (not neccessarily in Caps)
and it is this key code that i want returned in the cell (G2).
so if the worksheet name is
NW SD1 bobs your uncle. when macro runs G2 returns SD1
or
BDO xyz. when macro runs G2 returns BDO
I need to extract the part of formula in a cell. How can I do that?
For instance; I have formula: =C2/3579 in cell E2
I want to copy the last part in that formula i.e. 3579 to some other cell.
The last part (3579) is not constant in every report. It may vary in each report thus the length may vary from 4 digits to 5 digits also.
I'll try to keep this brief. Our part #'s look like this. XP014567. Where the first pair of characters stand for a value(say XP=Bolt), the second pair stand for a value(01= Grade 2), and so on. What I would like to be able to do is type in the part numer and have the description automatically populate. Is there an easy way to do this? I'm running Excel 2003.
View 4 Replies View RelatedIm using a formula to identify new part numbers. The formula is: =IF(ISNUMBER(MATCH(A217,Existing!A:A,0)),"","NEW"). However you can clearly see from the attached that if has flagged a duplicate part number as new. Why would it do that? Check out A1368 in existing and A217 in new.
View 5 Replies View RelatedI have 2 sheets, Sheet1 and Sheet2. I want to populate Sheet2 Column B with a formula which will reference Column B of sheet1 so that on each iteration of the For..Next, the cell being referenced in the formula will change. So if i = 5, I'd like the cell to be populated with the formula =Sheet1!B5
View 4 Replies View RelatedI have this function that checks a postcode and returns a value using the first part of the postcode.
What i would like to know is how to modify the function so if there is only the first part of a postcode already entered, it looks for that.
At the moment sometimes i get #N/A even though i know that value is in there.
Code:
=INDEX(U$4:U$1068,LOOKUP(1068,MATCH(IF({0,1},LEFT(B338,FIND(" ",B338)-1),LEFT(B338,1+ISERR(MID(B338,2,1)+0))),T$4:T$1068,0)))
I'm trying to create a macro to replace a link on a formula.
The formula is as below
='[Players Report 31121999.xls]Player1'!$AK$23
Where 31121999 is the date. there's a workbook everyday with different sheets for every player
I want to have a macro that gets the date I inputed in another cell and changes the date in the formula I displayed above (just changing the date).
I've got a problem with my workbook.
It gets datas from another workbooks and contains cells with formulas like these:
='C:YEAR[2006-ab.xls]'!A12
='C:YEAR[2006-bc.xls]'!C18
et.c.
Is it possible to write a code in VBA to replace a part of formulas above?
Is there a way to get the "then" part of the if formula to be another formula? I have the following formula which isn't working.
View 2 Replies View RelatedI have a spreadsheet serving as a simple document register. Column A is a unique document reference number, which is a simple concatenation of some text which prefixes all of the document numbers, plus the date in "yyymmdd" format (from column B) and a 3-digit sequence number (from column C).
Column B has no formula - my macro just pastes a timestamp in there.
Column C is a sequence number field which has a formula comparing the "yyymmdd" part of the timestamp with the cell directly above it. If they are different then the sequence number becomes "001" - if they are the same then the sequence number is incremented by 1, so all docs created on the same day have an incremental sequence number.
Here's the problem. When I want to add a new document I need to copy the last row down to the next row (in preparation for the next document to be created by my system) and there are two ways I've been doing this - one which works and one which doesn't.
This works > Say my last row (with the hidden formulae) is row 13. I select A13:C13 plus the three blank cells directly below (A14:C14) and do a Ctrl+D to copy them down. All is well.
But the following doesn't work - why? > I select the entire row 13, copy it, then reinsert it at row 13 so the formulae in row 13 get shifted down to row 14. What goes wrong here is that my formula in column C which WAS comparing the date in B13 to the one in B12 NOW compares B13 to B11
I've attached an example spreadsheet to try and demonstrate the problem. Sheet 1 is the original sheet. Sheet 2 is the "copying down" method which works, and Sheet 3 shows what happens when you copy the entire row 13 and "Insert copied cells".
excel-insertrow-formula-problem.xls
I need to pull information from a cell that is full of text and numbers and get it to creat a formular.
More information in the spreadsheet example
Is there anyway of aligning part of a formula to a certain character limit (63)?
For example:
Aaaaaa(A1), bbbbbbb(B1), 1111111.00(C1)
Aaaa(A2), bbbb(B2), 11.00(C2)
to
Aaaaaa(A1), bbbbbbb(B1),____________1111111.00(C2)
Aaaa(A2), bbbb(B2),______________________11.00(C2)
I have a formula that combines the the text of various cells.
Is there a way to automatically make part of the cell Bold - so in this case I want the Cell B13 to appear in bold.
See formula below:
I have this If statement on one of the cells
=IF(A7=AQ6,AQ32,IF(A7=AR6,AQ32,IF(A7=AS6,AQ32,IF(A7=AT6,AQ30,IF(A7=AU6,AQ29,IF(A7=AV6,AQ33,IF(A7=AW6 ,AQ33,IF(A7=AX6,AQ31,""))))))))
If I want to change only A7 to A8, A9, A10 without changing the other parts of the formula, how do I do that. I need to copy this formula in about 300 rows.
I've run into a roadblock in excel 2003 in trying to create an accounting spreadsheet that will pull in information from several separate workbooks (invoice files) using formulas which substitute invoice Nos. (which coincide with the tab/worksheet names).
My obstacle has something to do with order of operation/calculation I presume... I've successfully used labels to bring in information from separate tabs on the same file by using the substitute function with generic formulas.... in this case exel processes the formula, likely because it calculates the linked information first in its order of calculation; then trys to substitute information into the result of a calculation already made....
I'm trying to find a way to tell excel to look at respective cells in the same file but on a different tabs/sheets...
For example; I've been trying to pull in the date an invoice was issued for client "X".
I plug in a formula in a remote cell of my accounting spreadsheet "Y" which basically says to bring in the value on file ["X"]; worksheet "tab"; cell $J$11 - where "tab is just a filler that I would like to use the substitute function to exchange with respective invoice Nos. (i.e. 049; 050; 051; etc.) My hope is that by adding these 3-digit sheet identifiers; all other information from respective cells (in file "X") will be pulled into my accounting sheet - file "Y" (i.e. invoice total; applicable pst; gst; expenses; etc.) I have written as many formulas as required for the different types of info sought after, the only variable being the 3-digit worksheet identifier. The problem has been that excel first processes the formula in the remote formula cell(in file "Y"); then applies the substitute function to info already brought in from File "X"; whether it is a date value; or a currency value. I can't figure how to have the substitute function change the formula... not the result of the original function...!
I've tried to format the source formula cell as text (therefore not calculating a result at that instant) however the substitute function inherits the format of the source formula cell.
I have a formula in cell J2:
='H:Folder[CI10001G.xlsm]Sheet'!$C$5
I need a macro that will change the part of the formula that is "CI10001G" to the value of cell A2 which would basically be something along the lines of "CI10004D" so the formula would change to the following:
='H:Folder[CI10004D.xlsm]Sheet'!$C$5
And then this formula will then successfully look in the newly directly file path.
I was given this Formula to use in a cell to calculate the total hours from 2 different cells, from inputting date and time from and to dates..as an example from 4/1/14 0:00 thru 4/1/14 12:00.....The problem I am encountering is that when the spreadsheet is 1st opened "+1" is visible in the cell where this formula is. I am asking how the formula can be altered to have the "+1" not visible in the cell before any date and time is entered.
= INT(F13-D13)*24+(((F13-D13)-INT(F13-D13))/0.04166666)+1
I have a spreadsheet containing quite a few lookups etc. I am trying to copy a cell across about 10 columns. This has to be done for about 50 different rows on about 20 different sheets, so I am looking for an alteration to the formula to help me rather than typing in the formula over and over;
='Basic Labour'!AD6*Rates!$E$526
Say this is in AD6, then in AE6 I would require
='Basic Labour'!AE6*Rates!$E$527
and so on across the region to be autofilled...
(It is multiplying the same cell in a different sheet against the 'next row down' in a rates lookup sheet).
I want to go ("E:E") and if part of the cell contains "WAL-MART" than put the word "Food" in cell G of the same row and contunie until end
View 1 Replies View RelatedI have some problem with my excel formula here. It works in some rows but doesn't in others.
I have attached the excel file herewith : Book1.xlsx
See the attached workbook.
Please edit the formula in column B so that it only gives a BINGO if the adjacent cell in column G is less than 51.
So cell M16 should not be a BINGO because cell G16 is not less than 51.
Attached Files : Book1.xlsx