Prev: Autofill of Cell based on contents in non adjacent cell
Next: Problem opening an Excel file from code
From: John M on 31 Mar 2010 10:24
Hello,

I am trying to set up a way to track information in a "tracking tab" for new
tabs that I add for quotes on new business. So, we have a basic template
that we copy 2-3 times a day to develop new quotes. I want to extract key
info from each quote in this tracking tab (date of quote, customer, $ amount,
etc.).

The problem I have is that each time I add a new tab I need to manually copy
my formula down one line in the tracking tab and edit the formulas to link to
the new tab.

Is there a way to set up the formulas in the tracking tab so that when we
add a new tab for a quote the summary information will automatically populate
a new line in the tracking tab.

Any help is much appreciated.

Regards,
John M

From: Gord Dibben on 31 Mar 2010 13:12
Sounds a job for the Template Wizard with Data Tracking.

You create a Template workbook for your quotes.

Each new quote information is added to a database workbook.

The new quote is saved as its own workbook.

It is difficult to obtain the Wizard on-line but if you email me I can send
you a copy.

Was designed for earlier version of Excel but I have suceesfully used it in
Excel 2003 and 2007.

My email is gorddibbATshawDOTca change the obvious.


Gord Dibben MS Excel MVP

On Wed, 31 Mar 2010 07:24:02 -0700, John M <JohnM(a)discussions.microsoft.com>
wrote:

>Hello,
>
>I am trying to set up a way to track information in a "tracking tab" for new
>tabs that I add for quotes on new business. So, we have a basic template
>that we copy 2-3 times a day to develop new quotes. I want to extract key
>info from each quote in this tracking tab (date of quote, customer, $ amount,
>etc.).
>
>The problem I have is that each time I add a new tab I need to manually copy
>my formula down one line in the tracking tab and edit the formulas to link to
>the new tab.
>
>Is there a way to set up the formulas in the tracking tab so that when we
>add a new tab for a quote the summary information will automatically populate
>a new line in the tracking tab.
>
>Any help is much appreciated.
>
>Regards,
>John M

From: Tim Williams on 1 Apr 2010 00:06
How about something like this (in the tracking worksheet code module)

Tim

'********************************
Private Sub Worksheet_Activate()

Dim s As Excel.Worksheet
Dim r As Long


Me.Range("A2:C50").ClearContents
r = 2
For Each s In ThisWorkbook.Worksheets
If s.Name <> "TemplateSheet" Then
If s.Name <> Me.Name Then
With Me.Rows(r)
.Cells(1).Value = s.Name
.Cells(2).Value = s.Range("B3").Value
.Cells(3).Value = s.Range("C5").Value
End With
r = r + 1
End If
End If
Next s

End Sub
'*******************************


"John M" <JohnM(a)discussions.microsoft.com> wrote in message
news:B83E0E91-66ED-4FCD-9EDE-F88AAFD9013F(a)microsoft.com...
> Hello,
>
> I am trying to set up a way to track information in a "tracking tab" for
> new
> tabs that I add for quotes on new business. So, we have a basic template
> that we copy 2-3 times a day to develop new quotes. I want to extract key
> info from each quote in this tracking tab (date of quote, customer, $
> amount,
> etc.).
>
> The problem I have is that each time I add a new tab I need to manually
> copy
> my formula down one line in the tracking tab and edit the formulas to link
> to
> the new tab.
>
> Is there a way to set up the formulas in the tracking tab so that when we
> add a new tab for a quote the summary information will automatically
> populate
> a new line in the tracking tab.
>
> Any help is much appreciated.
>
> Regards,
> John M
>


 | 
Pages: 1
Prev: Autofill of Cell based on contents in non adjacent cell
Next: Problem opening an Excel file from code