Question about Business & Productivity Software

Assuming 1) you're using Excel and 2) the addresses are written:

1234 Main St., Anytown, CA 90210

You can use "Text to Columns" feature (selected "Delimited" and check the box next to "comma"). This will split the data into 3 columns (address | city | state and zip). Then run the "Text to Columns" again on the "state and zip" column and use "Fixed width" to separate the state from the zip.

If the assumptions I used are incorrect, clarify and we'll try again.

Posted on Jan 06, 2009

There are 2 types of direct cell references that you can use when you're writing formulas: Relative References & Absolute References.

A Relative Reference is the address of a cell (e.g. A5). When a Relative Reference in a formula is copied from one cell to another, the Reference gets changed automatically. e.g. If you put a formula in cell c5 as A5+1, when you copy this from c5 to c6 the formula A5+1 will automatically change to A6+1.

An Absolute Cell Reference does not change when its copied to another location. As in the example above if the formula in cell C5 is written as $A$5+1, if you copy this formula from C5 to C6 it will remain as $A$5+1 (NOT change to $A$6+1.

The $ sign signifies Absolute, and can be applied to the Row reference, the Column reference, or both Column & Row (as in the example).

A Relative Reference is the address of a cell (e.g. A5). When a Relative Reference in a formula is copied from one cell to another, the Reference gets changed automatically. e.g. If you put a formula in cell c5 as A5+1, when you copy this from c5 to c6 the formula A5+1 will automatically change to A6+1.

An Absolute Cell Reference does not change when its copied to another location. As in the example above if the formula in cell C5 is written as $A$5+1, if you copy this formula from C5 to C6 it will remain as $A$5+1 (NOT change to $A$6+1.

The $ sign signifies Absolute, and can be applied to the Row reference, the Column reference, or both Column & Row (as in the example).

Oct 17, 2014 | Microsoft Excel for PC

You would use the "+" to add the columns. Example: "=column+column+column" then CTRL and Enter.

Oct 08, 2013 | Microsoft Office 2003 Basic Edition...

Try this:

=LOOKUP(B4,A$1:A$31,A$1:A$31)

=LOOKUP(B4,A$1:A$31,A$1:A$31)

Sep 06, 2009 | Microsoft Office Professional 2007 Full...

When you include addresses like A2, C10, F15 they are relative addresses .eg: If you have entries like:

in B4 you have saturday

in C6 you have the formula =FIND("a",B4) .....the answer is 2

B4 is a relative cell address, because if you copy the formula across the row, OR up/down the column, the formula changes and the answer changes

To make it an absolute address you have to type a $ in the cell address. If you type the cell address formula as $B4, it means wherever you move to or copy the formula the B will not change

If you type B$4 the row no 4 is ABSOLUTE or fixed.

$B$4, both column B and row 4 are fixed. If you move the formula anywhere the cell reference will not change.

Summary B4 is a relative cell address

$B4 is absolute address (B is fixed)

B$4 is absolute address (4 is fixed)

$B$4 is abso;lute address (Bis fixed, 4 is also fixed)

Hope this answers your question

luciana44

in B4 you have saturday

in C6 you have the formula =FIND("a",B4) .....the answer is 2

B4 is a relative cell address, because if you copy the formula across the row, OR up/down the column, the formula changes and the answer changes

To make it an absolute address you have to type a $ in the cell address. If you type the cell address formula as $B4, it means wherever you move to or copy the formula the B will not change

If you type B$4 the row no 4 is ABSOLUTE or fixed.

$B$4, both column B and row 4 are fixed. If you move the formula anywhere the cell reference will not change.

Summary B4 is a relative cell address

$B4 is absolute address (B is fixed)

B$4 is absolute address (4 is fixed)

$B$4 is abso;lute address (Bis fixed, 4 is also fixed)

Hope this answers your question

luciana44

Sep 05, 2009 | Corel WordPerfect Office X4 Home & Student...

You would have to combine the use of 2 functions. The Address and Match funbctions.

Lets say the number you want the address of is located in cell F1 and you have 2 columns of numbers. One colum in Column A and the other in column B. I will give you 2 formulas. The 1st one will return just the row number. The 2nd one will return the cell address.

Option 1: Lets say you just want to know the row reference of the number in cell F1. Place this formula in cell D1. =MATCH(F1,A1:A20)

If you have another column ytou want the row number of, place the formula in lets say cell D2 and change the column references from 'A' to 'B'.

Option 2: If you want the cell reference, place this formula in cell D1 and D2 instead of the firt formula.

=ADDRESS(MATCH(F1,A1:A20,0),1,1,TRUE)

And just like the first option, for the 2nd column, put the formula in D2 and change the column reference 'A' to 'B'.

Lets say the number you want the address of is located in cell F1 and you have 2 columns of numbers. One colum in Column A and the other in column B. I will give you 2 formulas. The 1st one will return just the row number. The 2nd one will return the cell address.

Option 1: Lets say you just want to know the row reference of the number in cell F1. Place this formula in cell D1. =MATCH(F1,A1:A20)

If you have another column ytou want the row number of, place the formula in lets say cell D2 and change the column references from 'A' to 'B'.

Option 2: If you want the cell reference, place this formula in cell D1 and D2 instead of the firt formula.

=ADDRESS(MATCH(F1,A1:A20,0),1,1,TRUE)

And just like the first option, for the 2nd column, put the formula in D2 and change the column reference 'A' to 'B'.

Feb 17, 2009 | Microsoft Excel for PC

Here is one way to do it:

1. In column A enter the name

2. In column B enter their start date in the format day-month-2008 (31-12-2008 for Dec. 31, 2008); if a day is less than 10, put zero in front (03, 04, etc.)

3. In column C put 31-12-2008 (December 31, 2008, the last day of the year)

4. In column D, put C1-B1 and format as a number with 2 places after the period (10.25 for example) if you want to track partial days, other format for no zeroes after the period (13)

5. In column E, put this formula:

(D2/30)*1.25 (assuming this is line 2)

You can then copy these lines as many times as you need to, total them, etc.

1. In column A enter the name

2. In column B enter their start date in the format day-month-2008 (31-12-2008 for Dec. 31, 2008); if a day is less than 10, put zero in front (03, 04, etc.)

3. In column C put 31-12-2008 (December 31, 2008, the last day of the year)

4. In column D, put C1-B1 and format as a number with 2 places after the period (10.25 for example) if you want to track partial days, other format for no zeroes after the period (13)

5. In column E, put this formula:

(D2/30)*1.25 (assuming this is line 2)

You can then copy these lines as many times as you need to, total them, etc.

Dec 27, 2008 | Microsoft Office Standard for PC

Are you looking to solve any particular problem?--- because there are a huge number of possible formulas in Excel.

However, in my opinion, the most commonly needed ones are addition, subtraction, division, multiplication, and summing.

Suppose you have the following numbers typed into your Excel spreadsheet:

**columns: A B C D**

**rows**

**1 ** 20 3

**2 ** 10 4

**3 ** 15 2

**4 ** 1 2 3

Then suppose you type in the following formulas (in the D column):

**columns: A B C D**

**rows**

**1 ** 20 3 =A1+B1

**2 ** 10 4 =A2-B2

**3 ** 15 2 =A3*B2

**4 ** 1 2 3 =sum(A4:C4)

Then the following answers will appear in the D column:

**columns: A B C D**

**rows**

**1 ** 20 3 23

**2 ** 10 4 6

**3** 15 2 30

**4** 1 2 3 6

However, in my opinion, the most commonly needed ones are addition, subtraction, division, multiplication, and summing.

Suppose you have the following numbers typed into your Excel spreadsheet:

Then suppose you type in the following formulas (in the D column):

Then the following answers will appear in the D column:

Sep 29, 2008 | Microsoft Business & Productivity Software

Rudils,

The key is to import the data and not open the file directly.

1. Open a Blank Workbook in Excel.

2. Data, Get External Data, Import Data. (Excel 2007 is Data, Get External Data, Data from Text)

3. Browse to your .csv file and Select "Import".

4. Import Wizard should appear.

5. Page 1 Select "Delimited"

6. Select the row which you want to start the import.

7. click "Next"

8. In the Delimiters, select "semicolon" and/or other delimiters you are using.

Note: The bottom half of the window will preview the way the data is to be imported.

9. click "Next"

10. highlight each column of your data in the window below. For each column you can specify "General", "Text", "Data", or "do not import column" using the radio buttons in the top left of the Wizard box. This is an optional step.

11. Click Finish.

I hope that helps. Please add a comment if it not clear.

kpenguin

The key is to import the data and not open the file directly.

1. Open a Blank Workbook in Excel.

2. Data, Get External Data, Import Data. (Excel 2007 is Data, Get External Data, Data from Text)

3. Browse to your .csv file and Select "Import".

4. Import Wizard should appear.

5. Page 1 Select "Delimited"

6. Select the row which you want to start the import.

7. click "Next"

8. In the Delimiters, select "semicolon" and/or other delimiters you are using.

Note: The bottom half of the window will preview the way the data is to be imported.

9. click "Next"

10. highlight each column of your data in the window below. For each column you can specify "General", "Text", "Data", or "do not import column" using the radio buttons in the top left of the Wizard box. This is an optional step.

11. Click Finish.

I hope that helps. Please add a comment if it not clear.

kpenguin

Nov 22, 2007 | Microsoft Excel for PC

Vlookup: =VLOOKUP(A1,A1:C10,3,FALSE)
1. Lookup_value - A1 the value you want to search.
2. Table_Array - A1:C10 You will only search the lookup_value in the left column which is A1:A10 in this example.
3. Col_Index_Number - If you find a matching value give me column 3. Since A1=A1 it will give me C1. In this example 1 is A, 2 is B and 3 is C.
4. Range_lookup - I always choose false which is exact match. True will give you approximate match and its not always correct.
Vlookup is used when you have a list of values and you want additional values that exist in other fields. You will get those values only for the fields that you search for. In your example you can get the address by running vlookup at the names, it is also good way to search duplicates.
Q2 is very simple, on field D1 just type:
=A1 & " " & B1 & " " & C1
Just drag it or double click on the drag square
Let me know if you have any other questions
Daniel

Sep 24, 2007 | Microsoft Office Standard for PC

If the First Name and Last name are equal to both work sheets, I will just sort them by First Name and Last name and copy the results into a separate work sheet.
If the names are different you will have to use Access:
This can be easily done with Access and SQL, just import both work books into different tables to access and use the create query wizard, play with the results until you will get the result you want. Access will also solve the duplicate problem easily.
Very important:
You will have to use join properties:
Read help about join properties and understand how this works...
Afterward you can export it back to Excel or use Copy/Paste.
If you can't use Access and have only Excel:
You will have to use Vlookup on various fields until you will get the result you want. Its a hard work but in the end you will get the same result as access.
Let me know what happend
Daniel

Aug 20, 2007 | Microsoft Office Standard for PC

Nov 29, 2016 | Toshiba Business & Productivity Software

67 people viewed this question

Usually answered in minutes!

Are you using Excel?

×