How can I autofill a series to reference non adjacent cells?

I need to create a list of values by referencing a series of non adjacent 
cells (they are separated by a set number of rows each but in the same 
column).  For example the cells should read something like  =b2  =b4   =b6  
=b8.  Of course I could type these in individually but threre are something 
like 100 values.  Is there a way to auto fill this?
0
Microcell (1)
6/30/2005 2:16:04 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
531 Views

Similar Articles

[PageSpeed] 56

Look at the indirect function for your example 
=indirect("B"&(row()*2))
copied down a column will give you a list for non adjacencies.

"Microcell" wrote:

> I need to create a list of values by referencing a series of non adjacent 
> cells (they are separated by a set number of rows each but in the same 
> column).  For example the cells should read something like  =b2  =b4   =b6  
> =b8.  Of course I could type these in individually but threre are something 
> like 100 values.  Is there a way to auto fill this?
0
BJ (832)
6/30/2005 8:49:02 PM
Reply:

Similar Artilces:

Lookup and Sum in same cell
SHNAME ENTDATE SYMBOL QTY RATE COMM C78 1/24/2005 CSCO 1100 0.018 $20 C78 1/28/2005 CSCO -5439 0.018 $100 C78 2/23/2005 CSCO 50 0.017 $1 C78 4/5/2005 CSCO -7892 0.018 $140 C78 4/13/2005 CSCO 3786 0.018 $69 I am trying to create a lookup formula that will search by SHNAME, find each entry and sum the COMM column in one cell: SHNAME COMM C78 $360 This worksheet is thousands of rows long with multiple rows for each SHNAME. Thanks in advance -- Potatosalad2 ------------------------------------------------------------------------ Potatosalad...

Cell Border Line Width
Is it possible to specify the cell border line width using Excel 2002? The "Border" tab in the "Format Cells" dialog box allows me to choose predefined line widths but I need a width other than what is offered. Is there a way to do this? Thanks! Adam Not with the borders property...you are restricted to xlHairline, xlThin, xlMedium, xlThick"....... You could *possibly* fake it with the AddLines method of the worksheet but I wouldn't really want to try.....;o) OJ ...

How can a hyperlink be opened by pressing Enter.
In Excel 2003 after inserting a hyperlink to open a picture file stored in the local drive the user needs to be able to open the file by pressing Enter on the cell that contains the hyperlink rather than by clicking on it with the mouse. This feature is enable in Excel 2000 but it the not work in 2003. (the file type or location does not matter). In short, how can a hyperlink be opened by pressing Enter in Excel 2003. Thank you in advance for your help, Carolina I think that this went away in xl2002. You could send a message to mswish@microsoft.com asking for this to come back. Carol...

HELP!!! Can't Apply Payment for Posted Invoice while Unposted in C
One user was entering in a transaction in the Sales Transaction Entry Window. She had not saved or put the transaction in a batch. The power went out, and she started up the computer when the power come back. She could not see the transaction in the Sales Transaction Entry for that Customer. She could see the transaction in the Collection Main window for that Customer but in the Unposted Sales. I tried to view the transaction from the SmartList but it is says it has been posted. So the user goes ahead and post thru the GL from the Series Post window. She still can not view t...

enabling and desabling autofiler on protect sheet
Hi all, how can i give the option in the VBA command to dynamicly enable or desable autofilter on a row when the cells are protected for any editing ...

why i can not run excel2000 in windows XP?
i have installed office2000 in windows XP correctly, but i can not open it. after the splash window, there are nothing displayed. please tell me why i met that mistake and how to resolve it. thank you for your help The following MSKB article may help: OFF2000: Program Quits Immediately After Starting When SR-1/SR-1a Update Is Applied http://support.microsoft.com/default.aspx?id=255503 bugbug wrote: > i have installed office2000 in windows XP correctly, but i > can not open it. after the splash window, there are nothing > displayed. > please tell me why i met that mistake ...

Locked cells in IE
Hi, I need to open an Excel document from a web page. Active X controls are restricted and I cant pass the document as a parameter in a shortcut to the Excel executable because it needs to be generic (and would not e in this case). The problem Im having is that the Excel document contains locked cells that need to remain locked. When the document opens within the internet explorer interface the locked cells are not visible and the spreadsheet looks a bit disjointed. Does anyone know another way to open the document from a web page or how to make the cells visible? Many many thanks...

Can I Make Calendar Overlay my Default View?
I have my personal calendar and a shared public calendar. I have found how to go to the calendar shortcut, select the two calendars, and then overlay them. However, if I go to the folder view that overlay view goes away. Also, if I restart Outlook, I have to go through all of the work to reset that view up again. I found the "save calendar choice" freeware application mentioned in this forum elsewhere but this did not seem to really do the trick. For one thing, it only saves the view choice as side-by-side. Really, what I want to do is save my default calendar view to always sho...

UPPER CASE formatting a cell
I receive excel data from a number of sources to be combined together in a single sheet. I would like to format a column of cells to be all upper case but not use the =UPPER() function. I'd like to format a cell to force any lower case char to upper case automatically when data is entered. Custom formatting as >CCCC doesn't work. Can this be done? Conversely, how can I force cell A1 to be UPPER CASE by using =UPPER(A2), then remove the reference to A2 when printing the result? I don't want the cell A2 to print. This I forget how to do. Hi you'll need VBA for this. ...

find last cell in column
Hi, Apart from using VBA, is there any way to write a formula that will find the last used cell in a column of 15 cells? In the first set below, it would be ..388, in the second set it would be .133. Any help would be appreciated. 0.006 0.000 0.036 0.125 0.133 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.313 0.388 0.006 0.000 0.036 0.125 0.133 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 ...

how do make a row non numbered?
Trying to make a row not have a numbered value on the left side. I don't know where you're goinng with this, but.... Those numbers are used for reference purposes by excel. If you'd like to display something different, maybe this will work for you: Use Col_A to display the "row numbers" you'd prefer. Then, Tools|Options|View tab|Uncheck: Row & Column Headers If you need to display the Column Headers, you'll could put them in Row_1. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Removing number value from a row" wrote: > Tryi...

3d reference
Is it possible to suppress the "0" that is returned when referencing a blank cell in another worksheet? I have used an IF function to ensure blank cells in the past, but that requires a good bit of labor for even a medium sized worksheet. I tried various cell formats, but continue to get "0"s in the linked cell. You could use a custom number format or even Format|conditional formatting. But those will hide the cells that are returning an actual 0. I'd do that extra work: =if('sheet 99'!a1="","",'sheet 99'!a1) ==...

Add more than one series to a pivot chart using VB MS Access continued...
I'm trying to programmatically create a stacked bar pivot chart. Using "Programming Microsoft Office Access 2003" by Rick Dobson, I've created the chart. However, it doesn't distinguish between the different values for the series. Does anyone have any suggestions on how to create a chart using one column containing three values for the series? Essentially, this is a continuation of a previous post: http://groups.google.com/group/microsoft.public.access.formscoding/browse_thread/thread/e23f3506a6d561a0/674295d410a71cf9%23674295d410a71cf9" Any help is greatly appre...

How can comunicate annoucement more agile?
is it any form to replicate the winpoup utility with annoucemen of Micrososft cRM v3.0? ...

Db template that can handle making book indices?
Does anyone know if there is a template that can be used to create printed book indices? The indices are those pages usually found at the back of books that allow you to find information in any book quickly and easily. The difficulty is that some books, as an example even cookbooks, don't have a comprehensive enough index so it would be nice to create one's own esp. for books we use quite frequently where we'd like to create a booklet we tuck into the book so that when we go to do research, we can find anything. As an example, I'd like to use a cookbook because it...

Unread messages don't exist
My Outlook 2003 Unread Messages folder states that I have 11 unread messages in my inbox. I select the Inbox folder from the Unread Messages folder and right click and choose Mark Messages as Read. I'm then presented withe the following info: Cannot mark the items read or unread. The most likely reasons are: You don't have permissions to modify the items etc etc If I hi-light one of the messages and choose delete, I get the following: The item could not be deleted. It was either moved or already deleted, or access was denied. I cannot read the message either. I have run scanpst on...

Tweaking cells
If I have cells that may remain blank, how do I get them to be blank instead of showing a 0? These cells are set for text, not numbers, so I don't understand why a number would show there by default. TIA Steven Connor Hi do you have formulas in these cells and if yes what kind of formulas? -- Regards Frank Kabel Frankfurt, Germany **{Steven}** wrote: > If I have cells that may remain blank, how do I get them to be blank > instead of showing a 0? These cells are set for text, not numbers, so > I don't understand why a number would show there by default. > > TIA >...

can not forward or reply to messages
I was able to send, forward and reply to messages up til today. For some reason I can not forward or reply to messages today. They are greyed out. I am using Microsoft outlook 2007. Any idea how to fix this? And you cannot create new messages either? Probably the same applies to creating new documents in any of the other Office applications too. In that case it means that you haven't activated your copy of Office yet. You can do so in Outlook via Help-> Activate Product... -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto...

Setting up Solver reference in a protected VBAproject
Hello, I want to distribute a Excel 2007 workbookk that contains several macros. I need to protect the VBAproject so that the users cannot modify the code. However, in order to use some of the macros the user needs to enable the Solver Add-in, and for that purpose, he needs to have access to the unprotected VBAProject. Is there a way to setup the Solver Add-in while maintaining the VBA project protected (and without disclosing the password)? Any ideas would be appreciated. Regards, OMER ...

Can desktop be switched in InitInstance()?
Hi, I'd like my app to run in a non-default desktop. I thought I could create a new desktop and switch to it in InitInstance() and then restore the old desktop in ExitInstance(). The code in InitInstance() is as follows: CMyApp::InitInstance() { m_hDeskThreadSav = ::GetThreadDesktop(GetCurrentThreadId()); m_hDeskInputSav = ::OpenInputDesktop(0, FALSE, DESKTOP_SWITCHDESKTOP); m_hDesktop = ::CreateDesktop(L"MyDesktop", NULL, NULL, 0, GENERIC_ALL, NULL); ::SetThreadDesktop(m_hDesktop); ::SwitchDesktop(m_hDesktop); [...] } When app runs I get a new desktop but the a...

Where can I find Exchange EventID Descriptions #2
Hello. I need to create software which analyse MSExchange event log on the fact of occurence critical Exchange Server events. But can't find more or less full description of MS Exchange Server 5.5/2000 events. I've tried to find it in MSDN, Exchange SDK - nothing. Where can I find full description of MS Exchange Server events ID's (eventlog). Thanks. have a look at www.eventid.net they have a great event id resource and event monitors Simon "Igor" <igor_bo@pochta.ru> wrote in message news:266001c3e0dd$4e1948e0$a601280a@phx.gbl... > Hello. > I need to cr...

Can I run proprietary Windows Software on Remote Desktop?
Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC I am using a PowerPC G5 Mac Mini. I am taking a course that requires a minimum of Windows XP or Vista. Can I install the course software on Remote Desktop so that I can use it to take this class, or is this just for using MS Office programs? Also, what is the cost of this service? On 3/24/08 12:03 PM, in article ee95dbc.-1@webcrossing.caR9absDaxw, "boyerml@officeformac.com" <boyerml@officeformac.com> wrote: > Operating System: Mac OS X 10.4 (Tiger) > Processor: Power PC > > I am using a PowerPC G5 Mac ...

Text cell complains about date
I have a column containing ratios like 3/4, 3/0, 5/2, etc. I have them all formatted as text. All of the cells that have a zero as the second number (3/0) get a little green triangle and a warning that I have entered a "date string with only 2 digits for the year". Huh? How is "3/0" a date string with 2 digits for the year? And, why is it complaining about a text field? More importantly, how do I get Excel to stop being so "helpful" and leave my damned text data alone -- unexamined? Remove the warning in Options, Tools>Options>Error Checking in 2003...

how to reverse a series batch posting in the GL only
we have a batch from invoice batches. The GL numbers are wrong.. we want to reverse the GL journal entries only. how can we do this. We will put in a new Journal entry for the correct numbers. don't want any of the details in the invoices touched... thanks -- Linda W. Hi Linda, The backout feature in GL will work only on transactions originated in GL. You can always create a summary correcting entries in GL for the entire invoice batch instead of creating JE per invoice document. Hope this helps! Cheers, Dennis Araullo, MACS Microsoft Certified Techn...

Can't see row numbers or left scroll arrow in Excel spreadsheet
One of my excel spreadsheets won't display the row numbers (half of column "A" also can't be seen); left scroll arrow is not on screen. Other spreadsheets are OK. What to do? Thanks Jim Try Ctrl+F10 to maximize the workbook? "Jim Freund" wrote: > One of my excel spreadsheets won't display the row numbers (half of column > "A" also can't be seen); left scroll arrow is not on screen. Other > spreadsheets are OK. What to do? > Thanks > > Jim ...