# Excel 2007 :: How To Input Number And Get Name

Dec 9, 2011

I am trying to auto fill one cell with one of several names using different values from another cell. Ex: when one of six numbers (100, 101, 102, 103, 104, 105) in input in A2, I want B2 to display a name (Joe, Tom, Steve, Fred, Tyler, Todd) that corresponds to that number. Cells in columns A and B will be formatted as text. Using Excel 2007.

## Excel 2007 :: How To Count Same Input On A Cell

Jul 7, 2014

I need to count how many times an input in given on a same cell, and doing so on the hole column. Like when I enter "x" on cell A1 and it adds 1 on B1. If the input on A1 changes then comes back to "x" it adds once again 1 to B1.

For one Row I'm using this code but I really can't reproduce it on the others.

[Code] .....

I'm using Office 2007.

## Excel 2007 :: Sum Cells Based On Date Range Input

May 28, 2014

I am using Excel 2007.

Sheet 1 - I have columns filled with dates (weekdays only) For example 28-May, 29-May, 30-May up to 28-Nov. Above 10 rows below each date columns and each cell has some values.

28-May29-May30-May
0.50.50.5
1.01.01.0
1.01.01.0
1.01.01.0
1.01.01.0
1.01.01.0
1.01.01.0

Sheet 2 - I have Start date defined in B1 Cell, and End date defined in B2 Cell

I need defining the formula to sum all the cell values fall between the date range defined in Sheet 2 B1 and B2.

For Example if B1=28-May B2=30-May, I want the sum of all the numbers come under the range of 28-May to 30-May, with this above example, it should be 19.5.

## Excel 2007 :: Input Mask To Format Cell Content

Jan 20, 2014

The cell content is to be 0-7 characters in length.

Alpha characters only, i.e., no numeric.

First character must be uppercase.

Remaining characters may be lowercase or blank.

Is that possible to do in MS Excel 2007?

## Excel 2007 :: Data Validation Not Stopping Invalid Input?

Dec 1, 2011

I just have a basic data validation list. I used "List" and checked the right boxes and the file has been working before. Now the same file does not stop the user from typing in anything.

I re-did the same data validation in a new file and it works. Was there something in the file that prevents it from working?

I have Excel 2007. I saved in both xlsx and xlsm formats

## Excel 2007 :: Not Being Allowed To Input Formula With Reference To Another Workbook

Feb 13, 2012

I am having trouble making any formula references between 2 different workbooks.

For example, I being typing out my VLOOKUP function on 1 sheet, as soon as I get the table argument, I switch over to the other workbook in which the table is, and I no longer have the VLOOPKUP function active.

I am having this problem for any formula in which I am trying to reference this workbook.

I have ensured they are both saved as Excel workbook documents. I am using Excel 2007

## Excel 2007 :: Data Validation Based On User Input?

May 24, 2012

In the spreadsheet shown below I would like the user to select a project via a dropdown list in cell B2 which is from the data set shown in row 7 downwards. Then based on the project they select, they need to be able to see all of the locations associated with that project and choose in B3. Finally they then need to be able to select a team which is associated with the project & location combination chosen in cells B2 and B3.

Excel 2007
ABC2
Project3Location4Team56ProjectLocationTeam7Project 1PerthTeam 18Project 1MelbourneTeam 29
Project 1SydneyTeam 110Project 1Brisbane Team 111Project 1Brisbane Team 312Project 1DarwinTeam 413
Project 1DarwinTeam 514Project 2PerthTeam 315Project 2PerthTeam 416Project 2MelbourneTeam 117
Project 2MelbourneTeam 218Project 2SydneyTeam 419Project 2Brisbane Team 6

## Excel 2007 :: Create Bar / Count Weeks And Colour Bar From Input Date?

Feb 14, 2012

I am trying to simplify a type of gannt chart bar across a spreadsheet. The spreadsheet has dates across row 3 that are calculated from the first cell F3 with =F3+7 to populate the rest of the row with dates. I want to be able to input a start date in D4, then all the other titles in column A. This start date will start the coloured bar at that start date in the chart, I will then copy/drag that start date cell for however number of weeks for the duration and I want the finish date to auto populate E4 with the finish date and also auto populate the numbers of weeks in C4 for that bar.

OAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZ
CACBCCCDCECFCG1D42342McArthur River Mine Power

[Code]....

## Excel 2007 :: Userforms - Input Cell Value (via Text Box) And Output Corresponding Values From Resulting Row

Jun 24, 2013

I have a simple spreadsheet of an inventory. Each row on this spreadsheet represents the data related to an item. I would like for a user to be able to INPUT a serial number via a userform. Then, I would like for the program to OUTPUT some information about that particular item (a few cells that should be on the same row as the serial number).

I attached an image diagram that may better represent what I am trying to do.

## Excel 2007 :: Macro For Finding Out Missing Number And Duplicate Number In A Range

Jan 22, 2013

Excel 2007
ABCDE12145101843222121028543291410388563015104796731501058178325210
685894953107839104354108841011445510985111349661101215516710013135668
981417576999151858708916195971801720607291182161738219236274902024637
59121276476872228657786232425Sheet1

## Excel 2010 :: Macro Design For Serial Number Input

Dec 8, 2011

Excel 2010 O/S XP Macro for serial number input all serial numbers have JTC0. Any VBA code to place in macro to have a cursor appear after the four characters in the macro below?

Example: JTC0| < cursor ready for input

Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+s
'
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "JTC0"
End Sub

## Excel 2007 :: Restrict Cell Input Based On Another Cell Value

Jan 22, 2013

I am working in both 2003 and 2007 Excel.

This is a simplified version of my worksheet:

I have two cells, A2 and B2. When I enter a numeric value into B2, I want it only allowed when certain text values are in A2. I.E.: Allow any value in B2, but only if A1 is either "SYDN or "ADEL".

Now using info found in an old post, I can achieve this with a combination of data validation and code:
=OR(A2="SYDN",A2="ADEL") for the validation and for the code:

[Code] ......

The only problem is I also need to reset B2 if A2 changes to invalid data as a result of some other input to that cell (A2). In other words if B2 is showing "1" already and A2 were to change from "SYDN" to say "LOND", then B2 needs to be cleared. If A2 changed to "ADEL" then it doesn't need to clear but it is ok if it does because it is fine to re-enter "1" into B2 again.

I have attempted to attach a sample worksheet : Xl0000003.xls‎

## Excel 2007 :: How To Convert Number To Date

May 22, 2012

I have a column (A) with numbers like
0
15
30
45
100
115
130
145
200
215
230
245
300
315
330
345
400 and so on up tp 2345

I need to change this into time as;
00:00
00:15
00:30
00:45
01:00
01:15
01:30
01:45
02:00
and so on up to 23:45

Is there any formula which can do this?

## Excel 2007 :: Type Number In A Cell And Have The Word Appear

May 12, 2010

I need to convert numbers to text in excel 2007. I have done it before in 2003, but can't seem to remember exactly what I used. I may have used some kind of conditional formatting, but not sure.

Here is an example of what I need.

If I type "7203" in a cell I want "Home Repairs" to appear. I have a list of words associated with a list of numbers and want to be able to type the number in a cell and have the word appear.

## Excel 2007 :: Changing Number To Add Decimal Places

Jan 30, 2013

I am using Excel 2007 and importing a list of coordinates. The coordinates are in a text file and separated by commas, so I'm opening the file as a delimitted file. All of the coordinates import correctly except one. The value is supposed to 52530.6372, but Excel is automatically changing it to 52530.6371999999

If I manually type a "2" to replace the "1999999", it changes it back. If I type "3" it stays as a 3.

I added a round function to force it to round to 4 decimal places and that seemed to work until I then tried using the number with text commands. When I used an ampersand to insert the number into a text string, the additional decimal places are back, even with a round function!

## Excel 2007 :: Handling Large Number Of Records

Jul 10, 2013

I have various month's data - forex tick data - each month has between 12 and 14 million records and comes down as a .csv file.

I would like to analyse the data in Excel

How I can breakup the huge .csv file into manageable excel parts?

## Excel 2007 :: How To Remove Leading Zeros Of Number

May 6, 2011

I have a lot of record that contain number like shown below:

0000082181
0000005465
0000028997

I want to remove all 0 in front of this number in excel.. I use excel 2007..

I already try using formula

Code:
=IF( LEFT(A1) = "0" , RIGHT(A1, LEN(A1)-5), A1)

but it only remove 5 character in front what about the number that have 6 '0'......

## Excel 2007 :: If String Contains A Number Then Return True

Oct 15, 2011

formula that would return 'TRUE' if the string contains a number. Example below. Using Excel 2007.

One Part Order 123456 for shortage items shipping to US/TX-USA

## Excel 2007 :: Pivot Table - Max Number Of Columns?

Nov 9, 2011

I'm using Excel 2007. My pivot table seems to be limiting me to 256 columns in the Values/Data area. In researching below I believe that I should be able to have 16,000 columns in my Pivot Table.

[URL] The "Big Grid" and Increased Limits in Excel 2007

PivotTables Maximum rows displayed in a PivotTable report is 1 million.
Maximum columns displayed in a PivotTable report is 16,000.
Maximum number of unique items within a single Pivot field is 1 million.
Maximum number of fields visible in the Fields list is 16,000.

## Excel 2007 :: Converting Phone Number Format?

Aug 27, 2012

I have phone number in phone format which looks like this: (123) 456-7890 which needs to be converted to: 1234567890.

I tried but unable to do so.

## Excel 2007 :: Find Last Value In Column Regardless Of Number / Text?

Nov 1, 2013

I've used the below, for example in Column E, the actual data starts in row 4, so I can't use the full E:E syntax:

=IF(ISERROR(INDEX(Data!E\$4:E\$65000,MATCH(9.99999999999999E+307,Data!E\$4:E\$65000))),
INDEX(Data!E\$4:E\$65000,MATCH("z",Data!E\$4:E\$65000)),
INDEX(Data!E\$4:E\$65000,MATCH(9.99999999999999E+307,Data!E\$4:E\$65000)))

The above basically checks for a number, and if fails (there could be #N/A, #DIV/0!, text, or Number-stored-as-text in any cell here), it checks for text. (the 65000 is arbitrary; we will never have more than a few thousand rows)

However this is not reliable--the main problem seems to be number-stored-as-text. And it's not possible for me to do the Paste-Special Multiply *1 trick to convert the numbers because this data is dynamically generated from a database--the user would have to do that every time the sheet opens.

Is there not some formula that simply says: "Give me the last non-blank cell after row 4 in column E, regardless of what type of data"? (Excel 2007)

## Excel 2007 :: Data Validation Query - Sum Of Two Cells Less Than A Set Number?

Jan 6, 2014

I am wanting to create a validation rule so when two cells are added the together the answer must be less than or equal to 14:00 - otherwise a message box will appear

So for example if H7+I7 gives an answer of 13:00 in J7, that's OK, but if the answer is 15:00, the error box will appear. I know that data validation doesnt work on a cell that already has a formula so I'm hoping to use K7 for the Validation and then hide the column

## Excel 2007 :: ID Number Formatting Lost After Text To Columns Is Used

Jul 16, 2014

I have a text file that I need to open in MS Excel 2007. The file contains the following data. (Each column is delimited by the "|" character.)

Part #|Inventory ID
1743|213,221
1864|10,40
1948|1170,1180
5265|100,104,107,10004

Ultimately, I need three columns but the data needs to look the same as it did before I opened the file.

When I open the file for the first time, I use “Text to Columns” to delimit the fields by the "|" character. The problem is that the Inventory IDs in the middle column get a bit whacky.

Here's what I'm left with:
Part # Inventory ID
1743 213,221
1864 10,40
1948 11,701,180
5265 10,010,410,710,004

The comma is retained appropriately when there are two 2- or 3-digit Inventory IDs.

When a row has multiple, longer Inventory IDs or Inventory IDs that vary in length, the comma is moved to every 3rd decimal place.

I tried putting quotation marks around the data before I opened the text file for the first time but that didn’t do anything. I tried Custom Formatting to retain the comma position (0000,0000) but this only worked when I had two 4 digit IDs. I also changed the number type to Text and General. Once the comma was changed, I couldn’t figure out how to get it back.

How to best retain the format of my Inventory IDs.

## Excel 2007 :: Counting Number Of Times A Word Appears

Dec 12, 2011

I have a 2007 Excel spreadsheet (saved as .XLS) with worker names in column A and error types in column B. Column B can have multiple entries (which are sometimes duplicative of each other) separated by a hard return.

What I need to do is run tallies to determine the number of errors by type for each person, counting the value every time it appears, even if it is more than once in a particular cell. The ultimate goal is to generate a formula to track the number of occurrences for all error types types for the person in Column A (i.e. one formula each to track ABC's Procedural errors, ABC's Technical errors, ABC's Admin errors, DEF's Procedural, etc) though ideally I just need a formula to calculate any one of those and I can edit it to get the rest. Here's a sample screenshot:

The COUNTIFS formula is where I started but that only seems to count cells with the value as opposed to occurrences of the value. I did find this formula in my searches but it doesn't seem to work:

=SUM(LEN(B1:B100)-LEN(SUBSTITUTE(B1:B100),"Procedural","")))/LEN("Procedural")

## Excel 2007 :: Count Number Of Cells In A Range That Have Text

Feb 7, 2012

I'm using excel 2007, here's what I need.

I want to count the number of cells in a range that have text in them (any text at all) but not count them if they have numbers in them or are blank. How would this formula be written?

## Excel 2007 :: AutoPopulate Sequential Reference Number Field

Mar 27, 2012

I am trying to do some basic VBA in my spreadheet, I just want to create an auto-open statement that inputs then next sequential number in column A1 every time the s/s is opened i.e.1,2,3,4 .....

I am using excel 2007 and my knowledge is very limited. I used a code:

Sub AUTO_OPEN
Range("A1") = Format(Range("A65536"), "YYYYMMDD") & Right(Range("A1"), 3) + 1
End Sub

(the first time you open the file you would need to add the first entry '20021122001)

But this does not work and I get a compile error when run thru debugger.

## Excel 2007 :: Count Number Of Duplicate Rows With Two Columns?

Jun 3, 2012

I want to count the number of duplicate rows where the exact text in columns A and B match. An example is as follows, where column C would be the desired result. Note that there are hundreds of different text values of column A and hundreds of column B, I just simplified the example.

Excel 2007
ABC1AX72AX
3AX
4AX
5AX
6AX

[Code] ......

## Excel 2007 :: Validation With ISTEXT Not Working When Number In Cell?

Dec 3, 2012

I'm trying to validate a cell in Excel 2007 that should contain only two letters.

Formula:
Code:
=IF(AND(LEN(A1)=2,ISTEXT(A1)),TRUE,FALSE)
When the value of A1 is JK, the result is TRUE
When the value of A1 is 12, the result is FALSE

When the value of A1 is 3K, the result is TRUE
When the value of A1 is K3, the result is TRUE

When the value of A1 is 123, the result is FALSE
When the value of A1 is JKL, the result is FALSE

When the value of A1 is 3, the result is FALSE
When the value of A1 is K, the result is FALSE

The LEN function works as expected, but the ISTEXT function does not, whenever the cell contains a number and a letter. I've tested the LEN and ISTEXT functions separately, and get the same results.

Is it me, or is it Excel?

## Excel 2007 :: Inserting Number Of Blank Rows Between Cells

Mar 5, 2013

I have a worksheet with four data columns A,B,C,D starting in row 2. I want to add between every row, exactly 11 blank rows. So if we have:

Code:

A B C D

1 5 3 4
2 7 6 3
3 9 1 3

the end result should be:

Code:

A B C D

1 5 3 4
2 7 6 3
3 9 1 3

Do I necessarily need a macro for this? Or is there another quick and creative way to achieve this result?

## Excel 2007 :: Count Number Of Digits Without Counting Digit Twice

Sep 10, 2013

The values in columns b:d range from 000-999. I need a formula that will count the number of digits in B:D that match the 3 values in H1 without counting a digit twice. The existing formula counts a digit twice, like the value in H4.

DIGIT HITSABCDEFGH10,1,2209/09/132192191222309/09/134824821441409/08/131191191112509/08/139799792550609/07/137177171551709/07/138668662000
Excel 2007Worksheet FormulasCellFormulaE2=B2&C2&D2F2=SUM(B2:D2)G2=MOD(F2,10)H2=SUMPRODUCT(--ISNUMBER(FIND(","&\$B2:\$D2&",",","&H\$1&",")))E3=B3&C3&D3F3=SUM(B3:D3)G3=MOD(F3,10)H3=SUMPRODUCT(--ISNUMBER(FIND(","&\$B3:\$D3&",",","&H\$1&",")))E4=B4&

[Code] ......