| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy function across autofilter
I want to copy the following function across only the cells displayed in my
autofilter ='[RDD Register.xls]Oban'!G59 But it's picking up the cells hidden and the numbers jump out of sequence on the filtered sheet. Is there any way to overcome this? D Dawson |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copy function across autofilter
Select the visible cells. Edit|Go To...| Click the Special... button.
Select Visible Cells Only. HTH Kostis Vezerides dd wrote: I want to copy the following function across only the cells displayed in my autofilter ='[RDD Register.xls]Oban'!G59 But it's picking up the cells hidden and the numbers jump out of sequence on the filtered sheet. Is there any way to overcome this? D Dawson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copy function across autofilter
Kostis,
I tried it and it pastes to the selected cells okay, but the link updates according to the row number rather than sequentially. For example, if my first row is 1 and the reference I want to copy is "B7" and the next row is row 5, I want the reference to update to "B8", but it updates to "B11" Regards Dylan "vezerid" wrote in message ups.com... Select the visible cells. Edit|Go To...| Click the Special... button. Select Visible Cells Only. HTH Kostis Vezerides dd wrote: I want to copy the following function across only the cells displayed in my autofilter ='[RDD Register.xls]Oban'!G59 But it's picking up the cells hidden and the numbers jump out of sequence on the filtered sheet. Is there any way to overcome this? D Dawson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copy function across autofilter
Ah see...
OK here is a try. Let us say that you filtered on column C:C being greater than 5, i.e. your condition is (C15) Then, in your formula you can use the following expression in lieu of, say, B7: OFFSET(Sheet1!B7,SUMPRODUCT(--($C$1:C15))-1,0) If you have filtered by more than one conditions (e.g. C15 and D1="a") you can use: OFFSET(Sheet1!B7,SUMPRODUCT(($C$1:C15)*($D$1:D1=" a"))-1,0) Without knowing the formula you are using I cannot be more specific. Post if you need more help. HTH Kostis dd wrote: Kostis, I tried it and it pastes to the selected cells okay, but the link updates according to the row number rather than sequentially. For example, if my first row is 1 and the reference I want to copy is "B7" and the next row is row 5, I want the reference to update to "B8", but it updates to "B11" Regards Dylan "vezerid" wrote in message ups.com... Select the visible cells. Edit|Go To...| Click the Special... button. Select Visible Cells Only. HTH Kostis Vezerides dd wrote: I want to copy the following function across only the cells displayed in my autofilter ='[RDD Register.xls]Oban'!G59 But it's picking up the cells hidden and the numbers jump out of sequence on the filtered sheet. Is there any way to overcome this? D Dawson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copy function across autofilter
='[RDD Register.xls]Oban'!G59
Is the formula I'm using. I'm trying to link to data in another book. But, when I copy my formula down the column it goes out of sync, because I have added additional rows. When I created the document, In column A:A I linked to cells in another book using ='[RDD Register.xls]Oban'!A01 etc. Then I added rows below each referenced row and added more data, as per the diagram. Now I want to import other columns from the source document using ='[RDD Register.xls]Oban'!G1 etc. and make them fit beside the previously referenced cells. It know it sounds complicated but it isn't. I have A B C D 001 00 Elevations 6-8-06 001 01 Windows in wrong location 6-9-06 001 02 Lintols missing 6-10-06 002 00 Plans 7-8-06 etc. The items in column A are irregular, in our example 001 has three items, 002 may have 2 items, 003 may have 5 items, 004 may have ten items. I want my referenced cells to be input where the rows B = "00", otherwise the numbering goes out of sync and it repeats the copied cells. Regards Dylan "vezerid" wrote in message ups.com... Ah see... OK here is a try. Let us say that you filtered on column C:C being greater than 5, i.e. your condition is (C15) Then, in your formula you can use the following expression in lieu of, say, B7: OFFSET(Sheet1!B7,SUMPRODUCT(--($C$1:C15))-1,0) If you have filtered by more than one conditions (e.g. C15 and D1="a") you can use: OFFSET(Sheet1!B7,SUMPRODUCT(($C$1:C15)*($D$1:D1=" a"))-1,0) Without knowing the formula you are using I cannot be more specific. Post if you need more help. HTH Kostis dd wrote: Kostis, I tried it and it pastes to the selected cells okay, but the link updates according to the row number rather than sequentially. For example, if my first row is 1 and the reference I want to copy is "B7" and the next row is row 5, I want the reference to update to "B8", but it updates to "B11" Regards Dylan "vezerid" wrote in message ups.com... Select the visible cells. Edit|Go To...| Click the Special... button. Select Visible Cells Only. HTH Kostis Vezerides dd wrote: I want to copy the following function across only the cells displayed in my autofilter ='[RDD Register.xls]Oban'!G59 But it's picking up the cells hidden and the numbers jump out of sequence on the filtered sheet. Is there any way to overcome this? D Dawson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copy function across autofilter
Then use almost the formula I suggested:
=OFFSET'[RDD Register.xls]Oban'!B7,SUMPRODUCT(--($B$1:B1="00"))-1,0) Does this help? Kostis dd wrote: ='[RDD Register.xls]Oban'!G59 Is the formula I'm using. I'm trying to link to data in another book. But, when I copy my formula down the column it goes out of sync, because I have added additional rows. When I created the document, In column A:A I linked to cells in another book using ='[RDD Register.xls]Oban'!A01 etc. Then I added rows below each referenced row and added more data, as per the diagram. Now I want to import other columns from the source document using ='[RDD Register.xls]Oban'!G1 etc. and make them fit beside the previously referenced cells. It know it sounds complicated but it isn't. I have A B C D 001 00 Elevations 6-8-06 001 01 Windows in wrong location 6-9-06 001 02 Lintols missing 6-10-06 002 00 Plans 7-8-06 etc. The items in column A are irregular, in our example 001 has three items, 002 may have 2 items, 003 may have 5 items, 004 may have ten items. I want my referenced cells to be input where the rows B = "00", otherwise the numbering goes out of sync and it repeats the copied cells. Regards Dylan "vezerid" wrote in message ups.com... Ah see... OK here is a try. Let us say that you filtered on column C:C being greater than 5, i.e. your condition is (C15) Then, in your formula you can use the following expression in lieu of, say, B7: OFFSET(Sheet1!B7,SUMPRODUCT(--($C$1:C15))-1,0) If you have filtered by more than one conditions (e.g. C15 and D1="a") you can use: OFFSET(Sheet1!B7,SUMPRODUCT(($C$1:C15)*($D$1:D1=" a"))-1,0) Without knowing the formula you are using I cannot be more specific. Post if you need more help. HTH Kostis dd wrote: Kostis, I tried it and it pastes to the selected cells okay, but the link updates according to the row number rather than sequentially. For example, if my first row is 1 and the reference I want to copy is "B7" and the next row is row 5, I want the reference to update to "B8", but it updates to "B11" Regards Dylan "vezerid" wrote in message ups.com... Select the visible cells. Edit|Go To...| Click the Special... button. Select Visible Cells Only. HTH Kostis Vezerides dd wrote: I want to copy the following function across only the cells displayed in my autofilter ='[RDD Register.xls]Oban'!G59 But it's picking up the cells hidden and the numbers jump out of sequence on the filtered sheet. Is there any way to overcome this? D Dawson |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copy function across autofilter
try to grab the whole of column A, click copy, go to Column G, paste
specialformulas on Column G you will have the linked cells ready made with formulas to be [cut n paste] shifted on any virgin column you need. just maybe.. -- ***** birds of the same feather flock together.. "dd" wrote: ='[RDD Register.xls]Oban'!G59 Is the formula I'm using. I'm trying to link to data in another book. But, when I copy my formula down the column it goes out of sync, because I have added additional rows. When I created the document, In column A:A I linked to cells in another book using ='[RDD Register.xls]Oban'!A01 etc. Then I added rows below each referenced row and added more data, as per the diagram. Now I want to import other columns from the source document using ='[RDD Register.xls]Oban'!G1 etc. and make them fit beside the previously referenced cells. It know it sounds complicated but it isn't. I have A B C D 001 00 Elevations 6-8-06 001 01 Windows in wrong location 6-9-06 001 02 Lintols missing 6-10-06 002 00 Plans 7-8-06 etc. The items in column A are irregular, in our example 001 has three items, 002 may have 2 items, 003 may have 5 items, 004 may have ten items. I want my referenced cells to be input where the rows B = "00", otherwise the numbering goes out of sync and it repeats the copied cells. Regards Dylan "vezerid" wrote in message ups.com... Ah see... OK here is a try. Let us say that you filtered on column C:C being greater than 5, i.e. your condition is (C15) Then, in your formula you can use the following expression in lieu of, say, B7: OFFSET(Sheet1!B7,SUMPRODUCT(--($C$1:C15))-1,0) If you have filtered by more than one conditions (e.g. C15 and D1="a") you can use: OFFSET(Sheet1!B7,SUMPRODUCT(($C$1:C15)*($D$1:D1=" a"))-1,0) Without knowing the formula you are using I cannot be more specific. Post if you need more help. HTH Kostis dd wrote: Kostis, I tried it and it pastes to the selected cells okay, but the link updates according to the row number rather than sequentially. For example, if my first row is 1 and the reference I want to copy is "B7" and the next row is row 5, I want the reference to update to "B8", but it updates to "B11" Regards Dylan "vezerid" wrote in message ups.com... Select the visible cells. Edit|Go To...| Click the Special... button. Select Visible Cells Only. HTH Kostis Vezerides dd wrote: I want to copy the following function across only the cells displayed in my autofilter ='[RDD Register.xls]Oban'!G59 But it's picking up the cells hidden and the numbers jump out of sequence on the filtered sheet. Is there any way to overcome this? D Dawson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
| reminder notifications in a column | Excel Discussion (Misc queries) | |||
| Date & Time | New Users to Excel | |||
| Copy Function | Excel Discussion (Misc queries) | |||
| Using IF function to copy. | Excel Worksheet Functions | |||
Hybrid Mode
