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 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

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.

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.

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.

so this gives a part number depending on what number is placed in O6. What I need to do know is look at 2 different cells and for each combination of numbers give a different part number. so if A1 is 2 and B1 is 3 give a certain result.

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.

Im 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.

I 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".

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 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 am trying to write a formula with some variables passed into it. i want to sum up part of a column based on a date range (i've got the range already).

I am attempting to calculate commission (J2) based on the data entered in cell D2 1. The total fees (H2) is a part of the calculation. It represents a value from .5% to a maximum of 3%.

2. If the Loan Description is specifically 80/20 then 80% of the Loan amount is used in calculating the commission. (note: 80/20* is also a valid entry). Otherwise the total loan amount is used.

D E F G H I J Loan Dsc. Int. DSCNT% Y-S-P% Fees Loan Amount Commission 80/20 7.52.0 1.0 3.0 $137,403 $4,122.09

I attempted this formula and obviously its incorrect:

1. If H2 is blank then TRUE enter a blank 2. FALSE: H2 contains a fee rate then calculate the commission Commission Calculation: If the Loan Description is 80/20 then take 80% of the loan amount and multiply it by the rate fee amount (as a percentage) to get the commission. If the Loan Description is NOT 80/20 then use the whole loan amount in the calculation.