Paste damages formula in autofilter #3

That doesn't seem to work as the paste sends a #REF into the offset o
paste so the formula becomes =IF(OFFSET(#REF!,0,-1)>0,1,0) on the righ
which returns the #REF error.

Think solution would be easier to solve with a macro executin
everytime an update occurs maybe?.

There's got be a way to do it cause I have seen a worksheet where i
works and I don't believe it is likely it was done with macros.

(sidenote, database is a central one used to modify other worksheet
using macros. Each of these worksheets uses different sets of filters
The data pane on the right records subtotal and totals critical to th
filter. one set of fields in the data pane records the start pt and en
pt of where to copy from in the main database to the target worksheet
These start pt and end pts are calculated from a column next to col
which records the running total of 1's, so you see to modify col4 woul
require me to a lot of recoding to fix all my worksheets macros

--
shaw
-----------------------------------------------------------------------
shawb's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1420
View this thread: http://www.excelforum.com/showthread.php?threadid=26268

0
9/23/2004 7:28:13 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
461 Views

Similar Articles

[PageSpeed] 11

Hi
there should be an easier formula-based way. Still not 
sure about your file though. If you like, email me a 
sample file and I'll have a look at it this evening. Also 
describe the desired result in your email/file
email: frank[dot]kabel[at]freenet[dot]de

>-----Original Message-----
>
>That doesn't seem to work as the paste sends a #REF into 
the offset on
>paste so the formula becomes =IF(OFFSET(#REF!,0,-1)
>0,1,0) on the right
>which returns the #REF error.
>
>Think solution would be easier to solve with a macro 
executing
>everytime an update occurs maybe?.
>
>There's got be a way to do it cause I have seen a 
worksheet where it
>works and I don't believe it is likely it was done with 
macros.
>
>(sidenote, database is a central one used to modify other 
worksheets
>using macros. Each of these worksheets uses different 
sets of filters.
>The data pane on the right records subtotal and totals 
critical to the
>filter. one set of fields in the data pane records the 
start pt and end
>pt of where to copy from in the main database to the 
target worksheet,
>These start pt and end pts are calculated from a column 
next to col4
>which records the running total of 1's, so you see to 
modify col4 would
>require me to a lot of recoding to fix all my worksheets 
macros)
>
>
>-- 
>shawb
>----------------------------------------------------------
--------------
>shawb's Profile: http://www.excelforum.com/member.php?
action=getinfo&userid=14204
>View this thread: 
http://www.excelforum.com/showthread.php?threadid=262688
>
>.
>
0
frank.kabel (11126)
9/23/2004 9:15:25 AM
Reply:

Similar Artilces:

locking formulas
Is there a way I can lock my formulas so no one can change them when i send my spreadsheet out? I have about 50 cells with formulas in them in 4 or 5 columns and I need them all locked. Thank you for any suggestions. -- speary ------------------------------------------------------------------------ speary's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24959 View this thread: http://www.excelforum.com/showthread.php?threadid=389055 Select all cells that DO NOT require to be locked, go to Format>Cells>Protection and remove the check mark beside "L...

coping formula
I am trying to copy a formula down a column. When I copy the formula down I want the cell to be blank until i fill out the cells to give me my balance. My three columns are Debit / credit / balance. My formual reads : f7(balance column)+e8(credit column)-d8 (debit column) when i copy the formula down form my balance column it gives me the balance in every cell. I would like for it to be blank until i fill in my debits and credits. I hope you understand the way i wrote this thanks much -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-new/200803/1 You ...

Paste special treats cells as a picture
I just got a new computer, and a newer version of excel, which is great, but whenever I try to copy from one file to another, my paste special function treats the copied cells as a picture. Instead of the normal paste special popup, where it asks me how I want the data to come out, I get a different popup that shows the source as a "Microsoft Office Excel Worksheet Object." It asks me what format I want the picture to come out as, and gives me a list of different file formats. If I try to just paste a link, it puts a picture of the other file on the new one. I talked to so...

Formula: What am I Doing Wrong
4 A 3 A 2 B 1 A 4 $6.00 3 $6.00 2 $2.00 1 $6.00 In the first example I want to know how many instances of A there are in column B multiplied by the number in Column A. Answer would be 8. Formula: sumproduct((a1:a4)*(b1:b4="A")) But if I want to find out how many instances of $6.00 there are instead of A, I'm stumped. I get either 0, or 48. Must be something simple. It is better to use =SUMIF(B:B,"A",A:A) than sumproduct((a1:a4)*(b1:b4="A")) I assume you mean =COUTIF(A:A,6) -- HTH Bob (there's no email, no snai...

Copy and paste formula; Value pastes instead
Excel 2002 I've tried starting several worksheets, checking formats, pasting special (formula only). For some reason the formula actually copies OK by looking at the formula bar, but the value in the cell is identical to the value of the copied cell.Incorrect. Tried even the simplest formulas with no joy. BK ...

Is there an Excel formula to round a date to the end of the month
User enters a specific date, I'd like to round that entry to the end of the month With a date in A1: =DATE(YEAR(A1),MONTH(A1)+1,0) step into the next month and then back one day. -- Gary''s Student - gsnu201003 "Siralec" wrote: > User enters a specific date, I'd like to round that entry to the end of the > month Another way... This requires the Analysis ToolPak add-in be installed for Excel versions prior to Excel 2007. A1 = some date =EOMONTH(A1,0) Format as Date -- Biff Microsoft Excel MVP "Siralec" &...

how do i switch colum 3 and colum 34 ex.
Suggestion - ask your question in the message part of the form - not the subject part! Try inserting a column before column 3(C??) and column 34(AH??). Highlight column 3(C??) and drag it to the new column by column 34(AH??). Highlight the other column and drag it to the new column by column 3. Delete the two old columns. Note that the positions of the columns will change when you add the two new onex. I hope this helps. I don't know what you mean by 34 ex. Carole O "cocochef" wrote: > Select column 3. Hold SHIFT key and move mouse pointer down to edge of ...

Clipboard pasting
I copy something to the clipboard using Publisher 2007 and the computer tells me it is in the clipboard; however, when I go to paste that information into another document, Publisher acts like it is not there. I then need to close the document I wish to paste it into, reopen it, and then, like magic, there is the information in the clipboard and I am able to paste it. UGH!!!!!! -- Ginger Christenson Open the Office Clipboard in Publisher. Edit, Office Clipboard. What program are you pasting to? If it is an Office program, open the Office Clipboard in that program too. -- Mary Sauer ...

copy formula only
When I copy a formula down the column the value gets repeated all the way down too . How can I only paste the formula so as the cells stay blank until related cells are filled in to make the calculations. Thanks Steve if(relatedcell="","",relatedcellformula) -- Don Guillett SalesAid Software donaldb@281.com "Steve Fletcher" <steve.fletcher1@blueyonder.co.uk> wrote in message news:Oqetb.63$Mw1.40@news-binary.blueyonder.co.uk... > When I copy a formula down the column the value gets repeated all the way > down too . How can I only paste the formul...

Office for Mac #3
Version: 2004 Operating System: Mac OS X 10.5 (Leopard) Processor: intel I'm trying the Office Test drive - if I then decide to buy, will all my data stay put (e.g. Entourage e-mails etc.)? I'm importing them from my old PC and don't want to do it twice! Your Entourage data is stored in the Microsoft User Data folder, which you can find in ~/Documents. That folder is left untouched if you decide to remove Office; remember, you will need to remove the Test Drive prior to installing the full version of Office 2004. If you decide to buy Office 2008, the installer will do that for yo...

formula to get sheet name
hi, can a formula retreive a worksheet name or does it need to be macro based? thanks, N.S. Hi Nigel, This formula that takes care of the possible situation whereby you have only one sheet in the workbook and its name is the same as the workbook's. Note: CELL("filename") will only work if the file has been saved at least once and if the file is opened in a different language system, the argument "filename" will need to be changed manually to the corresponding word (e.g. in Spanish "nombrearchivo"): =LOOKUP(REPT("z",255),SUBSTITUTE(MID(CE...

Merge Records issue in 3.0
The regular CRM user is having issue merging records (Contacts specifically) in 3.0. Gets the error " This login does not have the permissions to merge.............." this happens in web client as well as Laptop client. Has anyone encountered this issue? Any pointers will be helpful. You need to enable merge privilege for a role so users with that role can merge records. The merge privilege is listed under Business Management tab for that role (under misc. privileges section in that tab) -- Jagan Peri Microsoft Business Solutions CRM This posting is provided "AS IS"...

Formula returned in vlookup function
I've used vlookup for a while now in the office 2003. Just converted to office 2007. Now when I do a vlookup I get the formula displayed in my current cell instead of a returned value. I can't figure our what I'm doing wrong. Can someone help? Thanks bmc You've probably got a tick in the wrong place: Try Office Button (top left hand corner) Excel Options (bottom line) Click on advanced Scroll down to Display options for this worksheet and untick 'Show formulas in cells instead of their calculated results Click OK Hope this helps "87vette" wrote...

Formulas for birth year and age in user defined fields
I'm using outlook 2002. I want to create 2 user-defined fields in my contacts, but am having trouble figuring out how to do it: 1. Year of birth field, based on the value entered in the birthday field - I created a new user defined field, with the Type set as Formula, and the formula set as: Year ([Birthday]). The year is displayed, but formatted as a number (1,957) rather than a year (1957). How can I get the number to be displayed without the comma? 2. Age, based on difference between birthday and present date - Is there a simple formula to accomplish this? I haven't been ab...

badmail directory #3
Hi- A couple of quick questions. I have a new Exchange 2003 server w/ about 50 users on it. After about a month I have yet to get anything delivered to the badmail directory. I do have IMF running on this server. The badmail directory is setup and in the correct place. Any ideas? Also are there any utilities (other than perfmon) that will show stats about how many emails are going in/out of the server, how many emails to specific users, smtpd errors that would show mail sent to incorrect user names etc? Imail has an analyzer tool that shows a lot of this info. Thanks! -Glen Do you ha...

Is this possible #3
Hi, How can I block users from adding accounts to outlook 2003? I have some users who, fof some reason, thinks they are the administrators. They are adding their personal hotmails accounts, pop3 and http accounts. How can I idsable this so that they can edit account settings in outlook 2003? Thanks Hi Chris, you can always block POP and IMAP protocols on fw... -- Regards, Sasa Milovanovic MCSE:Messaging sasa.milovanovic(at)exchangemaster.net "Chris" <Chris@discussions.microsoft.com> wrote in message news:047CAA4A-3D05-490A-AB40-F17A99C8C233@microsoft.com... > Hi, &...

Text data
I am entering text data into a spreadsheet and I need to know how to calculate the number of times particular words/phrases appear in each column. I am very new to excel, so any help would be appreciated! =COUNTIF(A1:A1000,"word") replace A1:A1000 with your range and word with the word you want to count -- Regards, Peo Sjoblom "excelbeginner" <excelbeginner@discussions.microsoft.com> wrote in message news:FE55CF40-F3B6-42F3-918F-D200690758F6@microsoft.com... >I am entering text data into a spreadsheet and I need to know how to > calculate the nu...

"Paste Special" with the right click mouse
I would like to have the option of "Paste Special" with the right click mouse in all microsoft office suite. Thanks, ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=de847738-df56-4...

Pasting formulas advances cell reference
Hi When i paste special from one cell into another, it advances the cell reference and i just want an exact match i.e. A1 formula ='Feb 2010'!C18 but when i paste it into another cell it pastes it as ='Feb 2010'!D18 The C changes to D, how do i stop this happening? Thanks for any help in advance Derek Use absolute referencing. Below are the different reference styles. A1 Relative referencing. Both column and row will change if you copy or drag the formula. $A1 The column reference is fixed and will not change A$1 The row reference is fixed and wil...

Formula Bar
Have cells custom formatted for seconds :ss but in the formala bar shows and invalid date and time, is there a way to make the formula bar show the correct value of the cell i.e. typed in cell - 55 for 55 seconds but the formula bar showing content as: 1900-02-24 12:00:00 AM for me to get it to display as :55 have to enter it as :55 but then can't use it for a calculation if format it as mm:ss enter 55 shows as 00:00 in the cell but in the formula bar shows content as: 1900-02-24 12:00:00 AM need cells to display a seconds :55 but will need to use the cells for oth...

Copy/Paste loop
I have a form on one sheet in excel, on the next sheet i have mad different coloums with different headings. I want the data from th form to be copyed to each heading e.g. name in form is copyed to unde the coloum with title name. So each time a user fills in there details he/she clicks submit an there data is copyed to the next page, like a small database. Is there a macro that can do this as i keep getting different error each time. I want this to continue in a loop Please help!!:confused -- Message posted from http://www.ExcelForum.com This might get you started: Option Explicit Opti...

Exchange 5.5 Migration #3
I have an exchange 5.5 server and am just about to migrate it to Exchange 2003 enterprise Ed. All the NT account associations are updated however for ADC to work I must refresh the "Last logged on by" value in Exchange 5.5. manager. I need to update the "Last logged on by" field automatically or else log into over 400 mailboxes. Can you help? -- Richard ...

Simple question #3
Dear all, Simple question I suppose: What are the valid characters for an SMTP address and what is the maximum number of characters allowed? Thanks in advance, Olivier From RFC 821 (SMTP Specifications) 4.5.3. SIZES There are several objects that have required minimum maximum sizes. That is, every implementation must be able to receive objects of at least these sizes, but must not send objects larger than these sizes. **************************************************** * * ...

Curly Brackets In Formulas
I'm trying to copy formulas from one spreadsheet to another similar to the one below, but the curly brackets disappear, rendering the formulas inoperable (in the original document, they compare values in one range of cells against another, count the cells that match as 1, and adds them up). Adding the curly brackets manually just converts the cells to text. How do I make these formulas work in the new document? I'm using Excel 2000. TIA. ~Charie G. {=SUM(COUNTIF(B5:F5,$B$2:$F$2))} Hi Charlie the {} indicate that the formula is an array formula which is entered in a cell using a c...

Delivery Error 5.3.5 Message bounces between two servers
Hello When sending an email to a particular address that I know works it does not go through and I get this error: A configuration error in the e-mail system caused the message to bounce between two servers or to be forwarded between two recipients. Contact your administrator. I have searched the knowledge base and found article kb31667 which descibes this problem excatly. However after doing what it told me I still have the problem. I have tested the recepient email address on dnsstuff.com and it works fine. Does anyone know why this is happening and only seems to be to this one ...