Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying functions without changing most variables in it
Let's say I have an annoyingly long function in D1 and I want it in D2, D3,
D4...D7. And in my function are all sorts of variables and refrences. Is it possible to copy the function to the remaining cells while only changing a few select variables in the functions? Specifically, I'd like for it to change the "col_index_num" in my Vlookups. Here is the function that I'd like to copy: =IF(B1=Sheet2!A2,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE), IF(B1=Sheet2!A3,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE), IF(B1=Sheet2!A4,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE), IF(B1=Sheet2!A5,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE), IF(B1=Sheet2!A6,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE), IF(B1=Sheet2!A7,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE), IF(B1=Sheet!A8,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE),"F alse"))))))) The part I'd like to change is the repeating 2's. In cell D2 I'd like them to be 3's. In D3 I'd like them to be 4's. And so on and so forth. Any help would be appreciated. Or if you know of a way to condense the above function, that'd be great too. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying functions without changing most variables in it
Too late for me to test anything but I think I am on the right track:
If this =IF(B1=Sheet2!A2,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE). .. is in D1, then you could replace the 2 by ROW()+1 =IF(B1=Sheet2!A2,VLOOKUP(B1,Sheet2!A1:G8,ROW()+1,F ALSE)... As the formula is in D1 (that is, it is in row number 1), ROW()+1 will return 1+1, giving 2 as required. So when you copy it down the column, ROW()+1 becomes 3, 4, .... best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Ruleroftheblind" wrote in message ... Let's say I have an annoyingly long function in D1 and I want it in D2, D3, D4...D7. And in my function are all sorts of variables and refrences. Is it possible to copy the function to the remaining cells while only changing a few select variables in the functions? Specifically, I'd like for it to change the "col_index_num" in my Vlookups. Here is the function that I'd like to copy: =IF(B1=Sheet2!A2,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE), IF(B1=Sheet2!A3,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE), IF(B1=Sheet2!A4,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE), IF(B1=Sheet2!A5,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE), IF(B1=Sheet2!A6,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE), IF(B1=Sheet2!A7,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE), IF(B1=Sheet!A8,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE),"F alse"))))))) The part I'd like to change is the repeating 2's. In cell D2 I'd like them to be 3's. In D3 I'd like them to be 4's. And so on and so forth. Any help would be appreciated. Or if you know of a way to condense the above function, that'd be great too. Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying functions without changing most variables in it
Hi,
Let shrink this formula to an array: =IF(OR(B$1=Sheet2!A$2:A$7),VLOOKUP(B$1,Sheet2!A$1: G$8,ROW()+1,FALSE),"") To make this an array enter it by pressing Shift+Ctrl+Enter. Yes, only one vlookup. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Ruleroftheblind" wrote: Let's say I have an annoyingly long function in D1 and I want it in D2, D3, D4...D7. And in my function are all sorts of variables and refrences. Is it possible to copy the function to the remaining cells while only changing a few select variables in the functions? Specifically, I'd like for it to change the "col_index_num" in my Vlookups. Here is the function that I'd like to copy: =IF(B1=Sheet2!A2,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE), IF(B1=Sheet2!A3,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE), IF(B1=Sheet2!A4,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE), IF(B1=Sheet2!A5,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE), IF(B1=Sheet2!A6,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE), IF(B1=Sheet2!A7,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE), IF(B1=Sheet!A8,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE),"F alse"))))))) The part I'd like to change is the repeating 2's. In cell D2 I'd like them to be 3's. In D3 I'd like them to be 4's. And so on and so forth. Any help would be appreciated. Or if you know of a way to condense the above function, that'd be great too. Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying functions without changing most variables in it
Enter this formula in D1 and copy down to D7:
=IF(COUNTIF(Sheet2!A$2:A$8,B$1),VLOOKUP(B$1,Sheet2 !A$1:G$8,ROWS(D$1:D2),0),"") -- Biff Microsoft Excel MVP "Ruleroftheblind" wrote in message ... Let's say I have an annoyingly long function in D1 and I want it in D2, D3, D4...D7. And in my function are all sorts of variables and refrences. Is it possible to copy the function to the remaining cells while only changing a few select variables in the functions? Specifically, I'd like for it to change the "col_index_num" in my Vlookups. Here is the function that I'd like to copy: =IF(B1=Sheet2!A2,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE), IF(B1=Sheet2!A3,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE), IF(B1=Sheet2!A4,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE), IF(B1=Sheet2!A5,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE), IF(B1=Sheet2!A6,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE), IF(B1=Sheet2!A7,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE), IF(B1=Sheet!A8,VLOOKUP(B1,Sheet2!A1:G8,2,FALSE),"F alse"))))))) The part I'd like to change is the repeating 2's. In cell D2 I'd like them to be 3's. In D3 I'd like them to be 4's. And so on and so forth. Any help would be appreciated. Or if you know of a way to condense the above function, that'd be great too. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to dealing with functions with 30+ variables? | Excel Worksheet Functions | |||
constructing (complex) variables with worksheet functions | Excel Discussion (Misc queries) | |||
LOGICAL FUNCTIONS WITH MULTIPLE VARIABLES | Excel Worksheet Functions | |||
Can I use variables for workheet name references in Excel functions? | Excel Discussion (Misc queries) | |||
Can I use variables for workheet name references in Excel functions? | Excel Worksheet Functions |