Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup formula question
I want to populate cells on worksheet 2 from worksheet 1 with values that a
user selects from worksheet one. The user can select multiple values but I only need those values selected to appear and not repeat. For example there are two columns A1 through A8 and B1 through B8 on worksheet one. Column B contains different values in each cell. Users will choose the value(s) they want from Column B by typing an X in the corresponding column A. For example, the user wants the value from cell B4 so types an "x" in cell A4 which then populates cell C1 in worksheet two. Then they select Cell B5 and I have adjusted the range in C2 to omit values from A1:B1 so cell C2 is populated with date from B5. I am using the formula below in cells C1-C8 . VLOOKUP("x",sheet1!A1:B8,2,FALSE) This works but if the user only selects one or two values, lets say B4, it populates all cells C1 through C8 with that value. Is there a way to populate cell C1 in this instance and leave all other cells blank? Would some sort of Macro work better for this or can it be one at all? -- Tim at Alliant |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup formula question
Macros are great and I use them all the time. But the problem with a
macro in this situation is that you have to make the sheet run the macro every time the user selects a new value, or make the user run the macro after choosing all desired values. Automatic is better, and yeah, there's a way to do it with formulae; it'll be a little more complicated than you hoped, though. Actually there's probably a number of ways, and you may be able to come up with a better after trying this, but here's how I'd probably approach it. First, forget about rows 1 through 8; eventually you're going to have one or more column-header rows, so let's just say your Sheet1 data is in rows 2 through, I dunno, 999. I mean, you don't have to use 999 as the upper limit, but that's what I'll use in this example. Now in Sheet2 set up some helper columns out at the right; I'll use X, Y and Z. In X1 put the value 1; that's the row right above the first data row in Sheet1 that your search function will be looking in, to start with. Then in Y2 put this formula: ="Sheet1!A"&X1+1&":A999" Since we just put a 1 in X1, this generates the character string "Sheet1!A2:A999". That's the range we want to search for the first 'x'. But so far Y2 is just a character string; next, in X2, combine a search formula and the INDIRECT function, which interprets that character string as a real range: =MATCH("x",INDIRECT(Y2),0)+X1 INDIRECT(Y2) just converts Y2 to a real range argument. The MATCH function is like VLOOKUP in that it searches the range in the second argument for the value in the first argument, only instead of returning a value from another cell out to the right, it just hands you the row number the match was found in. The row is relative to the range you gave it, so if, back on Sheet1, you put an 'x' in say row 3, that's the SECOND row of A2:A999, so the MATCH function returns not 3 but 2. That's why I added X1 back into the result, to turn it back into the real row number. Now watch what happens when you copy these two formulae downward. Y3 now displays the updated range, "Sheet1!A4:A999", which is where you want the next search to be done. If you've put only the one 'x' in Sheet1!A3, then X3 will do the next search starting in row 4 and find nothing, so for now X3 shows #N/A. Since that isn't a valid row number, Y4 shows #N/A too, and thus down the page. But go back to Sheet1 and put 'x' in some other row of column A; now the next set of values are filled in. And so on. Now in Sheet2 column X you have a list of the Sheet1 rows the user marked. Let's pull in the name from Sheet1!B:B; in col Z use this formula: =INDIRECT("Sheet1!B"&X2) In each row that has a valid row number in col X, this pulls the value from Sheet1!B<WhateverRow. When you get down past the number of Xs the user entered, Z just has more #N/As. Ok, you have your data. But #N/As are unsightly, so back in col A of Sheet2 let's make it more cosmetic. In A2 put =IF(ISERROR(Z2),"",Z2) This checks Z2; if it's #N/A then it counts as an error and you just display a blank character string; otherwise you display Z2 itself (the value in Sheet1). Copy all this downward and you have what you want. But as I warned you, it's more complex than it is pretty. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup formula question
Wow! that is complex, I think I understand how this would work but I am
getting a reference error in X2 when I enter the search/indirect function. I have entered the following for my spreadsheet =MATCH("x",INDIRECT(AB20),0)+AA19 AA19 would be X1 and AB20 would be Y2 in your example. Did I do something incorrectly? -- Tim at Alliant "Bob Bridges" wrote: Macros are great and I use them all the time. But the problem with a macro in this situation is that you have to make the sheet run the macro every time the user selects a new value, or make the user run the macro after choosing all desired values. Automatic is better, and yeah, there's a way to do it with formulae; it'll be a little more complicated than you hoped, though. Actually there's probably a number of ways, and you may be able to come up with a better after trying this, but here's how I'd probably approach it. First, forget about rows 1 through 8; eventually you're going to have one or more column-header rows, so let's just say your Sheet1 data is in rows 2 through, I dunno, 999. I mean, you don't have to use 999 as the upper limit, but that's what I'll use in this example. Now in Sheet2 set up some helper columns out at the right; I'll use X, Y and Z. In X1 put the value 1; that's the row right above the first data row in Sheet1 that your search function will be looking in, to start with. Then in Y2 put this formula: ="Sheet1!A"&X1+1&":A999" Since we just put a 1 in X1, this generates the character string "Sheet1!A2:A999". That's the range we want to search for the first 'x'. But so far Y2 is just a character string; next, in X2, combine a search formula and the INDIRECT function, which interprets that character string as a real range: =MATCH("x",INDIRECT(Y2),0)+X1 INDIRECT(Y2) just converts Y2 to a real range argument. The MATCH function is like VLOOKUP in that it searches the range in the second argument for the value in the first argument, only instead of returning a value from another cell out to the right, it just hands you the row number the match was found in. The row is relative to the range you gave it, so if, back on Sheet1, you put an 'x' in say row 3, that's the SECOND row of A2:A999, so the MATCH function returns not 3 but 2. That's why I added X1 back into the result, to turn it back into the real row number. Now watch what happens when you copy these two formulae downward. Y3 now displays the updated range, "Sheet1!A4:A999", which is where you want the next search to be done. If you've put only the one 'x' in Sheet1!A3, then X3 will do the next search starting in row 4 and find nothing, so for now X3 shows #N/A. Since that isn't a valid row number, Y4 shows #N/A too, and thus down the page. But go back to Sheet1 and put 'x' in some other row of column A; now the next set of values are filled in. And so on. Now in Sheet2 column X you have a list of the Sheet1 rows the user marked. Let's pull in the name from Sheet1!B:B; in col Z use this formula: =INDIRECT("Sheet1!B"&X2) In each row that has a valid row number in col X, this pulls the value from Sheet1!B<WhateverRow. When you get down past the number of Xs the user entered, Z just has more #N/As. Ok, you have your data. But #N/As are unsightly, so back in col A of Sheet2 let's make it more cosmetic. In A2 put =IF(ISERROR(Z2),"",Z2) This checks Z2; if it's #N/A then it counts as an error and you just display a blank character string; otherwise you display Z2 itself (the value in Sheet1). Copy all this downward and you have what you want. But as I warned you, it's more complex than it is pretty. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup question | Excel Discussion (Misc queries) | |||
VLOOKUP formula question | Excel Worksheet Functions | |||
vlookup question | Excel Worksheet Functions | |||
A question about VLOOKUP / any other helpful formula | Excel Worksheet Functions | |||
vlookup formula question | Excel Discussion (Misc queries) |