i have tried a lot of ways but i seem not to get it right. need help with this please.. i have two sheets in one workbook, in sheet 1

i have the following data under column A and Column B in Sheet 1.

Column A Column B

A1 a B1 1

A2 b B2 2

A3 c B3 3Problem 1

Now in sheet 2 when i enter in any Cell under Column A any alphabet (a,b or c), i want the corresponding value given in sheet 1 column B to appear in column B of sheet 2. that is

Sheet 2

Column A Column B

A1 c 3

A2 c 3

A3 a 1

A4 b 2

if this is not possible can u tell me a way to write the corresponding values without referring to Sheet 1thanks

In Sheet2, B1 use =VLOOKUP(A1,Sheet1!$A$1:$B$3,2)

And drag it down.If your range or Array is greater than A1 to B3 then adjust the formula accordingly.

Bryan

Hi, In cell B1 on Sheet2, enter this formula:

=IF(A1="","",VLOOKUP(A1,Sheet1!$A$1:$B$3,2,FALSE))Adjust the range of cells Sheet1!$A$1:$B$3 to match the two columns of cells you actually used on Sheet1.

Make sure you use the $ signs in this part of the formula - it will stop the address of the cells used on Sheet1 from changing as you drag/extend this formula.Now on Sheet2, drag to extend this formula down as many rows in column B as you intend to have letters in column A.

At row 20 it will look like this:

=IF(A20="","",VLOOKUP(A20,Sheet1!$A$1:$B$3,2,FALSE))The formula has two parts:

First is an IF statement that ensures that you get a blank cell in column B if the corresponding cell in column A is empty.

Second is a VLOOKUP which looks up the value in the corresponding cell in column A, and searches for it in the first column of the range Sheet1!$A$1:$B$3

It then returns the value from the same row but in the second column (in this case column B on Sheet1).

The 2 in the formula is for the second column in the range and FALSE instructs VLOOKUP to return an exact match - look at the VLOOKUP help file for more details.If there is no match, for example you have "xyz" in a cell in column A on Sheet2, VLOOKUP returns the #NA error.

To avoid the #NA error use this formula which catches the error and displays "No match" instead:

=IF(A1="","",IF(ISNA(VLOOKUP(A1,Sheet1!$A$1:$B$3,2,FALSE)),"No match",VLOOKUP(A1,Sheet1!$A$1:$B$3,2,FALSE)))

Drag the formula down as before.At row 20 it will look like this:

=IF(A20="","",IF(ISNA(VLOOKUP(A20,Sheet1!$A$1:$B$3,2,FALSE)),"No match",VLOOKUP(A20,Sheet1!$A$1:$B$3,2,FALSE)))Regards

tried but say the formula you typed contains an error.

i'm using excel 2007 if that helps, i tried Bryco and Humar's formula but it keeps syaing formula contains an error. =IF(A1="","",VLOOKUP(A1,Sheet1!$A$1:$B$3,2,FALSE))

the "" supposed to be like "a","b" right?????

Hi, I copied the formula you posted in response number 4 and I don't get an error when I paste it in to cell A1 on Sheet2.

Do you have a worksheet named Sheet1

(No spaces in the name) - if not you will need to change the formula.The "" is a pair of double quotes just like you put around the letter "a"

You should be able to copy formulas direct from the response and paste into a cell - no need to re-type them

Using Excel 2007 makes no difference.

Regards

Hi,Ok it didn't work..may be something wrong with my software or PC. i have done exactly as stated but seems to give the same error message. I'm sure the formula u provided is error proof.

lets see. how would i get these conditions into a formula

suppose lets say in any cell under Column A when i enter either A a, B b, C c, D d, (in no particular order) i want the following values where A a = 1, B b = 2, C c = 3 and D d =4 to be presented in the respective cell under Column B. This is without referring to list on another sheet or on the sheet which the formula is entered.

Thanks.

thanks a zillion HUMAR, it did work. there was some problem with my machine. tried on another and it worked fine.

need another favour.

suppose lets say in any cell under Column A when i enter either A a, B b, C c, D d, (in no particular order) i want the following values where A a = 1, B b = 2, C c = 3 and D d =4 to be presented in the respective cell under Column B. This is without referring to list on another sheet or on the sheet which the formula is entered.

can this be done and how do i do it,..

once again many many thanks

Hi, You can used nested IF functions to do this.

There is one IF for each letter.

If the first test [IF(A1="a")...] is not true , the next IF test is performed, and so on, until the final IF which if it is false, returns a blank "".As you want one of two values in the other cell to return the same result "a"=1 or "A"=1 then each IF test can contain an OR statement, so that the IF test is true if either one or the other of the tests inside the OR function is true.

Here is the formula for a or A to d or D:

=IF(OR(A1="a",A1="A"),1,IF(OR(A1="b",A1="B"),2, IF(OR(A1="c",A1="C"),3,IF(OR(A1="d",A1="D"),4,""))))and here it is for a or A to e or E

=IF(OR(A1="a",A1="A"),1,IF(OR(A1="b",A1="B"),2, IF(OR(A1="c",A1="C"),3,IF(OR(A1="d",A1="D"),4, IF(OR(A1="e",A1="E"),5,"")))))Note that as you are using Excel 2007, you can nest large numbers of IF statements - you can do a or A to z or Z

but

it won't work in Excel 2003 or earlier due to the limit on nested levels in formulas - only 7 allowed.Regards

thanks again Humar. gonna attempt it now

Hi Humar I have 3 sheets in the same workbook. Sheet 1 contains main data while Sheet 2 and 3 contains subsidiary data but when entered it will go automatically to respective cells in sheet 1. I have managed to figure that part out.

Now let’s say Sheet 1 is the “Main Mark Sheet” containing the following information in the cells.

Column A Index

Column B Name (this is automatically generated when I enter the respective Index in Column A. this is done so no need to focus on this)

Column C Main Exam Paper Mark

Column D Extra-Curricular Mark

Column E Drawing Skills Mark

Column F Total MarksNow Sheets 2 and 3 are labeled according to the Column D and E of Sheet 1 respectively. Each Sheets 2 and 3 have the following in Columns A to D

Column A = Index

Column B = Name

Column C = Details

Column D = Marks.In Sheets 2 and 3, like Sheet 1 the name is automatically generated, thanks to your help on look up formulae.

Also when I enter in Sheet 2 (Extra-Curricular Mark) or Sheet 3 (Drawing Skills Marks) in Column D the assigned mark, this mark automatically goes to the respective Column and Row and Cell in Sheet 1, i.e. Column D and E of of Sheet 1.

Problem 1

For any given row in Sheet 1, if there is no value under Main Exam Paper Marks (Column C) after entering the Index I want that to be known by highlighting the Cells under the following Columns in the given row in RED. Column A (index), Column B (name), Column C Main Test Paper Mark, Column D (Total)Problem 2

Also when I enter the index number in Sheets 2 or 3 and if the same student has no “Main Exam Paper Marks” (Column C in Sheet 1) I want all the cells in the said row of Sheets 2 or 3 to be highlighted in BLUE.Problem 3

If the student has no values under “Main Exam Paper Marks” (Column C in Sheet 1 ) I don’t want the values entered in Sheet 2 or 3 to appear in Sheet 1 under Column D Extra-Curricular Mark or Column E Drawing Skills Mark respectively.Problem 4

I have problems with entering the correct values in Excel due to carelessness or due to the rush. I want to know when I have entered a value too high or too low by coloring the entire row or the cell which I have entered the wrong figure.

For Sheets 2 and 3 in Column D (Marks) for each Sheet.

When I enter any value greater or less than the range of 0 – 50, I want cells Under Column D of sheet 2 or 3 to be highlighted in PINK or at least the font colour of these cells to change to PINK

Note: A student can be absent to the Main Test Paper but can score points in Extra-Curricular Mark (Sheet 2) or Drawing Skills Mark (sheet 3).

Cells under Column E till end are blank. You can use it.Please help. Thank you.

Hi, Problem 1

To highlight cells in columns:

A (Index), B (Name), C (Main Exam Paper Mark) and F (Total Marks)

Note: your column letters and names in 'Problem 1' do not match the columns given when you described your setup. I have used the column letters and names used in the setup description.To highlight these in red, do this:

Select cells A2, B2, C2 and F2 (use the ctrl key to make the non-adjacent cell selection)

From the Ribbon select Home - Styles - Conditional Formatting,

From the drop down select 'Manage Rules' and select 'New Rule'

In the dialog box select the last item in the list 'Use a formula to determine which cells to format'

Enter this formula in the box=IF($C$2="",TRUE,FALSE)

Note that TRUE and FALSE do not have double quotes - they are Excel's logical values true and false.

The cell reference to C2 must use a $ sign like this: $C2

Click the format button and from the 'Fill' Tab select a red color, click OK

Click OK and close the conditional formatting dialog box.

The cells in columns A, B, C & F will now be red if the cell in column C is empty (but not if it is zero)

As this conditional formatting will be applied to cells before an Index value is entered and therefore the row is empty, I suggest revising the formula so that the red color is not applied if column B is empty.

Use this formula instead:=IF(AND($B2<>"",$C2=""),TRUE,FALSE)

Again note the $ signs in the cell addresses.

Select cells A2 to F2, Copy and then select all the cells in columns A to F from row 2 down to the last row you intend to use and Paste Special... - Paste Formats.Problem 2

Decide how many rows you will be using. For this example I used rows 2 to 50 on the Main Mark Sheet

Select cells B2 to C50 (adjust as appropriate)

With these cells selected in the address box just above the row numbers and to the left of the column letters replace the cell address showing with "MainMarks" (don't enter the double quotes), then click Enter with the cursorstill inthe address box.

This allows the range B2 to C50 to be referenced by the name "MainMarks" instead of by a worksheet name and cell reference.[Apply this to both worksheets Extra-Curricular Mark & Drawing Skills Mark]

Select cells A2 to D2.

Follow the conditional formatting instructions and enter this formula:=IF(AND($B2<>"",VLOOKUP($B2, MainMarks,2,FALSE)=""),TRUE,FALSE)

Add a Blue format & OK out of conditional formatting.

Copy cells A2 to D2 and as before Paste Special ... Formats to the rows you plan to use - I selected B2 to D50, to match the number of rows setup on the Main Mark SheetNow, when a name appears in column B on the Extra-Curricular Mark worksheet, but the cell in column C on the Main Mark Sheet on the same row as their name is empty, the cells will be colored blue.

Problem 3

You haven't said what formula is in the cells in column D.

I have used a VLOOKUP to find the name from column B in column B on the "Extra-Curricular Mark" worksheet and return the value from column D of that person's row.

I have included a message "Name not found" when the name in column B is not found on the other worksheet.

I have combined a test for no name in column B with a test for no entry in the Main Exam Paper Mark column (C), using OR ... OR(B2="",C2="")

If either is true, no lookup is performed to try and find the mark from the "Extra-Curricular Mark" worksheet. This was my final formula in cell D2:=IF(OR(B2="",C2=""),"", IF(ISNA(VLOOKUP(B2,'Extra-Curricular Mark'!$B$2:$D$6,3,FALSE)), "Name not found", VLOOKUP(B2,'Extra-Curricular Mark'!$B$2:$D$6,3,FALSE)))

(Formula split onto more than one line for ease of viewing)Copy D2 and Paste Special... Formulas to cells D3 to D50

Repeat for column D "Drawing Skills Mark"

Problem 4

Select cells D2 to D50 on the "Extra-Curricular Mark" worksheet

Select Data Validation (I don't have Excel 2007 on this PC, so I can't give you the exact sequence)

In the data validation dialog box select the settings tab, and under 'Allow:' select 'Whole number'

Enter the values for 'Minimum:' and 'Maximum:' and click OK

Now you will not be able to enter values outside the range in these cells.

[You could use conditional formatting to set the font color to Pink when a value outside the range 0 to 50 is entered, but I think that this data validation approach is more appropriate in this case. The conditional format formula would be=IF(OR($D2<0, $D2>50),TRUE,FALSE)and Format... - Font - Color:].Repeat for D2 to D50 on the Drawing Skills Mark" worksheet.

Regards

Ask Your Question

Weekly Poll