Auto-update Fill Series in column that has randomly spaced blank c

Hi,

I have a worksheet of projects listed by quarter. A cell (say A6) contains 
QTR 1, followed by cells in column A filled with a series (1,2,3...say up to 
12). Then comes a blank row followed by a row containing words "QTR 2" and 
then the fill series continues from where it left under QTR 1 i.e. 
13,14,15...say up to 18. And so on for QTR 3 and QTR 4.

When a new project is added, I want to be able to add a row and with minimal 
steps, want the fill series below this added new project, to update.

When I delete a row (project), I want the fill series to automatically 
update below the deleted project.

I have searched in various discussions and =A6+1 or =ROWS($1:1) solutions 
that I saw do not work in the situation explained above.

I am working in Excel 2003 with Vista but if someone knows how to accomplish 
this in 2003 and XP, I would greatly appreciate the help.

Thanks,
Khind
0
Utf
2/4/2010 6:20:01 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
559 Views

Similar Articles

[PageSpeed] 30

Khind -

This could be easier if the calculated cells were not in the same column as 
the QTR 1, QTR 2, etc. cells.  This is one solution that is pretty quick.

Put this formula in cell A7 (Assuming QTR 1 is in A6):

=IF(A6="QTR 1",1,IF(LEFT(A6,3)="QTR",A4+1,A6+1))

Then copy/paste it to all relevant cells in column A, skipping the cells 
with QTR in them.

You only need to remove the contents of the last cells before QTR 2, QTR 3, 
and QTR 4.

Going forward, when you add a row, just copy/paste the formula from one of 
the cells in column A with this formula to the new rows plus the row below 
any newly-added row.  When you delete a row, copy the formula from one of the 
cells above the deleted row to the row that used to be below the deleted row. 
 Note that all the rows below the deleted row will have #REF in it until you 
paste into just the first cell with #REF, which is the one replacing the 
deleted row.

-- 
Daryl S


"Khind" wrote:

> Hi,
> 
> I have a worksheet of projects listed by quarter. A cell (say A6) contains 
> QTR 1, followed by cells in column A filled with a series (1,2,3...say up to 
> 12). Then comes a blank row followed by a row containing words "QTR 2" and 
> then the fill series continues from where it left under QTR 1 i.e. 
> 13,14,15...say up to 18. And so on for QTR 3 and QTR 4.
> 
> When a new project is added, I want to be able to add a row and with minimal 
> steps, want the fill series below this added new project, to update.
> 
> When I delete a row (project), I want the fill series to automatically 
> update below the deleted project.
> 
> I have searched in various discussions and =A6+1 or =ROWS($1:1) solutions 
> that I saw do not work in the situation explained above.
> 
> I am working in Excel 2003 with Vista but if someone knows how to accomplish 
> this in 2003 and XP, I would greatly appreciate the help.
> 
> Thanks,
> Khind
0
Utf
2/4/2010 7:44:01 PM
Daryl,

Thanks for the tip. If I do not have QTR1, QTR2, etc. in this column then it 
would leave 2 cells empty between each list of projects. I want to use the If 
function to say that if the preceeding cell is empty, pick up the number from 
2 cells above and add 1. 

I do not know how to specify 'blank' in an IF function. For example my QTR 1 
projects start in cell A13 and in cell A14 if I use
=IF(A13="",A11+1,A12+1)
then the formula returns "1" in cell A14. If I copy this to subsequent 
cells, I get 2,2,3,3,4,4... The numbers appear twice.

In an IF function, how do I specify logical test to be an empty cell (or a 
cell containing any value)?

Thanks.

"Daryl S" wrote:

> Khind -
> 
> This could be easier if the calculated cells were not in the same column as 
> the QTR 1, QTR 2, etc. cells.  This is one solution that is pretty quick.
> 
> Put this formula in cell A7 (Assuming QTR 1 is in A6):
> 
> =IF(A6="QTR 1",1,IF(LEFT(A6,3)="QTR",A4+1,A6+1))
> 
> Then copy/paste it to all relevant cells in column A, skipping the cells 
> with QTR in them.
> 
> You only need to remove the contents of the last cells before QTR 2, QTR 3, 
> and QTR 4.
> 
> Going forward, when you add a row, just copy/paste the formula from one of 
> the cells in column A with this formula to the new rows plus the row below 
> any newly-added row.  When you delete a row, copy the formula from one of the 
> cells above the deleted row to the row that used to be below the deleted row. 
>  Note that all the rows below the deleted row will have #REF in it until you 
> paste into just the first cell with #REF, which is the one replacing the 
> deleted row.
> 
> -- 
> Daryl S
> 
> 
> "Khind" wrote:
> 
> > Hi,
> > 
> > I have a worksheet of projects listed by quarter. A cell (say A6) contains 
> > QTR 1, followed by cells in column A filled with a series (1,2,3...say up to 
> > 12). Then comes a blank row followed by a row containing words "QTR 2" and 
> > then the fill series continues from where it left under QTR 1 i.e. 
> > 13,14,15...say up to 18. And so on for QTR 3 and QTR 4.
> > 
> > When a new project is added, I want to be able to add a row and with minimal 
> > steps, want the fill series below this added new project, to update.
> > 
> > When I delete a row (project), I want the fill series to automatically 
> > update below the deleted project.
> > 
> > I have searched in various discussions and =A6+1 or =ROWS($1:1) solutions 
> > that I saw do not work in the situation explained above.
> > 
> > I am working in Excel 2003 with Vista but if someone knows how to accomplish 
> > this in 2003 and XP, I would greatly appreciate the help.
> > 
> > Thanks,
> > Khind
0
Utf
2/4/2010 8:33:01 PM
Khind -

If QTR 1, QTR 2, etc. were in a different column, then it would be easy to 
drag the appropriate formula down all cells.

You can drag or copy/paste to all cells ignoring the blank cell.  The 
formula I gave you already takes into account the blank cell above QTR 2, QTR 
3, and QTR 4.  If you copy it into all cells other than the QTR and blank 
cells, it will count things correctly.

As for checking to see if cells are blank, there are a couple ways.  
=ISBLANK(A6) will return TRUE if the cell is truly blank, but if there are 
space characters in it, it will return FALSE, even though the cell looks 
blank.

You can use something like =IF(LEN(TRIM(A7))=0,TRUE,FALSE) to return TRUE if 
the cell is empty or has just spaces in it.

-- 
Daryl S


"Khind" wrote:

> Daryl,
> 
> Thanks for the tip. If I do not have QTR1, QTR2, etc. in this column then it 
> would leave 2 cells empty between each list of projects. I want to use the If 
> function to say that if the preceeding cell is empty, pick up the number from 
> 2 cells above and add 1. 
> 
> I do not know how to specify 'blank' in an IF function. For example my QTR 1 
> projects start in cell A13 and in cell A14 if I use
> =IF(A13="",A11+1,A12+1)
> then the formula returns "1" in cell A14. If I copy this to subsequent 
> cells, I get 2,2,3,3,4,4... The numbers appear twice.
> 
> In an IF function, how do I specify logical test to be an empty cell (or a 
> cell containing any value)?
> 
> Thanks.
> 
> "Daryl S" wrote:
> 
> > Khind -
> > 
> > This could be easier if the calculated cells were not in the same column as 
> > the QTR 1, QTR 2, etc. cells.  This is one solution that is pretty quick.
> > 
> > Put this formula in cell A7 (Assuming QTR 1 is in A6):
> > 
> > =IF(A6="QTR 1",1,IF(LEFT(A6,3)="QTR",A4+1,A6+1))
> > 
> > Then copy/paste it to all relevant cells in column A, skipping the cells 
> > with QTR in them.
> > 
> > You only need to remove the contents of the last cells before QTR 2, QTR 3, 
> > and QTR 4.
> > 
> > Going forward, when you add a row, just copy/paste the formula from one of 
> > the cells in column A with this formula to the new rows plus the row below 
> > any newly-added row.  When you delete a row, copy the formula from one of the 
> > cells above the deleted row to the row that used to be below the deleted row. 
> >  Note that all the rows below the deleted row will have #REF in it until you 
> > paste into just the first cell with #REF, which is the one replacing the 
> > deleted row.
> > 
> > -- 
> > Daryl S
> > 
> > 
> > "Khind" wrote:
> > 
> > > Hi,
> > > 
> > > I have a worksheet of projects listed by quarter. A cell (say A6) contains 
> > > QTR 1, followed by cells in column A filled with a series (1,2,3...say up to 
> > > 12). Then comes a blank row followed by a row containing words "QTR 2" and 
> > > then the fill series continues from where it left under QTR 1 i.e. 
> > > 13,14,15...say up to 18. And so on for QTR 3 and QTR 4.
> > > 
> > > When a new project is added, I want to be able to add a row and with minimal 
> > > steps, want the fill series below this added new project, to update.
> > > 
> > > When I delete a row (project), I want the fill series to automatically 
> > > update below the deleted project.
> > > 
> > > I have searched in various discussions and =A6+1 or =ROWS($1:1) solutions 
> > > that I saw do not work in the situation explained above.
> > > 
> > > I am working in Excel 2003 with Vista but if someone knows how to accomplish 
> > > this in 2003 and XP, I would greatly appreciate the help.
> > > 
> > > Thanks,
> > > Khind
0
Utf
2/5/2010 6:00:05 PM
Reply:

Similar Artilces:

if cell is text move left one column
ColB is a long list with sections names followed by category codes I need to move the text into colA leaving colB with codes only (all numbers) ColB. Doors 940590 555998 447006 447008 810697 810705 810706 810707 Windows 619435 525691 525692 Try Sub Macro1() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row If Not IsNumeric(Range("B" & lngRow)) Then Range("A" & lngRow).Value = Range("B" & lngRow).Text Range("B" & lngRow).Value = "" End If Next End Sub -- Jacob ...

Worksheet Auto update
I need to find a way to automate a process. Is there a way to automatically replace the content of a worksheet with the content of another one? Every morning I get a sales report in excel for the previous day sales. I save it in a folder and then I do a pivot table on this sheet to determine sales by product category for example. The following day, I open the previous day file, replace the sales report with the new one and then refresh my pivot table. Is there a way to have my sales report update anytime I get a new sales report? To be more clear I have a workbook with two tabs: Pivot...

Time Series
I have about 30000 lines of data that looks like so: Time Count 93000 447 93001 1020 93002 1438 93003 2177 93004 3042 93005 3467 93006 2913 93007 3341 93008 3547 93009 2908 93010 3512 93011 4090 Where ColA is time. I am having trouble creating a graph with time on the x-axis and count on the y-axis. Thank you in advance. You need to make an XY chart But with 30,000 data points you could run into trouble; Help says you can 32,000 data points in a single data series. However, if you want markers it will look like heck. You could limit the data like this: assume y-values in B2:B30001 in ...

100% stacked column chart shows wrong information
I wonder if someone can help me. I have created a table that tracks the profitability of a particular project and I’m using the table as a source for a 100% Stacked Column graph. The source data for the graph is in three rows: • B21:M21 - contain the names of the months from Nov – Oct • B22:M22 - money taken in • B24:M24 - profit B23:M23 contains the money paid to supplier each month. I need this in the table but I don’t need to show it in the graph. I just want to show profit as a proportion of what we bill to customers on that project. My problem is that the graph seems to show in...

[ANN] Office 11.3.8 update
Hi All, A new security update just came out. I suspect it requires 11.3.7 to install. Anyway, time to run the Microsoft Updater or to grab it from <http://www.microsoft.com/mac/downloads.aspx> Corentin -- --- Mac:MS MVP http://www.cortig.net/wordpress/ --- http://www.mvps.org - http://mvp.support.microsoft.com MVPs are not MS employees - Les MVP ne travaillent pas pour MS Remove "NoSpam" to e-mail me - Retirez "NoSpam" pour m'�crire I just let the Microsoft AutoUpdate do its thing. The only Office...

Can SUMPRODUCT be used for entire column?
This formula results in a numeric result: =SUMPRODUCT((A1:A20)*(B1:B20="b")) but this formula results in a #NUM! result: =SUMPRODUCT((A:A)*(B:B="b")) Which means I need to specify the length of the columns, which may grow over time. Any way to do this for the entire column, without having to specify the length of the column? XL07 removed the limitation on array formulas (which SUMPRODUCT is, even though it doesn't require CTRL-SHIFT-ENTER) and entire columns. For pre-XL07, one can use =SUMPRODUCT(A1:A65535, --(B1:B65536="b")) to get all but on...

formatting auto replies
Does anyone know of a method that allows outlook 2k2 to format automatic replies regardless of the format of the original message? I setup this automatic response rule that replies with a template, but problem is, when replying to http clients (hotmail or yahoo), the message at the receiver's end has words totally misplaced, all over the page. Any suggestions would be appreciated. ...

Office 2007 forms
I am creating a form with office 2007, will those people who do not use office 2007 be able to fill in my form? should I save it in a particular format? thanks Provided you start from the normal template, don't use fonts that were introduced with Word 2007, and save the form in Word 97-2003 document format, anyone with Word 97 or later should be able to open it. Use only the legacy form fields, to which end http://gregmaxey.mvps.org/Classic%20Form%20Controls.htm will make things easier. -- <>>< ><<> ><<> <>>< ><<...

C# Adding Days to a Date
Hello, I have 2 objects: objContract.activeon and objContract.expireson. I am trying to add 364 days to objContract.activeon and assign it to the value of objContractexpireson. ---------------------------------------------------------- // Contract Start Date DateTime dt = DateTime.Now; objContract.activeon = new CrmDateTime(); int iFound = 0; string sTemp = ""; if (objAccount.paymenttermscode.Value == 1) // due on receipt - use Todays Date { objContract.activeon = objInvoice.CFDinvoicedate; } else // ...

Problem with Update function of CRecordSet class
Hi All When I use from Update function of CRecordSet class, it apply to table after a few time(for example 1 second) when I need to see it at time. Thanks ...

auto reply to incoming messages
SOS. I am having trouble with my Outlook 2002. I am trying to set up an "out of office assitant". It says i need to have Microsoft Exchange for this to work. So i went back to my Office XP disk and tried adding it in seperately and then i tried reinstalling Office XP, and still nothing. Anyone have any suggestions, thanks! Paul "Paul" <hiltonjp1@comcast.net> wrote in message news:047CCF7C-4B62-4807-A784-238663B88EF7@microsoft.com... > SOS. I am having trouble with my Outlook 2002. I am trying to set up an "out of office assitant". It says i need ...

Sorting on Column headers
I have a spreedsheet that someone else created and I need to take the Column headers, which are in date form, and rearrange them in chronological order. Can this be done without cutting and pasting? I really need to save time with this project if possible. Thanks!:) --- Message posted from http://www.ExcelForum.com/ > I have a spreedsheet that someone else created and I need to take the > Column headers, which are in date form, and rearrange them in > chronological order. Can this be done without cutting and pasting? I > really need to save time with this project if possib...

Updating a chart Automatically
Hi, i update a spreadsheet with new data from Access everyweek. I have a chart that displays the last thrity days, how do i do this automatically, so i dont have to keep updating the chart manually ? Thanks in advance Jay Jay Richardson wrote: > Hi, i update a spreadsheet with new data from Access > everyweek. I have a chart that displays the last thrity > days, how do i do this automatically, so i dont have to > keep updating the chart manually ? > > Thanks in advance Jay See www.tushar-mehta.com, Dynamic Charts. Dave dvt at psu dot edu ...

Removing random extra spaces
I have a column for comments there are 900 rows (A1:A900) this data is being imported from another source and some users have extra spaces that I want to remove and place in (B1:B900)...I also want to format it so that it is converted from all caps to all lowercase. For example: A1 reads "INT IN A FREE QUOTE ON 50000 CASH FOR DEBT CONSOLIDATION" B2 reads "int in a free quote on 50000 cash for debt consolidation" -- jermsalerms ------------------------------------------------------------------------ jermsalerms's Profile: http://www.excelforum.com/mem...

Updating Credit Card Information
I have been trying counless times to update my credit card information from several Banks, but Money states that the system is unaavaible or the transaction cannot be completed at this time. What can I do to correct this problem. On 2005-03-23, Lou <Lou@discussions.microsoft.com> wrote: > I have been trying counless times to update my credit card information from > several Banks, but Money states that the system is unaavaible or the > transaction cannot be completed at this time. What can I do to correct this > problem. Well it depends on how long you've been tr...

Opening Multiple Web Links in a Column
Hi, I am very new to using web links in excel. A task I do very often is open a list of different websites that are in various columns in an excel spreadsheet. I am quite sure I am doing it the slowest way possible, so I need some help please. Basically I have been clicking on one link at a time. When I do this, the first website opens and excel automatically minimizes, then I have to go re maximize excel and then click the next web link and the same thing happens, etc... very time consuming. I am wondering if there is a way, either through Excel or whatever means necessary, to open all...

Customize columns in 'Marketing List Members'
I can't figure out where one can customize the columns used within the "marketing list" entitry when you click the 'marketing list members' on the left side to show the members. I want to add some columns, like Email. Screenshot: http://i355.photobucket.com/albums/r469/canadaka_bucket/marketing_list_members.jpg Just read the Posting on the Microsoft CRM Team Blog. <canadaka@gmail.com> schrieb im Newsbeitrag news:306584c6-2043-4962-b12a-d0b9287684bb@b31g2000prb.googlegroups.com... > I can't figure out where one can customize the columns used within the >...

Removing spaces from text #4
I'm in excel and i have a bunch of text data that has an extra space at the end of the text on the right hand side for each cell. Is there any easy way to remove this space? Use the TRIM() function. -- Kind regards, Niek Otten "lj" <lj@spu.edu> wrote in message news:1144876429.220961.309040@j33g2000cwa.googlegroups.com... > I'm in excel and i have a bunch of text data that has an extra space at > the end of the text on the right hand side for each cell. Is there any > easy way to remove this space? > I tried using that function but the results st...

how to automatically update inventory list with sales
Please provide the help on how to update the inventory list when some items are sold. Do you want to check this out while you wait for an answer? http://office.microsoft.com/en-us/FX011429711033.aspx Epinn "lalani" <lalani@discussions.microsoft.com> wrote in message news:07A10BC7-97FB-4E93-A686-CAAE3AA2DE88@microsoft.com... > Please provide the help on how to update the inventory list when some items are > sold. The page that Epinn points out is probably as good as any. Excel is a rather poor tool for trying to track inventory. You'll notice that the actual...

Character Length in a column
I have a column where every cell must have 14 characters. Lets say have the name Angel. I need 9 more charcters. Next cell down have the word cake. I need 10 more charcters.How do I automatciall insert the spaces I need to have 14 charcters. HELP PLEASE!!!!! GOIN NUTS ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements One way: Do you care about what characters are added? I'l...

Auto Delete the e-mail
Hi all We are using Microsoft Exchange 2003 and Outlook 2003. One mail box calls "Sales", i configured the achieve function for this mail box, this function will automatically delete mail if the mail is order than 7 days. But this function is not running if the outlook closed, is it possible this function running on server only, no need to open Microsoft Outlook for running the achieve function in client PC ? Philip In news:uApBq0j8FHA.2800@TK2MSFTNGP10.phx.gbl, Philip Tsang <philip@sib-infotech.com> typed: > Hi all > > We are using Microsoft Exchange 2003...

Freeze the side column/top row & scroll others
what is the function to set (lock in or freeze) the first column and / or top row of a spreadsheet, so the words and numbers remain in the same place as you scroll the other columns and rows. (so you can add more columns..yet keep the main information in the first column/row) Freeze Panes..... In older versions of Excel, it is under Window. In 2007 version of Excel, it is under View. You first select a cell, then activate the command. Excel uses the selected cell's upper left corner to define the freeze point. Play with it. You can also Unfreeze panes that were fro...

Zero to appear as blank
I have a VLOOKUP formula below. When the value is zero, I want the result to be blank. What do I do. =IF(A10="","",VLOOKUP(A10,detail,2)) Thnx Prakash..... =if(a10="","",if(vlookup(a10,detail,2)=0,"",vlookup(a10,detail,2))) Prakash wrote: > > I have a VLOOKUP formula below. When the value is zero, I want the result to be blank. What do I do. > > =IF(A10="","",VLOOKUP(A10,detail,2)) > > Thnx > Prakash..... -- Dave Peterson ec35720@msn.com In addition to modifying your formula as Dave suggeste...

Money Updates #2
Was wondering about money updates. What exactly is the program downloading during the update process? And if the program is updated on a daily basis, why would I ever need to upgrade to a newer version? In microsoft.public.money, invalid wrote: >Was wondering about money updates. What exactly is the program downloading >during the update process? And if the program is updated on a daily basis, >why would I ever need to upgrade to a newer version? It is mainly changes in online bank setup. Despite appearances is typically does not change anywhere daily. "invalid" <...

referencing lines and series points
what is the VBA code for referencing to individual points in a line on line graph? i would like to create a macro to change indiviual points shapes and the color or the lines in between them to different color depending on different parameters. is this possible -- medicenpringle ----------------------------------------------------------------------- medicenpringles's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1645 View this thread: http://www.excelforum.com/showthread.php?threadid=39101 Ther easiest way to do this is to first record a macro doing what y...