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 |

3/16/2010 8:10:08 PM

=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 |

3/16/2010 8:52:22 PM

I am looking for a way to display zero values in my charts as gaps. Right now I there is a big slant in my chart that I am trying to get rid of. The range I am using has formular in them, so even when the cells evaluate to blanks, they are displayed as zeros on the chart. Any help will be greatly apprecaited. Thanks Two possibilities I can think of" 1) Add an IF statement, to your formula, in order to present N/A instead of 0 [ use the function NA() ] Something like that: =IF(A1*B1=0,NA(),A1*B1) -------------------------------------- 2) Hide all the rows where the cell...

Here is my formula: =IF(ISBLANK('Detail Qry'!E25),NA(),'Detail Qry'!E25) It returns a #N/A like it is supposed to be it is still being charted on my chart. I have tried ISERROR and ISERR but they don't work either. I am actually using this formula to pull in a row of dates and then column A is a work center. So really what i have is a crosstab. Column A Column B Column C 5/29/07 5/30/07 L1 390 cs 502 cs B4 425 cs 618 cs When i run the query from the referenced sheet i will get...

In column A there are cells with team names in them like the following: NYY NYY NYY @OAK @OAK @OAK BOS BOS BOS @TEX @TEX @TEX I would like column B to list all the team names without the @ symbol and column H to list all the teams with the @ symbol. For the example I would like column B to have NYY NYY NYY BOS BOS BOS =COUNTIF(A:A,"@*") and =COUNTIF(A:A,"<>@*") -- HTH RP (remove nothere from the email address if mailing direct) "Jambruins" <Jambruins@discussions.microsoft.com> wrote in message news:135965C4-7523-47AB-88DE-D0B12B652CD5@m...

I have lists of insurance names followed by their types in once cell example: cigna WC Aetna GRP Progressive PIP i would like to have a column that would show me the type so i can sort by type (my report doesnt have 'type' column) i need the formula to say "if cell contains 'WC' then the result equal 1, if the cell contains 'grp' then the result would equal 2, if the cell contains 'PIP' then the result would equal 3" i cannot figure out how to write that kind of formula thank you MS community Since both Name & Type are in o...

I have a spreadsheet with three tabs. They all have people requesting time off...first sheet is for people requesting more than a week off - 2nd for 1 week at a time - 3rd for people just wanting 1 day off. The sheets have their name then 2 columns one for days off starting and the other for days off finishing (With the exception of people just wanting one day off) I would like to have another sheet counting the number of people off on a daily basis. It is easy enough using "counif" for the people asking for 1 day off...but I can't seem to easily break out the weekly...

I have a spreadsheet where I have two columns of numbers Example: Column C Column F Start Row # End Row # 2 5 2 5 2 5 6 20 6 20 6 20 I would like to define a sumproduct using these rows as guides i defining the range. I thought maybe I could use the indirect key t reference these cells as the row number, but it's not working as hoped. =INDIRECT("SUMPRODUCT ($L$"&F2&":L"&C2&",$N$"&F2&":N"&C2&"") ...

Hi Friends! Could some help me on this formula? I need to complete a task say from 1-Jun-05 to 30-Jun-05 i.e 30 days, but however on a given day when I analyse the Project schedule (say today), I find out that on the 9th day i.e 9-Jun-05 on actual progress I have completed only 4% of the Job, Hence what should be my forecasted days to complete the job a 100% in actual, now putting it in a mathematical sense: 100% = 30days (Plan) 4% = 9days (Actual & when the plan is 30%) Hence 96% = ? (How may days in Forecast) I want the formula to be dynam...

The following formula will not work and for the life of me I can not figure out why: =if(isna(match($a2,$e$2:$m$7,0)),$a2," ") In cell a1 is the text "All Players". From cells a2-a55, I have the following data: player1, player2, player3...player54. In cell b1 is the text "Available players". From cells b2-b55, I have the above formula so the default values are the same values as column a. From cells d2-d7 is the text team 1, team 2...team 6. The values in cell range e2:m7 will be populated by a dropdown box which includes all the data from cells a2:a...

Hi, I am wondering if there is a way to prevent a formula to be updated after an insert. As an example, let's say that I have a cell on Sheet2 that references Sheet1!A4. Let's say that I insert a row in Sheet1 above row 4. Automatically my formula on Sheet2 is updated so that is references Sheet1!A5. Is there a way to prevent this? TIA. Hi, Check out Indirect function =INDIRECT("sheet1!A4") Regards, Hari India "Atchoum" <NO_goglus_JUNK@videotron.ca> wrote in message news:OsZ1d.64670$WH1.1629608@wagner.videotron.net... > Hi, > > I am wonderin...

Hi! I have a blank workbook containing many formulas and many worksheets. I use this to create a new workbook everymonth and enter data. After a while, I found a way to improve in a subset of formulas. How can I go back and reapply these formula to the entire workbook (including all worksheets, some of them are identical but others are different) without doing lot of manual work? thank you, prashant -- How about the paste special? It didn't work or not likely what you wer looking for -- hidek ----------------------------------------------------------------------- hideki's P...

Hi, I have two seperate data in two seperate columns. say columns are ABC 123 DEF 456 GHI 789 JKL 012 Now i wish to write a formula that will count the occurance of 123 with corrosponde to ABC. say for above columns, value would be "1". i am trying it with COUNTIF, but i am not able to concatenate two COUNTIF's. Please help Mukesh Hi! Try this: =SUMPRODUCT(--(A1:50="ABC"),--(B1:B50=123)) Better to use cells to hold the criteria: D1 = ABC E1 = 123 =SUMPRODUCT(--(A1:50=D1),--(B1:B50=E1)) Biff "Mukesh Garg" <Mukesh ...

Is it possible to save a spreedsheet with only the the cell values and not the associated formulas. Basically, what I want to do is a giant Copy PasteSpecial Values across an entire workbook Thanks Bob "Bob Ewers" <anonymous@discussions.microsoft.com> wrote in message news:274FD675-AB2F-4295-A201-71B4AE2C6C2A@microsoft.com... > Is it possible to save a spreedsheet with only the the cell values and not the associated formulas. Basically, what I want to do is a giant Copy PasteSpecial Values across an entire workbook. > > Thanks. > > Bob Group the worksheets ...

I'm quite happy getting part of a row to format as I wish by using a formula and applying it to a range but I want to use this about 50 times in a single spreadsheet and I cant stop the range being absolute so when I drag/fill I don't get the effect I want. Is there way to fill down conditional formats which have been generated using a formula? -- Rod Not with FrontPage. Try asking in a newsgroup or forum that deals with spreadsheets. -- Ron Symonds Microsoft MVP (Expression Web) http://www.rxs-enterprises.org/fp/wf-menu.aspx RODXL presented the following e...

Financial planning spreadsheet containing many long interdependent formula needs to be updated from 04 to 05. Is there some quick way of editing this (ie find/replace)? -- Fionn Hi, Fionn- The answer is "Probably yes", but will you please provide an example of a formula? If it was a simple search and replace you probably would have done it already. Is it a matter of changing a cell reference from 'SheetName'!A1 to 'SheetName'!B1, where A1 is a 2004 total and B1 is a 2005 total? ...

I am trying to define ranges each of my ranges is characterised by an identifier in this case "D1" then directly below it are property descriptions. So my sheet looks like this: D1 XXX XXX XXX D2 XXX XXX XXX My problem is that when i use this formula it counts all text values so when i want to select range D1 rather than selecting D1 and the three rows below it, it selects D1 and 8 rows below D1. Any ideas on how I can make the formula select the range properly? =OFFSET(Sheet1!$A$1,MATCH("D1",Sheet1!$A:$A,0)-1,0,*COUNTA(Sheet1!$A:$A*),5) -- Vlad999 -----------------...

I have a spreadsheet; 25 participants for 31 days each participant must log a catagory (eg PC) the spread sheet count the number of PCs for the 31 days (eg. formula (=COUNTIF(E10:AI34,"PC")). Question how can I calculate how many participates have logged a catagory PC within the 31 days. Hi C, One way, Put =COUNTIF(E10:AI10,"PC") in cell AJ10 And drag down to AJ34 Then in another cell of your choice put =COUNTIF(AJ10:AJ34,">0") HTH Martin "C Sealy" <C Sealy@discussions.microsoft.com> wrote in message news:13350DAB-8BEC-4731-B235-B4B...

Is there an easier way to copy and paste the values of a cell without using notepad? When i paste directly to new column the formulas get copied--i only want to paste the final values... Makes sense? Jeremy -- Message posted via http://www.officekb.com Hi jeremy Copy the cells Right click on the target cell Choose Paste Special Select Values OK -- Regards Ron de Bruin http://www.rondebruin.nl "jeremy via OfficeKB.com" <forum@nospam.OfficeKB.com> wrote in message news:2af487a568964ca5af6a0c284eb172b0@OfficeKB.com... > Is there an easier way to copy and paste the val...

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" &...

Happy New Year and Sawasdee from Thailand. I have a file that I would like anyone who can help me to solve my problem. Please d/l my file and help me please.... Thank you Attachment filename: mark.xls Download attachment: http://www.excelforum.com/attachment.php?postid=404449 --- Message posted from http://www.ExcelForum.com/ > Happy New Year and Sawasdee from Thailand. I have a file that I would > like anyone who can help me to solve my problem. Please d/l my file > and help me please.... Thank you Not saying you would purposely send a virus, ...

In the Function Arguments dialog the official Formula Result is different (and wrong) from the result shown after "=" just under the Serial number field. I am using the Year formula. Whatever formatting I use for the date neither this formula nor Text to Columns can return the correct year. EG: Date is 15-05-07 or 39217, Forumula Result = 1905 but under serial number field it shows the correct result of = 2007 Dan, This question; at least to me, isn't clear. What are you entering and where and what is the formula you are referring to? If you enter something ...

Hi; I have a very large spreadsheet; with eg. from 2-4000 rows of data for each household in each suburb. I have separated the suburb records by writing a macro that inserts two lines between each separate suburb (ie. between Suburb AAA and Suburb BBB). In those two lines, I wish to calculate the Sum, Average and Median of the data in column B (A is the suburb name). I filter the suburb for blanks, and then paste and copy down the formula into all 3 cells of the blank rows (all formulas refer to the data in column B). However, I have a weird problem with the formulas, in that the formul...

Help, I need a formula for work. Here is a generic version of my problem? Leauge Team Attendance exceed 20,000? American Yankees Yes American Red Sox No National Phillies Yes National Mets Yes National Giants Yes National Dodgers No American Twins No American Angels Yes =COUNTIF(E3:E10,"YES") Total would be 5 "YES'' Answers ______________________________________________ Now the spreadsheet is FILTERED to only show "AMERICAN" Leauge Team...

I'm trying to Conditional Format a number of cells using "Formula Is" & "=ISODD(INT($f2))=True", ie. if the value in F2 is odd, format the data. I keep getting the message "You may not use references to other worksheets or workbooks for Conditional Formatting Criteria". Why am I getting this error? I used this formula instead: =MOD($F358,2)<>0 But was interested to know why my original wasn't acceptable. XL2003 WinXPSP2 -- | +-- Thief_ | =isodd() is in the analysis toolpak (tools|addins). It's not built into excel. Thief_ wrote: ...

Could anyone please tell me, I have developed a worksheet template were I have sourced the data labels in my chart. Sometimes this means 0 is inserted in the chart and I wondered if it was possible when I used the formulae below that I could state that if the source cell equalled 0 then this should not be used. =' Data'!$O$32 Many Thanks in anticipation. How about: =If(' Data'!$O$32=0, "",' Data'!$O$32) Robert Flanagan http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "JEM" <...

Greetings & bless your little cottons for existing. I've never don anything more than autosum before and now my workplace has gifted m with a spreadsheet project that looks monumental to me. Can anyone tel me how..... If, in Sheet 1, the cell range C2-C41 contains "Cohen" anywhere AND th cell range R2-R41 contains "allowed" anywhere, I need to have th number of times that these two conditions are satisfied displayed a that number of times, in Sheet 2 at cell B2. So far I have =(IF((C2:C41="Cohen")*(R2:R41="Allowed"),) I can't find the command...