Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can this formula be placed in VBA please.
Hello from Steved
=IF(ISNUMBER(MATCH(--B2,Staff!$A$2:$A$2000,0)),VLOOKUP(--B2,Staff!$A$2:$C$2000,2,0)&CHAR(10)&VLOOKUP(--B2,Staff!$A$2:$C$2000,3,0),"") Every day I have to copy the above formula to a new worksheet ( 700 Rows ) If the above is possible I will use the same worksheet every day, just updating the previous day Worksheet, hence I will nolonger have to input the formula.. Thankyou. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can this formula be placed in VBA please.
Copying this formula down 700 rows shouldn't be that time consuming.
Here's a way to do it quickly. 1) Copy and paste the formula into the top cell of the column you're copying the formula to (say E1). 2) Go to cell A1 and hit CTRL + down arrow. You should be at the bottom of your data (row 700). 3) Navigate to cell E701 and enter a period. 4) Hit ctrl+ up arrow. You should now be at E1. 5) Select E1, then SHIFT + CTRL + DOWN ARROW 6) CTRL +D 7) Hit enter to calculate. Practice and this should take all of 5 seconds. You could also record all these steps in a macro and assign it to a button. "Steved" wrote: Hello from Steved =IF(ISNUMBER(MATCH(--B2,Staff!$A$2:$A$2000,0)),VLOOKUP(--B2,Staff!$A$2:$C$2000,2,0)&CHAR(10)&VLOOKUP(--B2,Staff!$A$2:$C$2000,3,0),"") Every day I have to copy the above formula to a new worksheet ( 700 Rows ) If the above is possible I will use the same worksheet every day, just updating the previous day Worksheet, hence I will nolonger have to input the formula.. Thankyou. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can this formula be placed in VBA please.
Thankyou.
"Dave F" wrote: Copying this formula down 700 rows shouldn't be that time consuming. Here's a way to do it quickly. 1) Copy and paste the formula into the top cell of the column you're copying the formula to (say E1). 2) Go to cell A1 and hit CTRL + down arrow. You should be at the bottom of your data (row 700). 3) Navigate to cell E701 and enter a period. 4) Hit ctrl+ up arrow. You should now be at E1. 5) Select E1, then SHIFT + CTRL + DOWN ARROW 6) CTRL +D 7) Hit enter to calculate. Practice and this should take all of 5 seconds. You could also record all these steps in a macro and assign it to a button. "Steved" wrote: Hello from Steved =IF(ISNUMBER(MATCH(--B2,Staff!$A$2:$A$2000,0)),VLOOKUP(--B2,Staff!$A$2:$C$2000,2,0)&CHAR(10)&VLOOKUP(--B2,Staff!$A$2:$C$2000,3,0),"") Every day I have to copy the above formula to a new worksheet ( 700 Rows ) If the above is possible I will use the same worksheet every day, just updating the previous day Worksheet, hence I will nolonger have to input the formula.. Thankyou. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can this formula be placed in VBA please.
Not sure if this is appropriate for your situation or not, but if these
sheets are to all be in the same workbook, you might look into simply using the Edit | Copy/Move Sheet function. You could copy a worksheet from one day that already has all of these formulas in it and then just remove the entries (in column B?) from the previous day's sheet? "Steved" wrote: Thankyou. "Dave F" wrote: Copying this formula down 700 rows shouldn't be that time consuming. Here's a way to do it quickly. 1) Copy and paste the formula into the top cell of the column you're copying the formula to (say E1). 2) Go to cell A1 and hit CTRL + down arrow. You should be at the bottom of your data (row 700). 3) Navigate to cell E701 and enter a period. 4) Hit ctrl+ up arrow. You should now be at E1. 5) Select E1, then SHIFT + CTRL + DOWN ARROW 6) CTRL +D 7) Hit enter to calculate. Practice and this should take all of 5 seconds. You could also record all these steps in a macro and assign it to a button. "Steved" wrote: Hello from Steved =IF(ISNUMBER(MATCH(--B2,Staff!$A$2:$A$2000,0)),VLOOKUP(--B2,Staff!$A$2:$C$2000,2,0)&CHAR(10)&VLOOKUP(--B2,Staff!$A$2:$C$2000,3,0),"") Every day I have to copy the above formula to a new worksheet ( 700 Rows ) If the above is possible I will use the same worksheet every day, just updating the previous day Worksheet, hence I will nolonger have to input the formula.. Thankyou. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |