Make Numbers In Formula Stay The Same

Oct 2, 2008

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.

ADVERTISEMENT

How To Make Text Boxes To Stay In Place

Nov 13, 2013

I have text boxes for additional information. Is it possible to make text boxes to stay in place so that if I or someone else deletes a column the text boxes would not shrink and move to the left?

View 2 Replies View Related

How Can I Make The Cumlative Total Stay And Be Updated With Each New Entry

Aug 18, 2009

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?
C D E
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 12 Replies View Related

INDIRECT FUNCTION - Make Data Stay Permenantly

Dec 9, 2009

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 5 Replies View Related

Excel 2007 :: Conditional Formatting Of Numbers Will Not Stay Cleared On Reopening File

Apr 27, 2013

Excel 2007 Conditional Formatting. I am trying to apply conditional formatting to a wide range of cells. I only want the rule to adjust the color and shading, under the FONT tab in the section below. Specifically, I do NOT want to adjust the number format (I have the user select the scaling and use VB to populate the number formatting). I accomplish this by removing the number formatting by going into the conditional formatting rule, clicking on format, and then on the first tab (Number) -- on the bottom right there is button to Clear.

This resolves the issue perfectly, and I can operate the report exactly as I would expect.

Unfortunately, when closing the report (after a save), and then re-opening, the conditional formatting number format is no longer cleared. Worse yet, because it no longer is cleared, the scaling VB doesn't work, unless I go back into conditional formatting rules, and go to the number tab, and click clear.

Screen shot of after re-opening, no changes:

Report looks terrible (not real data):

View 2 Replies View Related

Formula To Hide Or To Stay As It Is Until Value Is Entered

May 16, 2014

In Spreadsheet A Cell H8 is entered as Item Description.

In Spreadsheet B Cell H8 is also entered as Item Description, but if the Item Description in Spreadsheet A has been changed, I need Spreadsheet B to reflect that change, if not it should stay as Item Description.

View 5 Replies View Related

Formula To Make Product Of Two Negative Numbers Negative

May 12, 2009

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 5 Replies View Related

Formula Needed To Make List Of Same Numbers From A List

Mar 21, 2014

A
B
c
d

[Code]....

So the data I put in Is Cells A to D the reast are calculated for me. so for example E2 is looking down column D to see if the number matches the number in A2, If any of them do it will put the name in the corresponding cell in E2 (Or you ca use a different cell if you prefer) so in this case D3=A2 so B3 is the answer also in the cell next to it I need whatever number is the cell next to the answer so in this case C3

View 3 Replies View Related

Make Certain Numbers Red

Jul 22, 2008

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 9 Replies View Related

Make Numbers A Percent Of 100

Nov 4, 2008

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 2 Replies View Related

How To Make All Numbers Formatted As Text

Apr 3, 2014

I have two columns with entries consisting of numbers. I'm trying to match between the columns but some names dont match because of the formatting.

Example

065 matches 065 (I have that green triangle on the cell that indicated "The number in this cell is formatted as text")

However 120 doesn't match with 120 (only one of them have that green triangle)

I tried to format all the cells as text at it seems the match only works when the green triangle is available. The green triangle seems to only show its self when I double click on the cell. I have thousands of entries so that doesn't work. I also tried to use the "Text to Columns" but it coverts entries like 002 into 2 which I dont want.

View 7 Replies View Related

Finding Which Numbers Make A Total

May 22, 2009

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 4 Replies View Related

How To Make Conditional Formatting For Negative Numbers

Feb 19, 2014

I need the conditional formatting to make all numbers that are zero clear (i.e. no fill).

I need it to make all negative numbers to be red, however it doesn't seem to recognize "-1" as a number, and ends up highlight everything red when I say "highlight values < -1 red".

How would I do this?

View 2 Replies View Related

Make Serial Numbers From Repeating Names?

Apr 16, 2014

I have repeating 7 names in A column.(Like a name group)

Each group names repeating after a blank(empty) row.

I want to write to B column for each repeating names from 1 to 7 as number value.

View 2 Replies View Related

How To Make Sure That Only Positive Numbers Are Entered In A Cell

Jan 13, 2008

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 9 Replies View Related

Find Numbers From A List That Make A Specific Total

Nov 18, 2008

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.

242.91
265.71
95.96
113.26
228.16
48.59
64.62
70.59
88.88
146.51
228.99
67.99
40.22
71.51
85.28
654.15

View 2 Replies View Related

Make Positive Entered Numbers Appear As Negative For Budget

Mar 28, 2007

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 5 Replies View Related

How To Make A Single Sheet Print Multiple Page Numbers

Sep 18, 2013

I have a workbook that has a single sheet in it, in cell G2 I want to insert a page number where if I print of multiple copies on the first sheet it will say 1, the second 2, the third 3 and so on and so forth.

I have never written any codes for Excel.

View 4 Replies View Related

Make Excel Find Only Full Match With Array Of Numbers?

Mar 8, 2013

Code:

Application.ScreenUpdating = False
Dim c As String
Dim d As String

[Code]....

this works perfectly but I have found one problem, I want excel to find on the existing arrays "0,00" and "0", only full match and not only partial like for example:

-154.09, which triggers "0" for the second array and 120,00, that triggers "0,00" how to I change to only find full match with the existing arrays?

View 9 Replies View Related

Stay The Same Or Substract

Oct 11, 2008

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 9 Replies View Related

Columns Won't Stay Formatted

Jan 9, 2009

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 10 Replies View Related

Scroll Area Set But Won't Stay Put?

Oct 29, 2007

I set the scroll area on a worksheet and saved the file. The next time I open the worksheet the scroll area is no longer set.

View 7 Replies View Related

Userform - Tab Selection Won't Stay

Jul 25, 2014

1. I have a userform I created that has a text box that pulls it's data from one of the spreadsheets tab. The userform also has 5 buttons I shall call Tab1, Tab2, Tab3, Done, Cancel.
2. The first thing a user will do is make a selection from the text box.
3. The User will then select one of the "Tab" buttons.

Below is my code. What happens is that no matter what button I click (Tab1, Tab2, Tab3) it ends up on Tab3 because it is the last transfersheet line of the done_click() sub routine. How do I get it to stay on the Tab I select?

+++++
Private Sub cancel_Click()
Unload UserForm
End Sub
+++++

Private Sub done_Click()
Dim transferworksheet As Worksheet
Set transferworksheet = Worksheets("Tab1")
transferworksheet.Cells(5, 7).Value = Me.ListBox1.Value

[Code] ..........

View 4 Replies View Related

Time To Stay As Text

May 30, 2008

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 9 Replies View Related

Vlookup To Stay Blank If Nothing Selected

Nov 23, 2008

i have this formula which works well...

View 8 Replies View Related

Lock Column Header So That It Will Always Stay At Top?

Jan 28, 2013

How do I lock a column header so that it will always stay at the top as I scroll down the page?

View 4 Replies View Related

Marco To Stay In Selected Cell

Apr 1, 2014

I am running the below macro which work fine ! However after running the macro, I do not stay in the cell I was!! How to stay in the selected cell?

HTML Code: 

Sub INSERT()
ActiveSheet.Unprotect Password:="P@ssw0r!"
ActiveCell.EntireRow.INSERT

Range("FORMULA1,FORMULA2,FORMULA3,FORMULA4").Select
Selection.Locked = True
Selection.FormulaHidden = False

ActiveSheet.Protect Password:="P@ssw0r!", AllowFormattingCells:=True
End Sub

View 1 Replies View Related

Using Median Length Of Stay With A Look Up Function

Oct 7, 2008

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 4 Replies View Related

R1C1 Reference Style Won't Stay Gone

Dec 12, 2011

Every time I copy and paste cells in a specific workbook it changes the reference style to R1C1. Setting it back in the options, saving the document, closing Excel then reopening brings it back as A1 referencing but copy and paste will again return it to R1C1.

I've checked for VB code and there's nothing there either.

View 1 Replies View Related

Excel 2003 :: Get Some Information To Stay Still?

Jan 29, 2012

While using Excel 2003, I am aware of "freeze plane" and "split worksheet". The freeze plane comes in handy if I want to the first few rows to remain stationary and displayed while I can scroll down to the rows below. Similarly, the split worksheet comes in useful if I want to have the first few columns remain in place while I can maneuver to the right to see the other columns.

*** THE QUESTION IS: how can I get cells A1 to F20 to remain displayed and unmoved even if I choose to move the active cell around anywhere between G1 to K9999

View 9 Replies View Related

NAME Doesn't Stay Attached To The Cell

Mar 19, 2008

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 9 Replies View Related

Highlight Every Other Row & Stay Highlighted After Sort.

Apr 22, 2006

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 2 Replies View Related

Code To Move To A Cell (stay In Top Corner)

May 11, 2009

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 9 Replies View Related

Cursor To Stay On The Cell After Pressing Enter

Jun 23, 2007

Imagine you have only one column - column A. In column A you have 500,000 + rows with text like the following:

!magnus
snooze'
sleepy/
act noun
act verb
Today&tomorrow
*bialgebras
/dev/ null
dev
ull
1970;
Dwight Schrute

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

snooze
sleepy
act
act
Todaytomorrow
bialgebras
devnull
devnull
1970
DwightSchrute

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 6 Replies View Related

Make A Formula Look For Something That IS NOT There?

Sep 23, 2008

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 2 Replies View Related

Formula To Make Every 50 Add 1

Feb 24, 2009

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 9 Replies View Related

Filtering Column - Calculation Does Not Stay With Assigned Cells

Mar 19, 2014

How would I fix my formulas from not working when I filter other columns? It is a simple =A1/A3 cell and this calculation is down the full column. When I filter any column, the calculation does not stay with the assigned cells.

View 2 Replies View Related

Convert List To Table And Stay Updated Dynamically?

Aug 21, 2014

I have a list or log that is updated by a number of people on a sharepoint file. The list consists essentially of 2 columns - lets call them Location and Date.

The Locations are populated from a pulldown list but can be repeated a number of times throughout the list. I have the list set up using Table Formatting so the range updates dynamically

Example:
Location Date
Site1 8/1
Site2 8/2
Site3 8/3
Site4 8/5
Site2 8/6
Site2 8/7
Site3 8/8

As this list gets items added to it I want to populate a summary table on another sheet showing the dates each site was visited like this...

____8/1 8/2 8/3 8/4 8/5 8/6 8/7 8/8 8/9
Site1 X
Site2 -----X-------------X---X
Site3 -------X
Site4 --------------X

(Had to add dashes above to get the X's spaced out properly)

I'm competent with lookups and such but I imagine this needs an array formula or some index/match combination which I'm a little weak on.

The solution should also not require any updating as the source list is updated periodically.

View 4 Replies View Related

Macro To Simulate Mouse Movement To Stay Idle?

Jan 29, 2014

Does someome know a macro to simulate mouse movement to stay idle to prevent communcator (Lync) from turning off? I need this to run for 2 hours only.

View 1 Replies View Related

Web Query Stay Connected And Get Live Data From A Site?

May 7, 2009

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 9 Replies View Related

ADVERTISEMENT