Autofill Formulas

Hello.  I have a workbook with 31 sheets of data.  Each sheet is a form with 
a record of data.  I would like extract the data into a list.  For example, 
range A5 is the same field on each sheet, so I want to autofill a formula 
reference the same cell on different sheets.  I suspect I need a macro for 
this, but wanted to make sure first.  Can you autofill like this 
=Sheet1!$A$5, =Sheet2!$A$5, =Sheet3!$A$5?

-- 
Thanks,
Mike
0
Utf
3/16/2010 8:10:08 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
899 Views

Similar Articles

[PageSpeed] 17

=INDIRECT("Sheet" & (ROW()) & "!A5")

Entered in A1 of new sheet.

Copy down to A31............will increment the Sheet number but not the
cellref.


Gord Dibben  MS Excel MVP


On Tue, 16 Mar 2010 13:10:08 -0700, Mike Archer
<MikeArcher@discussions.microsoft.com> wrote:

>Hello.  I have a workbook with 31 sheets of data.  Each sheet is a form with 
>a record of data.  I would like extract the data into a list.  For example, 
>range A5 is the same field on each sheet, so I want to autofill a formula 
>reference the same cell on different sheets.  I suspect I need a macro for 
>this, but wanted to make sure first.  Can you autofill like this 
>=Sheet1!$A$5, =Sheet2!$A$5, =Sheet3!$A$5?

0
Gord
3/16/2010 8:52:22 PM
Reply:

Similar Artilces:

Rounding Formulas
I'm working in a spreasheet that has a lot of formulas, if I want to round the number that a formula shows, do I need to do that in a different colmn or is there something I can add to the formula to round it to the nearest dollar? Here is an example if the formula I'm often using in this spreadsheet. =((C22*G11)*G17) =round((C22*G11)*G17),0) should do it. If you want the result to the nearest PENNY, do this: =round((C22*G11)*G17*100),0)/100 "Chandra" wrote: > I'm working in a spreasheet that has a lot of formulas, if I want to round > the number that...

formula required
Hi, Cell A1 has the information. abcdef200912070005643<ready>,abcdef200912070005644<ready>,abcdef200912070005645<ready> In Cell B1 i need the answer, abcdef200912070005643 Thanks in Advance!!!! Vicky "Vicky" <Vicky@discussions.microsoft.com> wrote: > In Cell B1 i need the answer, abcdef200912070005643 So, type abcdef200912070005643 into B1 and press Enter. Okay, that's probably not the answer you were looking for ;-). But the point is: you don't say much to tell us what kind of answer you are looking for. If you want...

Autofill Formulas
Hello. I have a workbook with 31 sheets of data. Each sheet is a form with a record of data. I would like extract the data into a list. For example, range A5 is the same field on each sheet, so I want to autofill a formula reference the same cell on different sheets. I suspect I need a macro for this, but wanted to make sure first. Can you autofill like this =Sheet1!$A$5, =Sheet2!$A$5, =Sheet3!$A$5? -- Thanks, Mike =INDIRECT("Sheet" & (ROW()) & "!A5") Entered in A1 of new sheet. Copy down to A31............will increment the Sheet numbe...

formulas to change font
I am new to this - I would like to know how to change the font color conditional to a formula result. for example =if((A1<than 0,"font red", "font blue") I am familiar with some very simple formulas - but I would like to understand how to perform the above. Please don't laugh - but I would like to be directed to the right place to learn how to do it? Yonnie Yonnie, If you want to change font color in cell A1, first change the font color to blue. Type "0" (zero) in cell A1. Along the top of your excel you will see an A with a color under it. Typical...

MIN function in array formula
Hello, I have created an array formula with a MAX array formula with 2 criteria, which works fine. However, when I change the function to MIN, I always get the result 0. this works fine: {max((a2:a20=1)*(b2:b20=2)*c2:c20)} result gives 75 But this one doesnt:: {min((a2:a20=1)*(b2:b20=2)*c2:c20)} result gives 0, but it should give 19 A B C 1 1 2 50 2 1 2 75 3 1 3 99 4 2 2 ...

Formula
I'm in need of a formula in C1 that gives me the value of A1 divided into 60 if B1 is empty, however if B1 has a value (not zero) then C1=B1/60. THANKS. Hi, This also checks they aren't both empty =IF(COUNT(A1:B1)=0,"",IF(B1<>0,B1/60,A1/60)) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Trainee" wrote: > I'm in need of a formula in C1 that gives me the value of A1 divided into > 60 if B1 is empty, howeve...

Problem setting up formulas
Hi folks, I'd like a simple table, that I've spent four days attempting to create using "More" Excel 5 for Dummies. I do believe this book has caused me more confusion than I had before opening it. Grrrr. A B C D Date Amount Balance Total Spent 1 $500.00 2 4-1 $10.00 $490.00 $10.00 3 4-2 $95.00 $395.00 $105.00 4 4-3 100.00 $295.00 $205.00 I went to the Microsoft site and looked at templates available for download, and I found one that's a check register, but, unfortunately, I must have the information that you see in Column-D above (t...

Where is formula bar?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Pardon me for a newbie question (though I've been using Excel for 20+ years) but I don't even know the terminology: I just upgraded to Excel 2008 and the information about the formula in the cell, which used to be in the upper right-hand corner of the window, is completely gone. How do I tell what formula is being done to calculate the value in the cell? On 10/05/2010 13:14, Baron_Dave@officeformac.com wrote: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: > Intel Pa...

how to i control autofill
Hello, Suppose I enter a formula in cell B2 and I want this formula to autofill down to cell B55000. If I click and drag down to cell B55000, there are two problems: it takes too long to scroll all the way down to cell B55000, and by the time i actually get there the scroll speed is so fast that i usually pass the cell. Is there a way to autofill a formula so that I don't have to do it by manually dragging the formula down to cell B55000? Thanks, Joe -- Message posted via http://www.officekb.com Hi two options that i know of: 1) if there is data in column A down to A55000 (or co...

Help with formula #7
The answer to this is most likely extremely simple, for which I apologize. I have 10 columns, each representing an inventory item. Each row represents a name and the qty ordered per item . The number of names rarely changes. A single row at the bottom of each inventory column contains the price of the inventory item. I want to add a Column at the end of each line which calculates the total valueof their order. =SUM(B2*B195)+C2*C195+D2*D195...) =SUM(B3*B195)+C3*C195+D3*D195...) etc. However, when I copy the formula it does not retain the row number "195" containing the pri...

open/import Lotus wk4 files into Excel 2007 w\ formulae intact
I do not access to Lotus Smartsuite and need to import an old Lotus wk4 spreadsheet into Excel with the formuale intact. It would be huge hassle to recreate it and I am not going to invest in Smartsuite. thanks hi 2007 does not support lotus. so you need to find someone with lotus or check in to open office. it's free. http://openoffice.org regards FSt1 "jrj19" wrote: > I do not access to Lotus Smartsuite and need to import an old Lotus wk4 > spreadsheet into Excel with the formuale intact. It would be huge hassle to > recreate it and I am not going to invest i...

simple formula question?
I have a column where each cell has either a X or is blank. I would like to count the number of X's and then return a percentage complete based on the number of cells total (X's and blanks). I also have another column, but this column has all different characters (not just a X) I would still like to count the cells with data and give an average based on number of cells total (X's and blanks). Thanks in advance One way: =COUNTIF(A1:A25,"X")/(COUNTIF(A1:A25,"X")+COUNTBLANK(A1:A25)) Give the cell a percentage format. And if I understand the second ques...

Excel Formulas #15
Is is possible to make a letter in a cell trigger an equation. For example, if in a cell I put the letter "Y" (for yes) it will add a group of cells then sugtract a value from another cell to give me a total. But if I enter the letter "N" (for no) then it will only add a group of cells to give me a total without subtracting a value as it did before? Thanks, Corey Brock Corey Something like =IF(A1="Y",SUM(B1:D1)-E1,SUM(B1:D1)) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Corey Brock" ...

Formula for multiple conditions
The formula i have entered does not work for all conditions. Here is the formula i have entered and works for 2 conditions but not all 4. =IF(A12=0,"",IF(S12=0,"",IF(s12<=R12,"GREEN",IF(S12>R12,"RED/LATE",IF(R12<=TODAY()+3,"YELLOW","WHITR"))))) A12, R12, S12 contain dates and Column T is a status column. If no date is in column A then i want column T to be blank. And if column S date is blank, then i want column T to be white (on target for due date, column R), or yellow (3 days before due date). If column S is >...

Excel Formulas
I need to create a formula on excel. If a person is under 40 he will not need a medical until he is 40. If he reaches 40, he will have to have one every 5 years and if he is over 50, it will be every year...I just cant get it to work!! Thanks in advance. Hi, suppose that the age is in cell C2 and you want the formula in D2 so enter =if(C2<40,"Medical Exam not needed", if(C2=>40,"Medical exam every 5 years","Medical Exam every year")) if this helps please click yes thanks "Struggling" wrote: > I need to create a formul...

Copying a formula to new workbook
Is there any way to copy a formula from one workbook to another without bringing over the reference to the originating workbook. If you're just bringing one formula, you can copy from the formula bar and paste into the formula bar. If you're doing lots then you can change the formulas to text, copy|paste, and then convert those text strings back to formulas (in both workbooks): select the range edit|replace what: = (equal sign) with: $$$$$= (some unique string) replace all copy|paste edit|replace what: $$$$$= with: = replace all (both workbooks) === Another option ...

SPreed sheet formula???
I need to make up a spreedsheet to put id numbers in order but!!!! when I download this information to a spreedsheet and I put them in order which I do know how to do there are some id numbers that are the same. How can i tell the spreedsheet to do away with any ID numbers that are the same and just leave one of those mlti numbers to be counted. aS YOU KNOW ON THE GRID ON THE LEFT SIDE THERE ARE NUMBERS FROM 1 TO 999999. I need to do away with any number that is the same that I downloaded , as lone as though there is one number that was the same remain on the spreedsheet. I am tring to find ou...

Pivot Autofilering Items
Dear All, I'd like to see only the valid items on the drop down list on the "Page Field" area . An example. Source Data: Model - Color - Full Optional - Qty FIAT BLU YES 10 OPEL GREEN NO 12 BMW RED NO 14 In the Page field I put the Model, Color and Full Optional field. When I filter the item FIAT on the field Model and then I want to filter the Color field, I'd like see only the color regarding the FIAT model (BLU), bu...

Autofill on addressing
Trying to set up Outlook on new PC as I had it on old one where e mail address is automatically offered when part of a name is inserted, but no luck in finding it. Trevor Appleton <trevor@weather.4dinternet.co.uk> wrote: > Trying to set up Outlook on new PC as I had it on old one where e mail > address is automatically offered when part of a name is inserted, but > no luck in finding it. What version of Outlook? For OL 2003, it's Tools>Options>E-mail Options>Advanced E-mail Options: Suggest names while completing To, Cc, and Bcc fields. -- Brian Tillman ...

Erasing contents and leaving the formula
What is the way to leave the formula (sum, etc.) intact after erasing the contents inside? Where I do set this? Thanks in advance. Hi, An example of what you are trying to do would help but if I understand correctly you might try copying the cell and then 'paste special' 'paste values'. The will replace the formula (=a1+a2) with the result of that formula. Mike "Octavio" wrote: > What is the way to leave the formula (sum, etc.) intact after erasing the > contents inside? Where I do set this? > Thanks in advance. > > Thanks for your rep...

Autofill
When entering data into fields that verify against a database, such as vendor IDs, accounts, checkbook names, and so forth, have the program suggest a logical selection, much like Excel does with its autofill feature. AutoComplete functionality was added in v8.0 of Great Plains and remembers the Key field values that you use. David Musgrave [MSFT] Senior Development Consultant Escalation Engineer MBS Support - Asia Pacific Microsoft Business Solutions http://www.microsoft.com/BusinessSolutions mailto:dmusgrav@online.microsoft.com Any views contained within are my personal views and no...

Help with IF formula for HS Course Codes
How can I do this (below) in 2007 Excel? I have a list of high school course codes for some college classes in Column D. In column E I would like the NAME of the class, but I do NOT want to look up all 500 course codes to manually type in the correct name of the class. (Column A, B, C are used for first, middle, last name) So, I'm thinking that there must be a way to put in an IF formula of some type that say IF the course code is 1001 in columnm D, then in column E the name will be English 1 (for example). And IF the course code is 1002, it will read English 2 in column E, etc. I hav...

FRx Formula
Hello: I fixed an issue with a client's balance sheet. But, I do not understand why my fix worked and I'm curious. So, I thought that I'd ask. The client had created a new account for Bad Debt Expense. Of course, they added this account to their row format for their income statement. And, it showed up just fine on the income statement. But, on the balance sheet, Retained Earnings would not reflect the correct amount. Simply put, Retained Earnings was not deducting the bad debt expense account. In other words, Retained Earning was $33k higher than it should have been....

A formula problem. #2
That worked perfect! Thank you! : -- parabol ----------------------------------------------------------------------- parabola's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1642 View this thread: http://www.excelforum.com/showthread.php?threadid=27794 ...

Autofill ID fields
I am using Microsoft Access 2002 SP3. I have one main table named Issues and several supporting tables all related on the ID field. The Issues table ID field has an Autonumber to populate the field when a new record is created. What I would like to accomplish is to have the supporting table’s auto fill the ID field using the last ID created in the Issues table. On my Issues form I have a combo box that depending on the selection will open the corresponding form to the particular table. When the form opens I want the Issues form to close and the ID field to auto fill with the la...