#### Write formula for simple copy and paste to another cell

```I have a worksheet that contains hundreds of columns. Each group of three are
related to each other (Cols A-C, D-F, etc...).
I have this formula that works well for columns A-C

=IF(NOT(ISBLANK('Page 5 Counts'!B2)),VLOOKUP('Page 5 Counts'!B2,'Color
Key'!\$A\$1:\$B\$87,2,FALSE),IF(AND(COLUMN()=COLUMN(\$B\$1),ROW()=1),IF(\$A\$2=\$A\$1,"",1),IF(AND(COLUMN()=COLUMN(\$C\$1),ROW()=1),\$B\$1,IF(AND(COLUMN()=COLUMN(\$B\$2),ROW()=2),IF(\$A\$2=\$A\$1,2,1),IF(AND(COLUMN()=COLUMN(\$C\$2),ROW()=2),IF(\$A\$2=\$A\$3,"",IF(\$A\$2=\$A\$1,\$B\$2,1)),IF(AND(COLUMN()=COLUMN(\$B\$3),ROW()=3),IF(\$A\$3=\$A\$2,\$B\$2+1,1),IF(AND(COLUMN()=COLUMN(\$C\$3),ROW()=3),IF(\$A\$3=\$A\$4,"",IF(\$A\$3=\$A\$2,\$B\$3,1)),IF(COLUMN()=COLUMN(B2),IF(A2=A1,B1+1,1),IF(COLUMN()=COLUMN(C2),IF(A2=A3,"",IF(A2=A1,B2,1)))))))))))

I know it is rather thick and probably difficult to read through, but that
is my formula and it works exactly how I want......sort of.
My problem is two-fold and I hope I say it correctly....

Column A contains a number (ex. 351), Column B counts the number of
occurrences each particular instances is. Column C shows the last number of
that occurrence. (Will provide example at the bottom)

One, I need to be able to put this in cell A1 and copy it over and down to a
variable number of columns and rows. Currently, I have to copy it exactly as
shown above (not drag/copy) into cell B2, C2, B3, and C3, then I can do a
drag copy of B3:C3 all the way down to the bottom of my data.

Two, I can't use it in column A or in Row 1. (the cell references to col A
and to row 1 causes #REF!). But I need it to know to look in cell A1 (or D1,
or G1, etc...)

My ultimate goal really is to just have one formula that I can put into cell
A1 and can drag copy from top to bottom, left to right, without any manual
changes.

Worksheet example:

Column A       Column B        Column C
722                 1                     1
351                 1
351                 2
351                 3                     3
879                 1
879                 2                     2
151                 1                     1
744                 1                     1

Columns D-F would look similar but would have different numbers (Col A) in a
different order. They are unrelated to Columns A-C. They are their own group
of three, as would be G-I and so on.
```
 0
Utf
1/11/2010 7:40:01 PM
excel.worksheet.functions 4936 articles. 2 followers.

1 Replies
1038 Views

Similar Articles

[PageSpeed] 54

```Disregard, I have found a solution.
For those who are curious, this is my new formula, which I am able to put
into cell A1 and drag copy all the way over and down through my entire sheet.
It is still a little messy and I plan to go back and see about simplifying
it, but for now, it works....

=IF(NOT(ISBLANK('Page 5 Counts'!A1)),VLOOKUP('Page 5 Counts'!A1,'Color
Key'!\$A\$1:\$B\$87,2,FALSE),IF(AND(COLUMN()=IF(ISNA(VLOOKUP(COLUMN(),'Color
Key'!\$E\$1:\$E\$100,1,FALSE)),COLUMN()-1,VLOOKUP(COLUMN(),'Color
Key'!\$E\$1:\$E\$100,1,FALSE)),ROW()=1),IF(INDIRECT("R2C"&VLOOKUP(COLUMN(),'Color
Key'!\$E\$1:\$F\$100,2,FALSE),FALSE)=INDIRECT("R1C"&VLOOKUP(COLUMN(),'Color
Key'!\$E\$1:\$F\$100,2,FALSE),FALSE),"",1),IF(AND(COLUMN()=IF(ISNA(VLOOKUP(COLUMN(),'Color
Key'!\$G\$1:\$H\$100,1,FALSE)),COLUMN()-1,VLOOKUP(COLUMN(),'Color
Key'!\$G\$1:\$H\$100,1,FALSE)),ROW()=1),IF(NOT(INDIRECT("R2C"&VLOOKUP(COLUMN(),'Color
Key'!\$G\$1:\$H\$100,2,FALSE)-1,FALSE)=INDIRECT("R1C"&VLOOKUP(COLUMN(),'Color
Key'!\$G\$1:\$H\$100,2,FALSE)-1,FALSE)),INDIRECT("R1C"&VLOOKUP(COLUMN(),'Color
Key'!\$G\$1:\$H\$100,2,FALSE),FALSE),""),IF(AND(COLUMN()=IF(ISNA(VLOOKUP(COLUMN(),'Color
Key'!\$E\$1:\$E\$100,1,FALSE)),COLUMN()-1,VLOOKUP(COLUMN(),'Color
Key'!\$E\$1:\$E\$100,1,FALSE)),ROW()=2),IF(INDIRECT("R2C"&VLOOKUP(COLUMN(),'Color
Key'!\$E\$1:\$F\$100,2,FALSE),FALSE)=INDIRECT("R1C"&VLOOKUP(COLUMN(),'Color
Key'!\$E\$1:\$F\$100,2,FALSE),FALSE),2,1),IF(AND(COLUMN()=IF(ISNA(VLOOKUP(COLUMN(),'Color
Key'!\$G\$1:\$H\$100,1,FALSE)),COLUMN()-1,VLOOKUP(COLUMN(),'Color
Key'!\$G\$1:\$H\$100,1,FALSE)),ROW()=2),IF(NOT(INDIRECT("R2C"&VLOOKUP(COLUMN(),'Color
Key'!\$G\$1:\$H\$100,2,FALSE)-1,FALSE)=INDIRECT("R3C"&VLOOKUP(COLUMN(),'Color
Key'!\$G\$1:\$H\$100,2,FALSE)-1,FALSE)),INDIRECT("R2C"&VLOOKUP(COLUMN(),'Color
Key'!\$G\$1:\$H\$100,2,FALSE),FALSE),""),IF(AND(COLUMN()=IF(ISNA(VLOOKUP(COLUMN(),'Color
Key'!\$E\$1:\$E\$100,1,FALSE)),COLUMN()-1,VLOOKUP(COLUMN(),'Color
Key'!\$E\$1:\$E\$100,1,FALSE)),ROW()=3),IF(INDIRECT("R3C"&VLOOKUP(COLUMN(),'Color
Key'!\$E\$1:\$F\$100,2,FALSE),FALSE)=INDIRECT("R2C"&VLOOKUP(COLUMN(),'Color
Key'!\$E\$1:\$F\$100,2,FALSE),FALSE),SUM(INDIRECT("R2C"&VLOOKUP(COLUMN(),'Color
Key'!\$E\$1:\$F\$100,1,FALSE),FALSE),1),1),IF(AND(COLUMN()=IF(ISNA(VLOOKUP(COLUMN(),'Color
Key'!\$G\$1:\$H\$100,1,FALSE)),COLUMN()-1,VLOOKUP(COLUMN(),'Color
Key'!\$G\$1:\$H\$100,1,FALSE)),ROW()=3),IF(NOT(INDIRECT("R3C"&VLOOKUP(COLUMN(),'Color
Key'!\$G\$1:\$H\$100,2,FALSE)-1,FALSE)=INDIRECT("R4C"&VLOOKUP(COLUMN(),'Color
Key'!\$G\$1:\$H\$100,2,FALSE)-1,FALSE)),IF(INDIRECT("R3C"&VLOOKUP(COLUMN(),'Color
Key'!\$G\$1:\$H\$100,2,FALSE)-1,FALSE)=INDIRECT("R2C"&VLOOKUP(COLUMN(),'Color
Key'!\$G\$1:\$H\$100,2,FALSE)-1,FALSE),INDIRECT("R3C"&VLOOKUP(COLUMN(),'Color
Key'!\$G\$1:\$H\$100,2,FALSE),FALSE),1),""),IF(AND(COLUMN()=IF(ISNA(VLOOKUP(COLUMN(),'Color
Key'!\$E\$1:\$E\$100,1,FALSE)),COLUMN()-1,VLOOKUP(COLUMN(),'Color
Key'!\$E\$1:\$E\$100,1,FALSE)),ROW()>3),IF(INDIRECT("R"&ROW()&"C"&VLOOKUP(COLUMN(),'Color
Key'!\$E\$1:\$F\$100,2,FALSE),FALSE)=INDIRECT("R"&ROW()-1&"C"&VLOOKUP(COLUMN(),'Color
Key'!\$E\$1:\$F\$100,2,FALSE),FALSE),SUM(INDIRECT("R"&ROW()-1&"C"&VLOOKUP(COLUMN(),'Color
Key'!\$E\$1:\$F\$100,1,FALSE),FALSE),1),1),IF(AND(COLUMN()=IF(ISNA(VLOOKUP(COLUMN(),'Color
Key'!\$G\$1:\$H\$100,1,FALSE)),COLUMN()-1,VLOOKUP(COLUMN(),'Color
Key'!\$G\$1:\$H\$100,1,FALSE)),ROW()>3),IF(INDIRECT("R"&ROW()&"C"&VLOOKUP(COLUMN(),'Color
Key'!\$G\$1:\$H\$100,2,FALSE)-1,FALSE)=INDIRECT("R"&ROW()+1&"C"&VLOOKUP(COLUMN(),'Color Key'!\$G\$1:\$H\$100,2,FALSE)-1,FALSE),"",
IF(AND(COLUMN()=IF(ISNA(VLOOKUP(COLUMN(),'Color
Key'!\$G\$1:\$H\$100,1,FALSE)),COLUMN()-1,VLOOKUP(COLUMN(),'Color
Key'!\$G\$1:\$H\$100,1,FALSE)),ROW()>3),IF(INDIRECT("R"&ROW()&"C"&VLOOKUP(COLUMN(),'Color
Key'!\$G\$1:\$H\$100,2,FALSE)-1,FALSE)=INDIRECT("R"&ROW()-1&"C"&VLOOKUP(COLUMN(),'Color
Key'!\$G\$1:\$H\$100,2,FALSE)-1,FALSE),INDIRECT("R"&ROW()&"C"&VLOOKUP(COLUMN(),'Color Key'!\$G\$1:\$H\$100,1,FALSE)-1,FALSE),1))))))))))))

"Kalffiend" wrote:

> I have a worksheet that contains hundreds of columns. Each group of three are
> related to each other (Cols A-C, D-F, etc...).
> I have this formula that works well for columns A-C
>
> =IF(NOT(ISBLANK('Page 5 Counts'!B2)),VLOOKUP('Page 5 Counts'!B2,'Color
> Key'!\$A\$1:\$B\$87,2,FALSE),IF(AND(COLUMN()=COLUMN(\$B\$1),ROW()=1),IF(\$A\$2=\$A\$1,"",1),IF(AND(COLUMN()=COLUMN(\$C\$1),ROW()=1),\$B\$1,IF(AND(COLUMN()=COLUMN(\$B\$2),ROW()=2),IF(\$A\$2=\$A\$1,2,1),IF(AND(COLUMN()=COLUMN(\$C\$2),ROW()=2),IF(\$A\$2=\$A\$3,"",IF(\$A\$2=\$A\$1,\$B\$2,1)),IF(AND(COLUMN()=COLUMN(\$B\$3),ROW()=3),IF(\$A\$3=\$A\$2,\$B\$2+1,1),IF(AND(COLUMN()=COLUMN(\$C\$3),ROW()=3),IF(\$A\$3=\$A\$4,"",IF(\$A\$3=\$A\$2,\$B\$3,1)),IF(COLUMN()=COLUMN(B2),IF(A2=A1,B1+1,1),IF(COLUMN()=COLUMN(C2),IF(A2=A3,"",IF(A2=A1,B2,1)))))))))))
>
> I know it is rather thick and probably difficult to read through, but that
> is my formula and it works exactly how I want......sort of.
> My problem is two-fold and I hope I say it correctly....
>
> Column A contains a number (ex. 351), Column B counts the number of
> occurrences each particular instances is. Column C shows the last number of
> that occurrence. (Will provide example at the bottom)
>
> One, I need to be able to put this in cell A1 and copy it over and down to a
> variable number of columns and rows. Currently, I have to copy it exactly as
> shown above (not drag/copy) into cell B2, C2, B3, and C3, then I can do a
> drag copy of B3:C3 all the way down to the bottom of my data.
>
> Two, I can't use it in column A or in Row 1. (the cell references to col A
> and to row 1 causes #REF!). But I need it to know to look in cell A1 (or D1,
> or G1, etc...)
>
> My ultimate goal really is to just have one formula that I can put into cell
> A1 and can drag copy from top to bottom, left to right, without any manual
> changes.
>
> Worksheet example:
>
> Column A       Column B        Column C
>    722                 1                     1
>    351                 1
>    351                 2
>    351                 3                     3
>    879                 1
>    879                 2                     2
>    151                 1                     1
>    744                 1                     1
>
> Columns D-F would look similar but would have different numbers (Col A) in a
> different order. They are unrelated to Columns A-C. They are their own group
> of three, as would be G-I and so on.
```
 0
Utf
1/12/2010 9:10:01 PM
 Reply:

Similar Artilces:

simple diplay
Hi All I need a formula for if A1 is greater than B1 or if A1 is equal to 0, then �OK�, otherwise display �Not OK� Simple but I cant work it out please help Andrew -- koba ------------------------------------------------------------------------ koba's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28639 View this thread: http://www.excelforum.com/showthread.php?threadid=502348 Hi, =IF(OR(A1>B1,A1=0),"OK","NOT OK") Regards Govind. koba wrote: > Hi All > > I need a formula for if A1 is greater than B1 or if A1 is equal...

Using insert to paste a row--how done in Excel 2007
Hi, In my old version of Excel, I could copy a row or chunk of rows, move to a new spot and use the "insert row" icon to insert the rows and paste it automatically. Now in Office 2007 it just inserts a row instead of what I have copied. I want it the old way! How do I do it? -- Thanks, PTweety R-click, Insert Copied Cells. pickytweety wrote: > Hi, > In my old version of Excel, I could copy a row or chunk of rows, move to a > new spot and use the "insert row" icon to insert the rows and paste it > automatically. Now in Office 2007 it just inserts a r...

How to see calculation and heading in same cell.
I would like the cell to perform a calculation and then display the answer as will as a heading. In other words the answer and the heading will appear in the same cell. Perhaps you mean something like ="Heading Name: "&A1+B1 ******************* ~Anne Troy www.OfficeArticles.com "Jeracho" <Jeracho@discussions.microsoft.com> wrote in message news:F34BACB9-E2DA-448A-924D-46A475F98F91@microsoft.com... > I would like the cell to perform a calculation and then display the answer as > will as a heading. In other words the answer and the heading will appear in...

Invalid References in formula
Hi, I got this error message when i close my workbook: "A formula in this worksheet contains one or more invalid references. Verify that your formulas contain a valid path, workbook, range name and cell reference." The funny thing is that this error message pop out only when i save and close the workbook on certain worksheets. E.g. I have worksheet a, b, c and d. When i am either on sheet a and d, i saved the file and close the book at that sheet, the error message did not pop out. However when I performed similar actions on either of the other 2 sheets, the error m...

How can I Enable a Check Box based on another fields value?
Hi There, I have a form with a disabled check box. I need to enable it when a certain value ("approved") is selected from a combo box. When I am in Form Design View and I have the Check Box selected the Conditional Formatting menu item on the Format menu is greyed out. I am using Access 2003. Can you tell me what I need to do to make this work? Many thanks, David As you've discovered, Conditional Formatting isn't availabe to checkboxes. Try this: Private Sub Form_Current() If Me.YourComboBox = "Approved" Then YourCheckBox.Enabled = True Else YourChe...

Having a go with assembly... how do I copy a double into a register??
I'm trying to speed up a maths routine which consists of much iterative multiplication (and adding and subtraction of ) doubles. Now if I declare my doubles as floats float f double d _asm { mov edx, f //ok mov edx, d //error operand size conflict Can someone show me the error of my ways??? Thanks "Si" <me@you.twang> wrote in message news:sm1gk0l60ng23gilu9b68hkcqi3ub9horn@4ax.com... > > > I'm trying to speed up a maths routine which consists of much > iterative multiplication (and adding and subtraction of ) doubles. > > Now if I de...

Formula #71
Hi all, I am using the following formula in a Excel 2003. =IF((F12="Maternity leave"),C12/5*G12,IF((F12="Jury Service"),C12/5*G12,IF((F12="Paternity Leave"),C12/5*G12,IF((F12="Family Leave"),C12/5*G12,IF((F12="Special Leave"),C12/5*G12,IF((F12="Unauthorised Absence"),C12/5*G12,IF((F12="Compassionate Leave"),C12/5*G12,IF((F12="Annual leave"),C12/5*G12)))))))) I'm trying to state that if F12 is one of the values listed then display the result of the calculation C12/5*G12. This is working. Where would I add s...

Coping part of a cell content into a seperate cell
Hi I have two cells, one containing first and middle name and another one with surname. I want to combine the first name and surname into a separate cell, can you advise how I can just copy the first name and miss out the middle name please?? Thanks Caz Hi, I assume that the midle name is separated by a space from the first name and is in column A and the last name in column B =TRIM(LEFT(A2,FIND(" ",a2)-1))&" "&B2 "Caz H" wrote: > Hi > I have two cells, one containing first and middle name and another one with &g...

Passing Values from One Form to Another Including a Combo Box
Hi, hope someone can help with passing two values from one form to another by way of a command button. I have spent a week on various code taken from this site, but still no luck. Please ... someone help!! The form I am passing values from is called PATIENT HISTORY-Form. On this form, I need to pass a date from a field called DateSFESigned and I also need to pass information collected from a Combo box, Combo91. The command button is called Command119. The form that the values are being passed to is called Personal Habits- Form. Thank you in advance for any help on this matter. Maurita ...

Adding a formula to the same cell (H5) on every tab
I have an inventory spreadsheet with 125 tabs. The tabs are numbered 1 through 125. The are identical except for the data below the column headings. If I wanted to put a formula in H5 on every tab, can it be done other than manually opening every tab and typing it? One additional question: If I add a Summary Tab, how could I show the value of a specific cell on each tab without manually entering it? I show the formula I'm using bring B3 to the summary for every tab: A B 1 Unit Value 2 1 ='1'!B3 3 2 ='2'!B3 4 3 ='3'!B3 5 4 ='4'!B3 6 5 ='5'!B3 7...

Copy Distribution List from One Computer to Another
Hi Folks - I have seen various methods for accomplishing this, but none seem to help me. Our company uses Outlook 2003 in a NON Exchange Server environment. I have seen a method for copying distribution lists within a Exchange server environment, but it does not work in our environment. Any ideas? Thanks. Michael Michael <info@homekeyinc.com> wrote: > Hi Folks - I have seen various methods for accomplishing this, but > none seem to help me. Our company uses Outlook 2003 in a NON Exchange > Server environment. I have seen a method for copying distribution > lists with...

sumproduct--counting--zero--blank cells
I'm using these formula to count, =SUMPRODUCT((\$W\$9:\$W\$272>=0)*(\$W\$9:\$W\$272<10)) =SUMPRODUCT((\$W\$9:\$W\$272>=10)*(\$W\$9:\$W\$272<20)) ........etc how do i get it so bank cells are excluded from the count. The way it is now, they are counted in the 0 to 10 range... Thanks Jeremy -- Message posted via http://www.officekb.com COUNTBLANK(range) "jeremy via OfficeKB.com" wrote: > I'm using these formula to count, > > =SUMPRODUCT((\$W\$9:\$W\$272>=0)*(\$W\$9:\$W\$272<10)) > =SUMPRODUCT((\$W\$9:\$W\$272>=10)*(\$W\$9:\$W\$272<20)) > ........etc > how do...

How do I check my email on another computer?
"LalaBobo" <LalaBobo@discussions.microsoft.com> wrote in message news:EADEB55F-1548-4A75-92FF-5D119532C946@microsoft.com... Go to it? <g> -- John Blessing http://www.LbeHelpdesk.com - Help Desk software priced to suit all businesses http://www.room-booking-software.com - Schedule rooms & equipment bookings for your meeting/class over the web. http://www.lbetoolbox.com - Remove Duplicates from MS Outlook, find/replace, send newsletters Is it ISP mail (your ISP will have details) or a web based service like Yahoo or Hotmail? -- Mike Hall MS-MVP Windows ...

Extracting data from one sheet to another
I have a speadsheet with 390 rows and 80 columns. For each column, I wish to copy a range of cells and place them in rows so I can create a separate table for each item represented by the column. For instance, I would like to take the data from the range B10:B15 and place that into a range A1:E1, and then data from range C10:C15 and place into a range G1:K1, D10:D15 into I1:M1 and then B16:B21 into A2:E2, C16:C21 into G2:K2, etc, etc, etc I could do this manually, but it could take a while. I was wondering if there was an easier way to do this. Any help would be greatly appreciated. Thanks...

References omit formatting and return cell address
In two cases of references between worksheets, the formatting from the original cell does not appear in the cell that it is referenced to. Case 1: Worksheet 1, A1 contains a currency formatted number - \$2,000 Worksheet 2, A1 references the Workhseet 1, A1 cell using the = sign, yet it returns 2000 (unless I manually reformat the Workksheet 2 cell to Currency Case 2: Worksheet 3, A1 contains an apartment # - e.g. 4 Worksheet 4, A1 references this cell but returns the cell address - Worksheet2,!A1' - rather than the number 4. I tried different formats for the number 4,...

How can I insert a cell reference in a footer (eg for variable foo
Any ideas on how to do this? I'm trying to create a template with the doc reference number in the footer However, I'm trying to avoid users having to edit the footer (because this just wont get done). Hi only possible with VBA using an event procedure. e.g. put the following code in your workbook module for cell A1 Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim wkSht As Worksheet For Each wkSht In Me.Worksheets With wkSht.PageSetup .CenterFooter = wksht.range("A1").value End With Next wkSht End Sub -- Regards Frank Kabel Frankfurt, Ger...

Copying a database, how do I change security?
I have a split app (developed using A2000) which my own office has been using for several months. Another office has recently asked me to make a copy for their use. I am happy to do so, however, both the FE and BE are fully secured and I'm not sure how to change security on the copies to recognize the personnel at the other office and to stop recognizing the personnel at my office. I confess that I never fully understood Acess' user level security process. I implemented security originally by following specific steps from a textbook which I no longer have. I do understand about...

Can't get past welcome screen
I recently used a startup manager utility to reduce the amount of programmes that launched automatically on startup. I am now unable to get XP to start past the welcome screen. If I click on a user it loads settings and then logs off immediately. I can't get into safe mode as it does the same thing. I've tried copy userinit.exe wsaupdater.exe as suggested in http://support.microsoft.com/kb/892893 but this does not help. Any other ideas? On Sat, 5 Dec 2009 12:00:01 -0800, Sam Wardill <SamWardill@discussions.microsoft.com> wrote: >I recently used a startup mana...

Need help with a formula 01-23-10
I am looking for a formula that will compute an average of a number of non contiguous cells such as G8, G16, G24, G36, etc. Each of these cells has a formula which computes an average of a range of cells. With the helpm of this forum, I have been able to find a formula which does that AND uses values only when they are greater than zero and does not display #DIV/0!. But I cannot fin a fromula that will do the next step- Take an average of those specific cells AND use only the ones where the cell is >0, Example G8=100, G16=85, G24 is blank, G36=75, then this formula would ca...

How do I chart the same data cell on a range of worksheets?
I have the same row of cells on numerous worksheets that I want to chart or consolidate onto another worksheet ? Keith - You need to create a consolidated data range: http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Keith wrote: > I have the same row of cells on numerous worksheets that I want to chart or > consolidate onto another worksheet ? ...

Endnote/Cite write, as well as database issues #2
Hello all- Is anyone using Endnote 7 on the mac and Office X? I have found some stability issues, as well as issues connecting to certain databases- e.g. Blackwell does not have a direct filter, you have to go through other steps. I am all ears (actually eyes) to talk with someone about this because I am writing my dissertation and think that the steep learning curve is worth it. Email : sethhalvorson@gmail.com Hi Seth, There are a number of people dropping in and out of either this newsgroup or more likely the microsoft.public.mac.office.word newsgroup, who use Endnote, including some p...

Paste-Link Problem
I have constructed a calculator in a spreadsheet that will calculate breach flow rate given values inputed by the user (Sheet 1). There is also a seperate spreadsheet within the same workbook that contains numourus different site numbers and their location (Sheet 2). I am wanting to include the calculated breach flow from sheet 1 into sheet 2 for each site. I have used the copy and paste link combo and it works for the first site but when I go and enter the data for the second site in the calculator it also changes my calculated value for the first site in sheet 2. Is there an IF statement tha...

Shading cells not working
When I try to shade cells they remain white, but if I go to print preview the color shows. Why won't the cells change color in normal view? If the fill colours aren't appearing, the high contrast setting may be turned on. There's information in the following MSKB article: OFF: Changes to Fill Color and Fill Pattern Are Not Displayed http://support.microsoft.com/?id=320531 Jenny wrote: > When I try to shade cells they remain white, but if I go > to print preview the color shows. Why won't the cells > change color in normal view? -- Debra Dalgleish...

Counting the number cells between two dates
Hi guys, Hope someone can help with this, I'm pretty sure it'll be quite a simple one. Column A:A contains a list dates, I want to use a formula to count the number of cells which contain a date between 01/01/05 - 31/01/05. Any ideas, Many thanks, Dave Try: =SUMPRODUCT((A1:A1000>=--"1/1/05")*(A1:A1000<=-- "1/31/05")) BTW - I'm using American date formats in mine. HTH Jason Atlanta, GA >-----Original Message----- >Hi guys, > >Hope someone can help with this, I'm pretty sure it'll be quite a simple one. > >Column A:A con...

How do I limit the number of characters in a cell?
When try to limit the number of characters allowed in cell by going through the data/validation menu, it still allows more than the number of characters than I specified. I don't get it :( ...