Need some formula advice for two part problemI hope someone can help me out with this problem ?
I want to place a date in a block when another block reads 100%.
say block B4 reads 100%, I want to place a date in D4 to represent when
(Days360) that block (B4) reached 100%.
Hope someone can help, thanks.
Mr. Goodbred
Benjamin Goodbred,
I came up with this solution:
Format cell B4 as percent, D4 as Date 3/14/01.
In cell D4 type formula:
"=IF(B4=100%,NOW(),"")" do not add the first " or the last "
The cell D4 will remain empty("") until B4 is equal to 100%.
hth
"BENJAMIN GOODBRED" w...
repeat formulae taking into account previous column's info?Using Excel 2003. Trying to add up home accounts. Want to copy formulae from
one column to another. Can Excel do this automatically? eg =SUM(B20+C16)
to =SUM(C20+D16) to =SUM(D20+E16) where B, C and D are months, line 16
is the sub total and 20 is the total that I would like to carry through to
the next month. Do I have to keep inputting =SUM etc, or is there someway of
replicating this formula when the computer can change the letter of the
column automatically? Help!
Hi Debbie
your formula of =SUM(B20+C16) can be corrected to either
=SUM(B20,C16)
or, better still
=B20+C...
Creating a conditional formula to increment #s in a test scriptGreetings,
I have an Excel question. I am creating a test script template for our team
and I an in need of some formula help. Here is what I wish to do:
Let's say I have a column A in my test script. I wish to label this column
with my test script #
TC1
TC2
TC3...(and so on)
However, the problem is I have section breaks in my test script that divides
the document into sections: eg..
<LOGIN SECTION>
TC1
TC2
TC3
<LOGOUT SECTION>
TC4
TC5
TC6
Now the problem is that if I want to add a new test case to the "Login
section" above, I need ...
Linking to files with a formulaIn Excel 2003 what is the trick to linking to a closed work sheet using a
formula?
I'd like to put a list of file names in column A then use a formula to ling
to cells from those books.
Straight link works ='C:\My Docs\[File_1.xls]Sheet1'!X1
Formula does not ='C:\My Docs\[&A4&]Sheet1'!X1
Indirect will not work because I have too many books.
Grateful for any help on this one!
Thanks!
xjvs
Hi JVS
Maybe you build your formulas with a macro
I use getopenfilename here but it is also possible to loop through the column with file names
http://www.rondebruin.nl/s...
Want to Change Range Selected in FormulaI would like to pass a variable through a function. Based on the value, I
have some ranges already name ie. Dataquestion#. I would like to be able to
change the named range to use in this formula based on numbers, such as mQNo.
Any help would be appreciated.
mQNo = 1
Set mQuestionRange1 = Worksheets("Data").Range(Chr(34) & "DataQuestion" &
mQNo & Chr(34))
This is siomple. I don't think you understadn strings. You don't need
a second set of double quotes. It is that simple
for example
Var1 = "abc"
Var2 = "def&q...
Enter Formula without using =I would like to be able to enter a formula into a cell and have it not
require me to first type the equals sign.
For example, when I type
12+12
I would like for the cell to return
24
and when I edit the cell, it would show
=12+12
(e.g. Excel automatically inserts the = before the formula)
Does anyone know how to do this?
Tools>options>transition and select transition formula entry
--
Regards,
Peo Sjoblom
"Brian Tankersley" <bftcpa@gmail.com> wrote in message
news:6A70362C-6A75-4C5B-A50A-ECA311B48DA4@microsoft.com...
>I would like to be able to en...
Inter sheet formula won't format properlyHi
MS Excel 2003.
I am writing a formula with a reference to a cell in another sheet, and
Excel is behaving oddly. Sometimes it will give me the result, ie what
is in the target cell, but sometimes it gives me the formula.
The formula I am writing is simply
='2'!A22
thereby trying to get cell A22 from the sheet called 2.
Why is Excel not always just giving me what's in the target cell?
Sometimes all I can see is ='2'!A22. It's driving me nuts.
I have looked at all the formatting options with no luck; I have toggled
(using Ctrl`) the formula view with the norma...
Need help with formula #2I am trying to build a formula that will find all instances of a
particular word in one in column then add the corresponding number that
is in another column for a total of that particular word.
ie: column A column B
sp 4.00
hol 4.00
sp 8.00
vac 6.00
I want to total all instances of "sp"
Thanks for the help
---
Message posted from http://www.ExcelForum.com/
Hi,
Try the countif function to count the instances of the word you are looking
for..
=COUNTIF(range,criteria), put ...
Date FormulasI am trying to find a way to set a start and finish date based on other tasks.
I have 3 design tasks 18, 35 and 40. I would like to create a single
"milestone" task that looks at these tasks and determines what the first
start date is and what the last finish date is?
For example:
Task 18 has a start date of 4/1/2010 and a Finish date of 4/30/2010
Task 35 has a start date of 4/15/2010 and a Finish date of 5/15/2010
Task 40 has a start date of 4/23/2010 and a Finish date of 5/29/2010.
I would like to create a task called "Milestone - Design" and look ...
Formula to maintain 1st day of monthWhat is the formula to make a cell always post the 1st day of the month.
For instance.. Its July and cell a1 has 07/01/2006. when it becomes August I
want that cell to auto change to 08/01/2006.
=date(year(today()),month(today()),1)
"Rheyjune" <Rheyjune@discussions.microsoft.com> ���g��l��s�D:9A4CFF6B-D053-44F4-89D5-F1F85D010AFB@microsoft.com...
> What is the formula to make a cell always post the 1st day of the month.
> For instance.. Its July and cell a1 has 07/01/2006. when it becomes August
> I
> want that cell to auto change to 08/01/2006.
Thanks
bobo...
Please help with formula #2It has been a long time since I've needed to write for help, but I need
it now. I had a friend come to me for help with a spreadsheet he's
creating for the food service company he works for. While I taught
advanced Excel in college, that was four years ago, and I don't recall
this particular issue.
The first worksheet is a payment summary sheet. Next, there are sheets
for (invoices) for each organization the food service company deals
with. On each invoice sheet, there are cells which identify information
common to each of these company sheets. All of the invoice sheets are
t...
Don't know which formula to use!If I have several sheets in a workbook, and I want one as the master to refer to all others so that when a number is placed in a cell on the master it is either subtracted or added from the same cell in another sheet, depending which sheet or sheets are named from the master sheet. Is this possible?
Could you explain what you mean in a bit more detail?
For instance, will the entry on the Master sheet be made in the same
cell each time? If not, will it be the same cell for each named sheet?
How will the name in the master sheet correspond to the value- to the
left? right? above? below? Ho...
Pivot Table FormulaHi,
Does someone can help on this please.
In the same field I have 4 different Items (4 columns in the Pivot table)
SalesA, SalesB, Refusal, Pending
I need to add SalesA with SalesB
I tried the Formulas "Calculated Item", however I get a message:
Multiple data fields of the same field are not supported when a Pivot table
report has calculated Items.
How can I fix that.
Thanks
...
Need to print formulasHello all! Using E02 on XP. Have linked cells in OutFile.xls 'reading' data
from InFile.xls. Rather than handwrite my cell mapping, how can I print the
linked cells or the formulas?
Example: InFile has:
A B C
1 10.45 9.32 3.25
2 9.87 5.98 5.32
3 12.87 10.11 7.42
OutFile has:
D L
V
10 ='S:\[InFile.xls]P4'!A1 ='S:\[InFile.xls]P4'!B1
='S:\[InFile.xls]P4&...
Data row wise, formula column wiseI have data in cells b2:aj2
I want to refer to these cells in a column from a2 down. I can manually type in:
A2: =B2
A3: =C2
A4: =D2
Rather than doing so, is there a formula I can type in once in A2 then copy
down?
--
Regards,
Fred
If you just need the data in column A........
Select B2:AJ2 and copy.
Select A2 and Paste Special>Transpose>OK>Esc
Gord Dibben Excel MVP
On Wed, 7 Dec 2005 17:18:55 -0600, "Fred Smith" <fredsmith99@yahoo.com> wrote:
>I have data in cells b2:aj2
>
>I want to refer to these cells in a column from a2 down. I can manuall...
wire transfershello,
What is the best method in recording a wire transfer for Accounts Payable
payments in Great Plains.
Thanks,
Scott
We record them as regular payment transactions in AP and note in the document
number that it is a wire payment. Don't know of any other way to handle
these.
--
Jim@TurboChef
"Scott" wrote:
> hello,
>
> What is the best method in recording a wire transfer for Accounts Payable
> payments in Great Plains.
>
> Thanks,
> Scott
>
Thanks for the info, do you print the check.
"Jim@TurboChef" wrote:
> We record th...
old file transfersI have an old system running on 3.1, with business files on it I want to
transferee these files to my other computer , it has xp system running.
is there a patch I can download to my xp system which will let me open old
excel documents
If your xp computer has Excel installed, you should be able to open the
old Excel file.
If not, you can download an Excel Viewer from the Microsoft web site,
that allows you to view and print files:
http://www.microsoft.com/downloads/details.aspx?FamilyID=4EB83149-91DA-4110-8595-4A960D3E1C7C&displaylang=EN
J B wrote:
> I have an old system ru...
help me with excel formula pleaseA B can somebody help with a formula in the B colunm so
that it
1 1 give values added upwards in the A colunm
2 (1+2)
3 (1+2+3)
4 ( 1+2+3+4)
advance thanks
In B1 enter:
=A1
In B2 enter:
=B1+A2 and copy down
--
Gary''s Student - gsnu200903
"shaanu" wrote:
> A B can somebody help with a formula in the B colunm so
> that it
> 1 1 give values added upwards in the A colunm
> 2 (1+2)
> 3 (1+2+3)
> 4 ( 1+2+3+4)
> ...
UDF to Convert formula results to textCan a UDF be used to convert formula results from column A to text in
column B?
I know that I can manually cut and paste "special" to achieve this, but I
want to avoid a manual step.
Instead of a UDF use a macro. Change columns(2) to columns(1) to change the
column withOUT the need for an additional column.
Sub converttovalues()
Columns(2).Value = Columns(1).Value
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Don" <don87109@hotmail.com> wrote in message
news:%230kPQ8gDKHA.4608@TK2MSFTNGP02.phx.gbl...
> Can a UD...
Beginner(formulas?)
Hey..im doing this add math project of mine and im really new to
microsoft excel.
Lets say i have the numbers 1 - 10 in column A, and i have to count
them using the sum 4.2+2X (X representing the respective numbers in A).
WHat formula do i have to use? and how do i duplicate the same formula
for each of these numbers.
eg:
A B
1 4.2+2(1) = 6.2
2 8.2
3 10.2
4 12.2
5 14.2
6 16.2
7 18.2
8 20.2
9 22.2
10 24.2
the thing is, the question requires me to do the same thing for a
hundred numbers..and its a tougher sum..so i need help..thanx
--
nomad31
--------------------------...
time formula questionThis formula works great if the ending time is before 0:00.
=IF(D21>0,B21/INDIRECT("Sheet1!E"&MAX((A21>=$C$9:$C$11)*(A21<=$E$9:$E$11)*
{2;3;4})),"")
$C$9:$C$11 is my starting time i.e. 20:00
$E$9:$E$11 is my ending time i.e. 04:30
How can I get this to work if A21 = 20:15?
TIA,
David
Try this:
=IF(D21>0,B21/INDIRECT("Sheet1!E"&MAX((($C$9:$C$11<$E$9:$E$11)*(A21>=$C$9:$C$11)*(A21<=$E$9:$E$11)+(($C$9:$C$11>=$E$9:$E$11)*((A21>=$C$9:$C$11)+(A21<=$E$9:$E$11)))*{2;3;4})),"")
HTH
--
AP
"David" <dfizer@r...
Help with Formula Hi,
I'm trying to count dates in a range in another worksheet (Test 1),
count is to be based on being euqal (=) to or less than (>) the date
that is entered in B10. If there is no date in B10 I want the cell to
remain blank.
The data in 'Tests 1'!AD3:AD1000 is entered and formatted as a date as
is the data in B10
I am using:
=IF(B10="","",(COUNTIF('Tests 1'!AD3:AD1000,"=>B10")))
this only returns 0
Suggestions welcome.
--
Steve
Reality is the leading cause of stress amongst those in touch with it.
- Jane Wagner
=IF(B10=&quo...
I have a formula in my workbook that was functioning but it doesn.I have a simple formula that adds amounts. It was working all the time but
now suddenly it's not adding on, i.e. the formula is still in the field but
the result is not coming......
Can somebody help?
Hi Ivan
it could be that your numbers are formatted as text - copy a blank cell from
another worksheet
select your numbers and the formula and choose edit / paste special - add
then select the formula cell press F2 and then enter
alternatively, check that calculation is not set to manual - tools / options
/ calculation - ensure that automatic is selected
Cheers
JulieD
"Ivan&qu...
help with a formula #5I have 98 sets of magazines each set has 12 in the set
what formula can I use to get a total of issues in all sets
have you tried 98*12
--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Just Me" <no@isp.com> wrote in message
news:eZ66ZEGHGHA.1192@TK2MSFTNGP11.phx.gbl...
>I have 98 sets of magazines each set has 12 in the set
>
> what formula can I use to get a total of issues in all sets
>
=98*12
or
=A1*A2
if the numbers are in A1 and A2
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"Just Me" <n...
Data Transfer Issues with remote siteDear Developers,
I am hoping one of you may have some suggestions on what may be causing this
issue and provide some suggestions on how to reduce the occurrence of this
issue.
We have a .NET 1.1 Windows application that ties in with Great Plains
running SQL Server 2000, using ADO .NET 1.1. The application is run on three
sites – one local (main server) and two remote. I will call them Remote1 and
Remote2 for the sake of this posting.
In overview, the application provides services for filling orders. Orders
are retrieved form the main server and order lines are posted for
ver...