Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Make Numbers In Formula Stay The Same

I have a formula:
=INDEX('Part Info'!E1:E300,MATCH(1,('Part Info'!A1:A300=A1)*('Part Info'!B1:B300=B1),0))

I want the E1:E300 to stay E1:E300 when I drag it down but I want the A1 for example to change to A2, A3, A4 and etc.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
How Can I Make The Cumlative Total Stay And Be Updated With Each New Entry
I have two columns D and E, the header takes up rows 1-4 so the first available row is row 5. The D column is going to have numbers added daily, the E column will show the cumlative total. How can I make the cumlative total stay and be updated with each new entry in column D?
Name daily cumlative
dave 20 20 Row #5
Jim 30 30
Tim 11 11
Sara 6 6

Say these are the entries for day one, I need to be able to make new entries in the same D column on the next day and have it update the running total in E?? I cant figure the darn thing out.

View Replies!   View Related
INDIRECT FUNCTION - Make Data Stay Permenantly
I have approx 500 workbooks which contain custoemr details and I need to extract certain info (postcode, name, contact, phone number etc) and place it all on 1 spreadsheet. I was originally going to source data from unopen workbooks, but have realized I only need to do this task once, and I can maintain it from then on...

I have created a spreadsheet (attached) which uses INDIRECT formulas to retrieve the data. When I type in the file name in column A, Columns B to K fill themselves in. What I want to know is:

Once I open say, 10 Workbooks, TYPE the name in manually and all the other cells have filled themselves, I want to somehow KEEP the data there, rather than it return a #REF error when I close the workbooks to open the next batch.

Also, if you look at my workbook, to the far right is a range of data which I use in my INDIRECT formulas. Once I have filled all the cells with the relevant data, I want to be able to Filter my spreadsheet by Date Ascending. BUT I cant seem to do it because it includes my INDIRECT range in the filter and mess's up the formulas then I have tried Locking & protecting workbook, with Autofilter & select cells etc TICKED for users to use, but it still tells me to unlock workbook when I try to filter.

View Replies!   View Related
Formula To Make Product Of Two Negative Numbers Negative
I have a large dataset (24000 rows) that requires me to multiply two different columns of integers. In some cases, the two integers are both negative and multiplying them results in a product that is positive. I actually need that product to be negative rather than positive. I can't quite seem to figure out the best way to accomplish this.

View Replies!   View Related
Make Certain Numbers Red
I have some numbers in column G and column I. The formula I'm wanting to write in lamens terms is:

If the number in I4 is not within 10% of the number in G4 then I want the number in I4 to be red otherwise I want it to stay black.

View Replies!   View Related
Make Numbers A Percent Of 100
I have a spreadsheet with a large list of plants. Each plant has a breakdown of colors by container size. Each cell contains a number that corresponds to a percent, e.g. a cell may contain the number 20, which would also mean this number is equal to 20%. I want to change all numbers to a percent of 100, or turn 20, for instance, into .20. There are many hundreds of numbers that I need to make a percent, so I was hoping I could do this in one fell swoop somehow. This percent number will be used in another spreadsheet for calculating on order. How do I do this?

View Replies!   View Related
Finding Which Numbers Make A Total
I do a lot of work in excel to do with accounts and this often needs checking against sage. When the invoices/petty cash sheets are put into sage the total amount is put in, but in my spreadsheets I need to split the reciepts. So I was wondering if there was a formula/VBA code, that if I only knew the total of the invoice would find which cells added up to this total?

View Replies!   View Related
How To Make Sure That Only Positive Numbers Are Entered In A Cell
I am trying to restrict data input to a cell for only positive numbers. Currently, I have used data validation to accept decimals and numbers greater than zero. The cell is formatted for percentage with 2 decimal places. The "error message" alerts you that you have to have positive numbers only. The catch is that when the error message comes up and you use the 'retry' button, the entry is multiplied by 100.

View Replies!   View Related
Find Numbers From A List That Make A Specific Total
I have a list of numbers and want to see if the sum of any of them exactly makes up a specific larger number. Any quick way to do in excel? Eg do any of the below together make the exact total of the number at the bottom.


View Replies!   View Related
Make Positive Entered Numbers Appear As Negative For Budget
I am trying to create a budget in excel and want to type my numbers (in certain cells only) without a - sign but I want them to appear, and calculate, as negative numbers. Basically, I am lazy and don't want to have to remember to use a - for all of my expenses when I am inputting the data.

View Replies!   View Related
Stay The Same Or Substract
In Cell O4 I have a number 35 that number never changes.

Now in Cell C40 I have another number that is a formula. the formula gives me a total of how many points are left.

In C42 I want the total to either been 35 or subtract the negative number from 35, the points can never go over 35.

Any number that is a positive or 0 will equal 35, if a negative number subtract from 35.

View Replies!   View Related
Time To Stay As Text
I have a spreadsheet that tracks employee stats. Most of these stats are amounts of TIME. These times are being pasted from another source and the problem I'm having is that as soon as excel sees "1:02:03" it puts it in TIME format. The problem I have with that is later on I need it in text format because a lot of the other times are in text already. (the program the info is coming from will use ":59:59" and not have a 0, so I have compensated for that later) So how can I paste 1:01:01 and not have it saved as TIME? I cant CONVERT it to text, I need it to be text from the start.

View Replies!   View Related
Vlookup To Stay Blank If Nothing Selected
i have this formula which works well...

View Replies!   View Related
Using Median Length Of Stay With A Look Up Function
I have two columns of data

1st - Multiple physician names (may be duplicate as data set is Emergency Room encounters)

2nd - Denotes the length of stay in minutes for each patient encounter

I want to develop a formula to calculate the median length of stay (off 2nd col) for every physician a select in a drop down.

View Replies!   View Related
NAME Doesn't Stay Attached To The Cell
I have a cell (H27) which has a (sum) total in and have attached a NAME FRED.
This is fine as long as the client doesn't add a row above it.
If they do when I re-open the sheet the NAME doesn't stay attached to the cell (now H28)

Is there a way to keep the NAME attached to the cell regardless where it is?

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Range("$H$27").Name = "Fred"
End Sub

View Replies!   View Related
Columns Won't Stay Formatted
I have Excel 2008 for Mac (vers 12.0). On a basic spreadsheet, the first column is for date which I have formatted so I can type in 15/8/9 and it appears as 15-Aug-09.

However, each time I open Excel after having Quit it, I have to reformat the cells in this column again as they revert to converting my date input with something like 39,123.

My other columns are formatted to currency and are always okay.

View Replies!   View Related
Cursor To Stay On The Cell After Pressing Enter
Imagine you have only one column - column A. In column A you have 500,000 + rows with text like the following:

act noun
act verb
/dev/ null
Dwight Schrute

and your goal is to figure out a way to simultaneously transform the above rows into:


I know I can use Filter and filter out characters like &'!/; but that takes too long because I have a lot of lists to go through with thousands of entries. There must be a macro that will close spaces between words when only one space exists, but eliminate every word if 2 or more spaces exist between words - as well as eliminate unwanted characters like the ones I described above. I know I could use substitute and eliminate the spaces between words =substitute(a1," ","") but then I'd have to filter each and every instance of unwanted character.

View Replies!   View Related
Make A Formula Look For Something That IS NOT There?
Total newbie trying to use excel for work here. I have two problems that both need the use of "is NOT in another column". 1. I need to count the number of instances in a column that do NOT have the word "fly" in column G. The current formula is...

View Replies!   View Related
B1:B6 Background To Change To Green, And Text To Stay Black
I have a table with a range A1:C6

If B1 is “hello” I want B1:B6 background to change to green, and text to stay black
But if B3 is “goodbye” I want this to override the above and make B1:B6 background white and text colour of B1:B2 and B4:B6 white (so the text in these cells seems to disappear).

View Replies!   View Related
Code To Move To A Cell (stay In Top Corner)
I want code to move the cursor to A788

But keep A788 in the top left corner on screen.

Is there easy code to help me accomplish this?

Also, Is there code that will take you to the first cell it finds closest to today's date.

Dates are in Column B.g

View Replies!   View Related
Highlight Every Other Row & Stay Highlighted After Sort.
I have an excel spreadsheet that shows every other row in a pale blue. When I do data sorts, the rows loose their order. Is there a way to keep the row color in every other row and still keep it that way after the data is sorted?

View Replies!   View Related
Formula To Make Every 50 Add 1
If i wanted to tack on something to the end of a formula so that it would make every 50 add 1 (example: 200 would convert to 204, because 200 divided by 50 is 4).

View Replies!   View Related
Make Concatenate A Formula
I have a DDE code that returns a stock symbol value. The code is as follows, where @ES# is the symbol:


I am trying to create a cell so when I put in a symbol (H10), it automatically gives me the quote. So I split up the code and then concatenated it as follows:

G10: '=NeoTicker|q!'
H10: '@ES#

View Replies!   View Related
How Do You Make Text In To A Formula
If I have different equations in various cells without an = sign (text only) how do I make the cells into formulas without using the formula bar?

e.g Cell B1 has text in it A1+A2+A3 and we need cell B2 to be =A1+A2+A3

View Replies!   View Related
Web Query Stay Connected And Get Live Data From A Site?
Is there any way to have a web query stay connected and get live data from a site while still being able to use excel at the same time?

Basically the site has information that changes by the second and i need to keep a running record of it, and when it hits the variables needed i want to be able to trigger a set of code.

View Replies!   View Related
Formula To Make A Box A Color
I need to be able to enter text in a box, and from that box the formula looks at the text to see if it is in within strings of text in any other box in the entire spreadsheet. If the text is there, the box needs to turn green (or any color). If not, it stays the same. The catch is I need the formula to be in a separate box, and the Entered text will be in a blank cell, so the user (who is excel illiterate) will not be confused by the odd formula.


B1, B2, C2, D4, and D6 will have the items: XXX, YYY, ZZZ
B3 and B4 will have only: YYY

In the "search box" the user will enter XXX

B1, B2, C2, D4, and D6 turn green, while B3 and B4 stay as normal.

If they enter YYY in the search box, all of them turn green.

View Replies!   View Related
Formula: Make Drop Down Boxes
Column A - I want to make drop down boxes #1-30 - which would represent quantity
Column B- That will be 30 rows of dollar amounts
Column C - To be able to calculate the quantity x dollar amount

I'm able to create the drop down boxes with the numbers, but say I choose the number 5 by the dollar amount 200.00 It won't calculate 5 x 200.00....

I've been trying the formula you would use to find an average, and i've tried the if statments, but my drop down box isn't being recognized.

View Replies!   View Related
Formula To Make Cell Blank Instead Of Zero
I am looking for a formula that will work with my current formula, and check that cells a1 and b1 contains information. If they do contain info then the original formula should carry on as usual and leave the answer in cell c1. I also need it so when no information has been entered in cells A1 and B1, C1 will be blank instead of containing a zero.

This is the formula I have at the moment:
=IF(SUM(A1=0,B1>0),B1-A1,"No Charge?")


View Replies!   View Related
Make This If Formula Return A Cell Value
Heres my current formula but its justreturning text i would like it to return the cells value


View Replies!   View Related
Formula To Make Sure No Cells Are Duplicates
A1 = 3
A2 = 3
A3 = 3

formula: =A1A2 gives us FALSE....

Why does =A1A2A3 give us TRUE

What is the best formula to use to make sure no cells are duplicates?

View Replies!   View Related
How Can I Make A Persons Name Read A Formula
If I type the name Joe Bloggs How can I make whenever i type that name the computer knows to retreive a number or formula from a cell on another or same sheet.

View Replies!   View Related
How To Make + Symbol Variable In The Formula?
How to make + symbol variable in the below formula?

This formula is in G25 and symbol is in F25 has + & - symbols


View Replies!   View Related
Make A Part Number Formula?
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 Replies!   View Related
Can I Make A Formula That Will Highlight A Cell
I have a large spreadsheet with many pieces of equipment in it. There are a few piece that I would like to highlight should thier computer license go out of date, or even better 30 days prior. I have a Feild with the Expire dates but it is at the end of the spreadsheet not in plain sight. Is there a way to make my Equipment description column highlight when the date in the other column reaches the current date (or 30 Days before). I have cut and pasted the part of the spreadsheet into a new file to make it easier to read and hid all the none important feilds.

View Replies!   View Related
Personal Macro Workbook In The Startup Folder Must Stay Open For Recording
I have a macro that is designed to paste a row of formulas into each Subtotal line on a 30,000+ line spreadsheet. I have specified the formulas that I want to paste in the named range "formulas". The macro below worked fine for about 3000 lines, then threw the error Run-Time Error 1004: Paste Method of Worksheet Class Failed on teh line ActiveSheet.Paste. I thought it had just bogged down my computer, so I added teh ScreenUpdating and Calculation lines, but they made no difference. I also tried reducing the range to just a couple thousand lines, which also made no difference. I also have found some suggestions on this forum and others for this error.

In addition to it working fine for 3000 lines, then failing, there are a couple other weird things. One is that on the line in the spreadsheet that threw the error, the formulas were pasted in up to column AX, leaving columns AY through CR blank. The second odd thing is that this exact same macro still works on my sample data spreadsheet (which only has around 50 records).

Sub FormatTotalRows()
Dim rCell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each rCell In Range("A3040:A5000")
If Len(rCell) > 0 Then
ActiveCell.Offset(0, 2).Select
End If
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

View Replies!   View Related
Psychologist Trying To Make Custom Formula For My Evaluations.
The test takers are shown three pictures. Under each picture is a number. So for example, let's say there is a plane, a car and a train presented for question #1. Underneath those pictures are the numbers 1, 2, and 3 respectively. Those numbers represent a tendency to a certain occupational field. After 55 questions, we can make a pretty good recommendation of where we think they should look for a job (without going into the specifics of the test).

There are 11 occupational fields. In excel, I make those fields A through K. In field L1 through L55 I put in their answers.

Let's say that in all 55 questions, for example, answering 1, 3, and 5 lead to vocational choice K, I want it to tally it up for me automatically. I don't want to have to count those by hand.

I just want to put in the patients answers, and have excel do the work. I tried using the "countif" function, but it doesn't look like you can add multiple conditions.

I need something that will be like this: add 1 each time (as in a tally) to the cell I choose, for the answers that I specify. So, ultimately, it will be tallying up the answers based on which categories that I correspond them to into the respective cells.

So to be redundant... Let's say that answering 3, 7, and 9 to questions 1, 2, and 3 pertain to cell B. After I put those in, I want cell B to have the number 3 in it.

View Replies!   View Related
Make A Cell's Values Become A Formula
I have on my Sheet named "Data" in Cell K4


The values of that cell become a formula.

I try to make a macro that pastes 'Data'!K4 into 'Data'!L3 and then have that formula functioning in cell 'Sheet1'!A31.

The problem is when I try to make a macro to do this it will always paste the values that were recorded during the macro rather than the unique formula that is created via cell 'Data'!K4 at the time.

Is there any way to have A31 actively using the values that are created with 'Data'!K4? at all times?

View Replies!   View Related
How Do I Make And SUM(OFFSET Formula Dynamic
why this isn't working?


View Replies!   View Related
Leadtime :: Make A Substract From A Today Formula
i got the data in this format and need to get it as date in order to make a substract from a today formula minus that date in that format, if it helps this is data imported from a website.

View Replies!   View Related
Make Part Of A Cell Bold Using A Formula??
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:

View Replies!   View Related
Make Formula Pick Up Filename From A Different Cell...
I have a formula that looks like this : ='G:STOCKS[1001 Stock.xls]Matl%'!$I$32

There are hundreds of similar formulas, all referencing different cells in the same file. The “1001” bit refers to the date, and next month it will need to say “1002” to pick up the new month's file. I would like to hold the “1002” bit in a cell (say A1), so that at month end, I just change that one cell, and all the formulas will point to the new file, but I’m not sure how to work that into the formula.

View Replies!   View Related
Make A Formula Output A Blank Cell?
I want my formula to output a blank cell if the answer is 0. Ex. [B1=IF(A1=0, ???, A1)]. I want B1 = A1 if A1 is not 0. I want B1 to output a blank cell if A1 = 0.

View Replies!   View Related
Count Used Cells Formula. Make Reference Static
I have a problem using indirect in a counta function. Basically, I want to count the number of used cells in a range. However, there would be insertions to the rows (using the insert row) on top of the range.

my function is now =counta(indirect(" events database"!"A"&M22&":A"&M500)

However, this formula returns an error. What have I typed wrongly?

View Replies!   View Related
Sumproduct Formula: Count The Components Used To Make One Product
Is there a simpler way to to the calculations on the spreedsheet i have attached?

Basically I want to count up the components used to make one product

View Replies!   View Related
Make Formula Cell Appear Empty Until Data Is Entered
I'm creating a "universal-fluctuating" vendor inventory return worksheet for a auto parts store that consist of one criteria (cores, warranties, or N/R ) and will return one or two results of core cost and/or unit cost. This part of the task I have accomplished by using a drop down list for my criteria and my results will appear in two different columns using a Vlookup table. The problem is due to inventory fluctuating from cores and waranties on a month by month basis, vendor requiremnts differ for the number of units returned, and last make the boss happy on ink and papers supplies :D I was wondering if it is possible loop my code in a given column where it will move my code to the next row untill I reach a grand total?

View Replies!   View Related
Copy And Paste (not Reading The Totals In Row 2 To Make The Formula Complete)
I am trying to get the formulas to work on Sheet 3, the rows formated to percentages correctly, but they are not reading the totals in row 2 to make the formula complete. I also want this to run more than one number, I would prefer a range for example value >= 1.29 so that Sheet 3 shows all items less than 1.29.

View Replies!   View Related
Make Now () = 06/29/2009 (make To Cells With Dates Equal Each Other)
I need to figure a way to make to cells with dates equal each other if the
day,month and year are the same but are placed into a cell at different times during the day. "Making Date Now () = (06/29/09) In another cell". Therefore, A1= Now() and E11 = 06/29/09

View Replies!   View Related
Use The Results Of A Formula As Column/row Numbers In Another Formula
I have two cells. The first cell has the formula:

=CONCATENATE("D",TEXT(MATCH($B$6,'Zip Ranges'!$D$1:$D$157,0)+1,"0"))

which results in a col and row number (such as D65).

The second cell has the following formula:

=INDEX('Zip Ranges'!$A:$B,MATCH($B$6,'Zip Ranges'!D1:$D$157,0),2)
I wish to replace the 'D1" in the Match function with the results of the first cell's formula.

I assume Indirect would work, but I don't know how to code the formula to use it.

View Replies!   View Related
Make Cell Formula Display A "+"
i have a formula that will return both negative and positive numbers. If it's negative it obviously shows a "-" sign. How do I force it to display a "+" if the result is positive?

View Replies!   View Related
Make Formula Cells Empty Rather Than Empty Text
Is it possible to make a cell "really" blank/empty based on an If statement? For instance:


Has a value_if_false of "". But Excel interprets this a bit differently than a cell that never had anything typed into it.

So if you have a column full of this formula copied down, and hit <control+down arrow>, you will go straight to the bottom and skip over all rows. Whereas if you have a column with values and empty cells alternating and hit <control+down arrow>, you will only skip the empty cells and go to the next value. Excel treats the conditionally empty cells as if they have a value, when it comes to this type of navigation. This holds even if you copy and paste "Values" for the cells over the formulas.

Is there any way to tell Excel to make the cells truly empty?

View Replies!   View Related
Negative Numbers In An IF Formula
IF formula


G9 and G10 can be positive or negative. If G9 or G10 is a negative number,
I want the greater of the two to be used and then to be added to E10. But
right now, Excel is saying -20 (which is G10) is greater than zero. I'm
getting a VALUE error.

View Replies!   View Related
Copyright © 2005-08, All rights reserved