Custom lists from different sheets

Hi to all,
I want to create a custom list from linked data from other sheets.
Lets say we create the list on 'sheet10' and the values will be taken from:
'Sheet1!A1' , 'Sheet2!A1' , 'Sheet3!A1' ect....and i want the list to continue till 'Sheet9!A1'. I tried to drag the first three link cells (on sheet10),in order to create the list but the next cells didnt jump to: 'Sheet4!A1',Sheet5!A1'....

Does somebody knows the answer....
Thanks in advance
0
Lp12 (7)
6/28/2004 6:06:01 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
622 Views

Similar Articles

[PageSpeed] 46

Hi

One way is to link your formula to the row number, and use this to increment
your sheet numbers. Try
=INDIRECT("Sheet"&ROW()&"!A1")
when you fill this down, the ref will increment with the row number.

-- 
Andy.


"Lp12" <Lp12@discussions.microsoft.com> wrote in message
news:DF6D81FC-4AE4-4F8E-8052-A17D01169FF9@microsoft.com...
> Hi to all,
> I want to create a custom list from linked data from other sheets.
> Lets say we create the list on 'sheet10' and the values will be taken
from:
> 'Sheet1!A1' , 'Sheet2!A1' , 'Sheet3!A1' ect....and i want the list to
continue till 'Sheet9!A1'. I tried to drag the first three link cells (on
sheet10),in order to create the list but the next cells didnt jump to:
'Sheet4!A1',Sheet5!A1'....
>
> Does somebody knows the answer....
> Thanks in advance


0
andyb1 (494)
6/28/2004 8:23:35 AM
Thanks a lot for the answer but still no luck with it.
When i fill the formula down it still filled as same as the upper cell.
no luck.....
can you send me a XLS file for example to: minista_ar@hotmail.com
thanks a lot...

"Andy B" wrote:

> Hi
> 
> One way is to link your formula to the row number, and use this to increment
> your sheet numbers. Try
> =INDIRECT("Sheet"&ROW()&"!A1")
> when you fill this down, the ref will increment with the row number.
> 
> -- 
> Andy.
> 
> 
> "Lp12" <Lp12@discussions.microsoft.com> wrote in message
> news:DF6D81FC-4AE4-4F8E-8052-A17D01169FF9@microsoft.com...
> > Hi to all,
> > I want to create a custom list from linked data from other sheets.
> > Lets say we create the list on 'sheet10' and the values will be taken
> from:
> > 'Sheet1!A1' , 'Sheet2!A1' , 'Sheet3!A1' ect....and i want the list to
> continue till 'Sheet9!A1'. I tried to drag the first three link cells (on
> sheet10),in order to create the list but the next cells didnt jump to:
> 'Sheet4!A1',Sheet5!A1'....
> >
> > Does somebody knows the answer....
> > Thanks in advance
> 
> 
> 
0
Lp12 (7)
6/28/2004 11:33:01 AM
Reply:

Similar Artilces:

Seek (ADO) finds same absoluteposition for two different indexes
The function below takes a serial number entered in the "Serach" control and then seeks that value in the data table called "PCM Interfaces (Main Table)". If seek does not return EOF then the absolute position is found and then the form is set to show that record. This function is just a form record locator.The problem is that the same AbsolutePosition (1) is found for records 1 and 2, when their respective serial numbers are searched. So the symptoms are that when serial number 1 is searched it locates record 1. When serial number 2 is searched it locates record 1. When se...

Elapsed time with custom work week.
I have a huge problem. I need to determine elapsed time in hours for projects. The main problem I am having is that I do not know how to set up a custom work week of 6am Monday - 6pm Friday. I have a formula now, but it does not take into account the special hours on Monday and Friday, but it does exclude the weekends. =NETWORKDAYS(Y2,Z2)-1+IF(NETWORKDAYS(Z2,Z2),MOD(Z2,1),1)- IF(NETWORKDAYS(Y2,Y2),MOD(Y2,1),0) Where: Y2 = DATE IN Z2 = DATE OUT Is there anyway to establish Mon - 6a-11:59p Tues - 12:00a - 11:59p Wed - 12:00a - 11:59p Thurs - 12:00a - 11:59p Fri - 12:00a - 6:00p Any help wou...

custom paragraph numbering
I need help trying to set up a custom number for a patent document. The numbering needs to be in [0001], [0002], [0003]. It needs to single level with a five space indent from the heading of the list. On the design new numbering format it won’t let me make a custom one without one of the numbering formats included, like 01, 02, 03, and so on. I would like to have the numbering continue from 0001 up a number each paragraph so it does not have to be done manually... Is it possible to do this either with numbering, multilevel numbering or in the styles? This document is due in a...

Different Booklet Format
Using Windows XP and Office 2007 Trying to print a booklet that is on a portrait orientation 8 1/2 by 11 sheet of paper that is folded in half from top to bottom. The resulting booklet would open from the bottom rather than from the left like a normal booklet. It would be like a greeting card that has a top and a bottom on the inside rather than a left and a right. Hope I'm explaining this correctly. Anyhow, cannot seem to get a page set up that works correctly. Any suggestions??? BK wrote: > Trying to print a booklet that is on a portrait orientation 8 1/2 by 11 > ...

Print list of ws names
Is there a way in Excel 2003 to print out the names of all the worksheets in my workbook? Thank your for your help Joanne Thanks Chuck I appreciate your efforts Joanne CLR wrote: > Don't remember who in the group gave this to me, but it works good....... > > Sub ListSheets() > 'Lists all SheetNames in Workbook on new sheet called "SheetNames" > On Error Resume Next > Sheets.Add.Name = ("SheetNames") > For i = 1 To Worksheets.Count > Cells(i, "a") = Sheets(i).Name > Next i > End Sub > > Vaya con Dios, > Chuck, CABG...

2007 Customization
Hi - Over the past couple years I've created custom macros and toolbars (in word and excel) for my company. Some are deployed to a few individuals, some to a lot of people. Some are very simple macros that make life easier and tasks faster. None of them make any sense when you attach an unrelated symbol to them on the QAT. (This is probably one of the most frustrating aspects of the ribbon.) I'd like suggestions on how to handle this in 2007. Is it best to customize the ribbon (can I share this customization?) or ??? I've looked at P. Schmid's RibbonCustomiz...

Custom Help File
Is anyone aware of a way to add a custom help file to an excel 2002 workbook? I'd like for a user to be able to highlight a specific word and find the word in the custom help file accordingly. Suggestions appreciated! ...

Publisher cannot open files from a different version (again!)
I've gone through lots of messages about this problem and have tried the Norton and dragging the file to the desktop suggestions but neither have worked. My version of Publisher is: Microsoft Publisher 2000 SR-1. I have received a CD today which has 3 templates on it which I need to access but I keep getting the above error message. On the files I can't open it says the version is Publisher 2000 Version 6. I really am not technical but I presume that means it is a newer version than my own, yet they are both Publisher 2000 - will I need to purchase a newer version of Publishe...

Stop listing FAX in "Select Names"
Is it possible to stop the listing of FAX info in the Select Names drop down? Currently both SPTP & FAX are listed under e-mail types. I would like to only show SMTP. Thanks, JOHN version: Outlook 2003 I would like to know also. I have OL2003. "JRS" <anonymous@discussions.microsoft.com> wrote in message news:11df901c3f57a$d43627c0$a401280a@phx.gbl... > Is it possible to stop the listing of FAX info in the > Select Names drop down? > > Currently both SPTP & FAX are listed under e-mail types. > I would like to only show SMTP. > > Thanks, JOHN...

custom chart and image control
I created a chart type that contained an image control (company logo) and a textbox. I saved that chart as a custom chart type. But when I selected that custom chart through the chart wizard in another workbook, the image and textbox controls didn't appear. I even edited with copy and paste those controls directly into xlusrgal.xls where custom chart types are held, but they refused to show up in my charts. Can it be done? and how? PS: I posted this question on the general forum earlier (I don't yet have the reflex to look at other forums. sorry) --- Message posted from http://ww...

pivot tables
I have a table with region, leaders (rows), points per leader (value), and date (column). I want to get the average number of points per region per day. If I summarize the data using sum, I get the total number of points, not the average. If I summarize the data using average, I get the average number of points per person per day, not the average numer of points of the region per day. What need is to divide the total number of points of each region, each day, by the number of persons that actually get points that day. Any ideas? ...

sort sheets alphabetically
Hi, Do I have to drag them in place or can I somehow sort all my sheets so Andrew is at the front and Zach is at the back. Regards Suzanne See http://cpearson.com/excel/sortws.htm In article <8C4EB970-955F-4591-A7DD-4210DFC1D72C@microsoft.com>, Grd <Grd@discussions.microsoft.com> wrote: > Hi, > > Do I have to drag them in place or can I somehow sort all my sheets so > Andrew is at the front and Zach is at the back. > > Regards > > Suzanne Or... the free Excel add-in "XL Extras" will do it for you. (plus some other nice stuff) ...

Contact listing
I have my contacts listed by Last Name. But I find that if I enter a Nickname in the Nickname area that name becomes listed alphabetically by his Nickname. Is there a way to change that? I deleted the nickname from that area and placed in somewhere else. Control Shift C Rt Click on the column header - you can add, remove, change columns Click on a column header to sort Note: Multiple fields are available(First Name, Last Name, Name, Nickname) - the First Name and Last Name fields only display the content entered in that contact's respective field. - the Name f...

Controls on chart sheets
I count myself a fairly competent user of Excel, but I've just started expanding my knowledge thanks to some of the web sites out there (including Jon Peltier's fine examples). I was delighted with Rob Bovey's labelling add-in, which gives Excel something that I have missed ever since my firm stopped using Lotus 123. Now I am experimenting with controls in Excel, and I have added a scroll bar to interactively change the appearance of an embedded chart. My problem is that I don't usually use charts embedded in a spreadsheet; I prefer to have them on their own pages. ...

combination chart (different graph types)
Hey, I have a line graph, now I want to extend this with a high/low/.. graph type. But I'm not able to do this I need this because I want to add a 95% realibilty interval. Thanks Hi Piet - You can add the high and low series (or 5%-95%) to your line chart, format these new series to be invisible (no markers or connecting lines), then format any series, and on the Options tab, select High-Low Lines. Excel draws a high-low line from the highest value to the lowest value among all the points at a given X category (regardless of which series happens to be highest or lowest). This onl...

report variables from form list
I have a table that lists shifts(shiftdetails) including their date/time. I have a listbox that is a query of those and shows the last 10 shifts. I would like the user to select one shift from the listbox, and hit a button to generate a report using shiftid as a variable. I currently have the report working, along with the where condition. I just can not figure out how to pass the selected records shiftid value to it. Private Sub previewrep_Click() On Error GoTo Err_previewrep_Click Dim stDocName As String stDocName = "logs" DoCmd.OpenReport stDocName, acPrevie...

new Q&A topic for MBS site: RMS receipt/report customization
Hi everyone, Michelle here, from the Business Solutions Community. Many of you wrote to me with questions on RMS item management, and I thank you for all your messages. Microsoft SDET Jimmy Wong has provided me with his answers, and they're now with Web our production team, which is busily shaping them into a spiffy new Web page. Next week, we'll post the Web page containing Jimmy's answers to the community site (www.microsoft.com/businesssolutions/community). Meanwhile, I'm here to ask for your questions for another Q&A on RMS. The topic is CUSTOMIZING RECEIPTS AN...

customize does not work #2
I have 1 WinXP computer that has Outlook 2000 installed as the mail client. When I select "Customize Outlook Today" nothing happens. I do not get the chance to change any of the default configuration of the Outlook Today page. Does anyone know the reason for this? thx in advance. >-----Original Message----- >I have 1 WinXP computer that has Outlook 2000 installed >as the mail client. When I select "Customize Outlook >Today" nothing happens. I do not get the chance to change >any of the default configuration of the Outlook Today >page. Does anyo...

Campaign / Marketing List Problems
I have a couple of issues around my Campaigns / Marketing Lists and hoped someone may be able to help. N.B. I am currently using Scribe for data migration 1] I can migrate Campaign data and also Marketing List data but I cannot relate the two to each other as Scribe does not expose the CampaignItemBase or CampaignActivityItemBase entities. I do not believe this would be possible using the DMF either. I also need access to these entities to add products to Campaigns. Any suggestions? 2] I need to migrate in excess of 6 million Marketing List Members. Scribe will take c.4 weeks...

Accessing Autofilter Lists
Is there a way (programmatically) to access the drop down lists that are created for each column when autofiltering is turned on? I took a quick look at the Autofilter object of a worksheet that has autofilter turned on, but had no luck finding anything that looked like a collection of lists. Thanks, Eric ...

Protect all sheets at once
I have a workbook with about 30 pages. I want to be able to protect and unprotect them all at once. I cannot select all sheets and then protect them because the option will not be available. Any Ideas? TIA Wayne ~me~ wrote: > I have a workbook with about 30 pages. I want to be able to protect and > unprotect them all at once. I cannot select all sheets and then protect them > because the option will not be available. Any Ideas? > TIA > Wayne I have a couple of macros I use to protect/unprotect each worksheet in large workbooks. I have them assigned to buttons on a custom ...

Centering Word Art on a sheet
I am trying to center a word I ased in Word Art. How do I center it to the page? ...

Cannot get a list of users from Exchange 5.5
Below is the log showing an attempt to get a list of vailable users from an Exchange server 5.5 SP4 (MAILSERVER) using LDAP. It always comes up empty! Can someone please tell me what might cause the problem? What do I need to setup in Exchange 5.5 to make this work? Thanks Bill 4/12/2004 11:13:45 AM[][Loading current user array...] 4/12/2004 11:13:45 AM[][Loading current available user array...] 4/12/2004 11:13:50 AM[][ADSIDir = LDAP://MAILSERVER] 4/12/2004 11:13:50 AM[][authenticating with username: MYDOMAIN\Administrator] 4/12/2004 11:14:16 AM[][ADsGetObject called ok.] 4/12/2004 11:14:16 ...

How can i change the font size of the items in a list
Hi, I have a list with some items, But they look really tiny. Is there a way to change the font size of the list items? Thnx If these are data validation dropdown lists, you can't change the font size. There are a couple of workarounds here: http://www.contextures.com/xlDataVal08.html#Larger If it's a combobox from the Control toolbox, you can change the font size by switching to Design view, opening the Properties window, and changing the Font properties. If it's a combobox from the Forms toolbar, you can't change the font size. Kiran wrote: > Hi, > I have ...

Formula to return value from multiple sheets if volume matched
I have a worksheet with average unit prices for <=50k, <=100k,<=200k,<=300k,<500k and am creating a cover worksheet which will pull the relevant unit cost based on the volume entered. EG, I have a volume of 150k so I want the formula to take the price from the <=100k sheet. I originally did the formula for sum(if xxx) but it added each unit price instead of looking for the correct volume price. Tried this but have an error - anyone able to help me ? =if(IF(E8<=50000,'>=50k pricing sheet'!AB7,IF(E8>=199999,'>=100k pricing sheet'...