Relative Sheet References

I have 50 worksheets with data, that I want to summarize 
on a sumary sheet.  I have created the link to the cell 
on the first worksheet.  I when I copy and paste the 
link, the cell reference changes relative to the row 
below, etc.  I also want the sheet to change from the 
Sheet 1 to Sheet 2, etc.  
Can I do this?  Please say yes.  And then give me a 
little diretion on how.
I don't use newsgroups very often, so if anyone knows the 
answer, could you email me at 
rgrimsley@metropo.mccneb.edu 
Thank you!!
0
rgrimsley (1)
3/5/2004 11:06:29 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
368 Views

Similar Articles

[PageSpeed] 14

Hi
do the worksheet names follow a logig. e.h.
wks1
wks2
wks3

If yes you may post some more detail and a combination of INDIRECT()
and ROW might help you


--
Regards
Frank Kabel
Frankfurt, Germany

rgrimsley@metropo.mccneb.edu wrote:
> I have 50 worksheets with data, that I want to summarize
> on a sumary sheet.  I have created the link to the cell
> on the first worksheet.  I when I copy and paste the
> link, the cell reference changes relative to the row
> below, etc.  I also want the sheet to change from the
> Sheet 1 to Sheet 2, etc.
> Can I do this?  Please say yes.  And then give me a
> little diretion on how.
> I don't use newsgroups very often, so if anyone knows the
> answer, could you email me at
> rgrimsley@metropo.mccneb.edu
> Thank you!!

0
frank.kabel (11126)
3/5/2004 11:11:34 PM
Or if they all have the exact same structure in terms of rows/columns and you
were looking to summarise every cell A3 for example, then is quite easily done
with a 3D range

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                  Sys Spec - Win XP Pro /  XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:ua4yzcwAEHA.2796@TK2MSFTNGP09.phx.gbl...
> Hi
> do the worksheet names follow a logig. e.h.
> wks1
> wks2
> wks3
>
> If yes you may post some more detail and a combination of INDIRECT()
> and ROW might help you
>
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
> rgrimsley@metropo.mccneb.edu wrote:
> > I have 50 worksheets with data, that I want to summarize
> > on a sumary sheet.  I have created the link to the cell
> > on the first worksheet.  I when I copy and paste the
> > link, the cell reference changes relative to the row
> > below, etc.  I also want the sheet to change from the
> > Sheet 1 to Sheet 2, etc.
> > Can I do this?  Please say yes.  And then give me a
> > little diretion on how.
> > I don't use newsgroups very often, so if anyone knows the
> > answer, could you email me at
> > rgrimsley@metropo.mccneb.edu
> > Thank you!!
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.613 / Virus Database: 392 - Release Date: 04/03/2004


0
ken.wright (2489)
3/5/2004 11:20:44 PM
Reply:

Similar Artilces:

Indirect function
Hi, I struggle to create a macro with INDIRECT function that would jump to different cells. What cell I want to jump to depends on a currently selected cell. This means I need to put a relative reference into the function. Here is my function - I need to replace R1C1 expression with a realtive one (currently selected cell address with the same behavior as R1C1 expression). How can I do that? Application.Goto Reference:= _ "INDIRECT(CONCATENATE(R56C8,""!"",""a"",MATCH(R56C1,INDIRECT(CONCATENATE(R56 C8,1)),false)))" Whatever I do I get follo...

vlookup -- table_array as reference to named range
Dear geniuses, I have price sheets coming from several sources with the same format and products, but containing difference prices. I am trying to build a dataset (table) from these sources which I could then link to Access for querying. Given each price sheet source is a seperate worksheet in a workbook, I have given each sheet the named range of its pricing rate. For example, RATE_165 represents the pricing sheet which contains the prices for the "165" rate; RATE_180 represents the pricing sheet which contains the prices for the "180" rate. I want to be able to...

Need to print the second sheet only if it is active
I have a two page excel worksheet, I always want the first page to print, but if there is a lot of information and the second sheet becomes active, I want that to print as well. But only if it is active. Select the Data Range and give Cntrl+P and check the [Selection] Option Button Under the [Print What] heading and give Ok... While selecting the data range and printing the selection only will let excel to print the sheets which is having the data. Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Lori Mae&qu...

Can I show a reference value with a string?
Hi All, For a single field, can I show a refernce value (ex: =B2+B3) and then follow up some strings? the final result looks like this: The price is 20 Thank you very much. Best regards, Boki. ="The price is "&B2+B3 -- goober ------------------------------------------------------------------------ goober's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19838 View this thread: http://www.excelforum.com/showthread.php?threadid=486137 If you want to use a format like currency you need to use the TEXT function ="The price is &q...

Countif on two columns in a sheet
Hello fellow Excellians, On my last question I got a perfect answer. I wanted to know how many times a particular value could be found in a certain column in a certain tab. Now I want to know how I can use the same by selecting two values. I have a sheet with the following values. Date In (Column A) Number (Column B) Solved by (Column C) Flow (Column D) Cause (Column E) etc, etc Now I want to know how many times the person (in Column C) has solved a particalur issue concerning a Flow (Column D). With selecting only one value (for the situation how many tim...

Tricky question about pasting references
I have a group of cells that are right next to each other. Call these, A1-D1. The cells reference cells in another sheet, but the cells they are referencing are not necessarily adjacent. For example, A1-D1 will contain (in order) references to Worsheet!C3,Worsheet!D3, Worksheet!D3, and Worksheet!F3. I'd like to start a new worksheet where the arrangement of the references is maintained, except that I am now referencing a different worksheet where the referenced cells are moved to the right. For example, cells C3, D3, D3 and E3 are now NewWorksheet!E3, NewWorksheet!F3, NewWorksheet!F3...

combining related data into one row of data
Access 2003 If I have a table like this with fields and data like this: Table1 Acct - Name ------ value1 - value2 - value3 123------Bob------------10-----------10-------10 123------Sue------------10-----------10-------10 How can I get a query result like this? Acct - Name ------ value1 - value2 - value3 123--- Bob, Sue------20------------20--------20 Any assistance greatly appreciated! RBolling On Aug 2, 4:54 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com> wrote: > There is a generic concatenate function with sample usage athttp://www.rogersaccesslibrary.com/OtherLibraries.asp...

relative path...
Hello, Using VC++6, I have created an MFC application. My .exe utilizes an outside folder named 'MyFolder'. Presently, I have used the following code in my .exe to access 'MyFolder': CString InitialPath="C:\\Documents and Settings\\Main\\Desktop\ \MyFolder\\"; 'MyFolder' is located in the same folder as my '.exe' What relative path could I use so '.exe' can access 'MyFolder'? Thanks in advance, RABMissouri2007 "RAB" <rabmissouri@yahoo.com> ha scritto nel messaggio news:1188483407.448383.320730@g4g2000hsf.goo...

Updating Excelsheet with conected VLOOKUP sheets time consuming
I have a workbook with several sheets. Some sheets connects via VLOOKU to an updatable sheet with raw data. When I try to update data (impor from database) to the raw data sheet. It takes forever and I think thi is casued by the "linked" VLOOKUPs to from the other sheets to thi rawdata sheet, beacuse when I have tried to update the rawdata shee without "connected" VLOOKUP sheets it does not take forever. Is it possibel to solve this? I have tryed Application.ScreenUpdating = False without success, maybe because the VLOOKUPs are in the othersheets no in the rawsheets th...

Need help with linking sheets.
Hi all, I have a problem I was hoping someone could help me with. What I'm doing is using a =sumif() to pull some numbers from different files. On the files that I'm pulling the information from I have two ranges named Code and Current and we'll call the actual file names Table.xls, Table1.xls, Table2.xls, etc. On the file that I'm trying to pull the information into I have =sumif('Table.xls'!Code,A5,'Table.xls'!Current) in C5. This pulls the information fine but I have 300 rows I have this formula in. I want to be able to just put the file name like ...

How to add column(s) that show Yes or No indicating whether that record came from a related table
I had to merge three tables together, and now a request has been made to show (in the merged flat file) in which report the data exists. So I will be adding three columns, and there needs to be a streaming Yes or No in each column, indicating whether the Person's name matches to each file. Is there a way to do this? I am stumped, but mainly b/c I am new to Access and am learning how to connect some of these queries to result in the desired output. Any suggestions would be appreciated. > I had to merge three tables together Why? As in why would you want/need to do that? That is comp...

Protect sheet problem
When I open the workbook, Sheet CALCULATEHERE is not protected properly. "Select locked cells" and Select unlocked cells" are both checked. Can someone explain what I'm doing wrong? Thanks, Bernie Private Sub Workbook_BeforeClose(Cancel As Boolean) Sheets("CALCULATEHERE").Select UNPROTECTSHEET Range("A1") = 0 Range("A2") = 0 Range("A4").Select DELETEWORKSHEETS Sheets("CALCULATEHERE").Select PROTECTSHEET 'When I check the sheet here, it is protected properly, but something apparently goes...

How to name each sheet in the box
Hi there, I used someones spreadsheet and they had the names of the sheets listed in the box below the font at the top of the screen. I think its called the name box. I'd like to do that with my spreadsheet so I can switch sheets by clicking the dropdown. Anyone know how to do this? Thanks in advance Grd I do not completey understand the situation, but it may be something like this: In the upper left box in the Formula Bar the active Cell address is displayed or the name of the Cell if it has been given a name. If you select the Down-arrow on the right-side a drop-down list of a...

I want chart source data to be relative references, not absolute.
Everytime I try to enter the chart source data as a relative reference, Excel automatically adds the dollar signs to make the references absolute. I have 12 groups of columns repeating data above the graphs. (The rows and the columns are identical, except for the data entered in the variable cells.) If the cell source data were relative, I could copy the original chart over 11 times and be done. Since it is not, I have to go in each chart and manually edit the source data to adjust the column references. This is a pain. Also, if we delete or add any groups of column data, which we...

time-sheet record with over 24 hours
I want ot create a time sheet for my employees where I can have the first column be the start time, and the second be the end time, with the ability to start at 10pm and finish the shift at 8am, which is over the 24 hour mark. Then have the hours totalled in the third column, with a subtotal for each week, and total for two weeks. My current dilema is where the hours go over the 24hour mark in the same day and getting the total of hours worked for that day. You might get some help from here: http://www.cpearson.com/excel/overtime.htm tj "kkwaters" wrote: > I want ot ...

Total a column from sheet 2 based on value in sheet 1
Col B Col C Col F Mary Team 1 $331.00 George Team 1 $222.00 Sam Team 2 $186.00 Tom Team 2 $100.00 Above is an example of my data on Sheet 2. On Sheet 1, I want to total all the total funds raised per Team shown on Sheet 2. I am trying to create a summary of what each team raised. I tried using the formula: =SUM(('sheet 2'!F2:F482=Sheet1!B2)) and I get just a dash in my total col. Can anyone provide some help? Thanks Look in HELP for the SUMIF() function -- Kind regards, Niek Otten Microsoft MVP - Excel "Nee...

Problem printing excel sheet with a browser
I have a worksheet that I want others read and print via their I browser. This worksheet consist of just one page and I have saved th worksheet as .htm and it looks perfect in the browser. But when I wan to print it with the IE browser, it tooks 4 pages, all excel formattin are lost, no footer. How can I fix this ? Thank you all. mis2OO -- Message posted from http://www.ExcelForum.com ...

Different data sheets
We put together a questionnaire in xls. Got over 100 of them back, and have now to pull together the info we gathered. I put all 100 in one single workbook, each time in a different sheet. Would now need to put all the answers given per question, in a separate sheet. If I put f.i. in a cell ='1'!A37, I get there the answer of sheet one, cell 37. But if I drag this down to copy this, the sheet always stays the first one, while I would like xls to change each time the sheet, but keep the cell. So how can I copy this that I get in the cell below ='2'!A37, next ...

Copy using two sheets
Hi, I have 2 workbooks, the first is: HCP_2005 with one of the sheets named "October 2005"and the second is WV_2005 with one of it's sheets "Oct". The following macro filters a range and copy the filtered rows to the second workbook to the mentioned sheet in the second workbook named "Oct". I need ALSO to copy the cells D1 & D2 from the sheet "October 2005" in "HCP_2005" to the same location in the second workbook "WV_2005" in the sheet "Oct". I am not succeding to do it. It seems I am missing something. Can an...

filling datat to another sheet
i have a list of peoples names, hundreds long, that need to get inpute to another sheet in the same workbook, but each name has a differen spot to go to. They are seperated by about 15 lines each, with dat between? How to do this without cut and paste -- Message posted from http://www.ExcelForum.com Static, How you do this depends on whether you're trying to add the list to an already filled column that has a blank spot every 15 rows, or to a completely blank column. For the completely blank column: Suppose your list starts in A1 of Sheet2. In A1 of Sheet1, use the formula =INDIR...

where can I find a simple home budget work sheet?
I want to record all our home expenses on a daily basis to keep track of where the money is going. I am looking for a worksheet to accomplish this in a simple way. Thank you. "Jagadeep" <Jagadeep@discussions.microsoft.com> wrote in message news:92737A01-1804-42B0-8444-9B75F5D73B26@microsoft.com... > I want to record all our home expenses on a daily basis to keep track of > where the money is going. I am looking for a worksheet to accomplish this > in > a simple way. > Thank you. Try Office Online - plenty of Home Budget templates there. In f...

Duplicate a chart with relative formulas
If I have several sheets, each with data for a particular year, how can I copy a chart from 1 year to all the others, making sure the data references are to the respective year? Duplicate the sheet that has the chart, then replace the data on the copied sheet. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ On 3/23/2010 9:29 PM, Bobhoe wrote: > If I have several sheets, each with data for a particular year, how can I > copy a chart from 1 year to all the others, making sure the data references > are to the respective year? ...

sheets
Dear sir,with my regards,i enclosed herewith an example from a workbook asking you kindly to help me for transferring the datas mentioned in main sheet to agents sheets continually . Thanks in advance. -- tofimoon3 Hi, No files can be attached here, so you need to find a location accessible from the web and let us know where you put them. -- If this helps, please click the Yes button Cheers, Shane Devenshire "tofimoon3" wrote: > > Dear sir,with my regards,i enclosed herewith an example from a workbook > asking you kindly to help me for transferring the datas ...

Named Range that uses "relative" range
BTW - Excel 2003. I have some data in a table with a column for Jan (column A), Feb (column B), etc. for Sales in Jan, Feb, etc. In the past, I had array formulas that added up each month as needed, using an Array Sum, starting with Jan formula were refer to A$1:A$99. As I copied this formula for Feb, the range I added up was relative, so changed to B$1:B$99, etc. Now I am trying to switch to some cleaner Named Ranges, such as Sales_Jan, Sales_Feb, etc.). My add Array Sum formulas have been changed to use the Named Ranges. Unformtunately, my formula for Jan uses Sales_Jan, wh...

database related problem
I am trying to post this message to the database session, but there seems to be a problem for the past few days posting to it. Here is my question: I have a CRecordset class (call it CMyProblem), which I'm using to update an Access table. I am using MoveFirst() to set the pointer to record 1 and am iterating through the table with MoveNext(). Everytime before moving to the next record, I update a text field. After about the third record, I got a 'Multiple rows were updated' message, which I corrected by changing CMyProblem from snapshot to dynaset. I am using the open() member fun...