$$Excel-Macros$$ Moving Row Data to the Left Forcing Blank Cells to the Right

[John A. Smith]

My daily download has intermittent blank cells per row based on the column
heading.  I need shift all the data to the left leaving the blank cells per
row on the right.  I am interested in a macro or a formula or both. I have
attached a sample file with the required details.


Thank you for your ongoing help with my Excel learning, I really appreciate
you who take time to teach and help.


John



-- 
*John*

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.


Moving Row Data to the Left Forcing Blank Cells to the Right.xlsx
Description: MS-Excel 2007 spreadsheet

Re: $$Excel-Macros$$ Need to Subtract Two Date/Time Cells - Formula Issue

[John A. Smith]

Excellent, that saves a step.  Thank you very much.

John

On Tue, Mar 5, 2013 at 12:05 PM, xlstime  wrote:

> use your formula with value and trim  formula
>
> .
>
> Enjoy
> Team XLS
>
>
>
> On Tue, Mar 5, 2013 at 10:24 PM, John A. Smith wrote:
>
>> I have a download into Excel with start and stop cells which contain the
>> date/time.  I need help making my formula work please to get the NET
>> minutes.
>>
>> Please see my attached problem example.
>>
>> Thank you for being here (again).
>>
>> Very Grateful,
>>
>> John
>>
>> --
>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>> https://www.facebook.com/discussexcel
>>
>> FORUM RULES
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>> 2) Don't post a question in the thread of another member.
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>> 4) Acknowledge the responses you receive, good or bad.
>> 5) Jobs posting is not allowed.
>> 6) Sharing copyrighted material and their links is not allowed.
>>
>> NOTE : Don't ever post confidential data in a workbook. Forum owners and
>> members are not responsible for any loss.
>> ---
>> You received this message because you are subscribed to the Google Groups
>> "MS EXCEL AND VBA MACROS" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to excel-macros+unsubscr...@googlegroups.com.
>> To post to this group, send email to excel-macros@googlegroups.com.
>> Visit this group at http://groups.google.com/group/excel-macros?hl=en.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>>
>>
>
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: $$Excel-Macros$$ Need to Subtract Two Date/Time Cells - Formula Issue

[John A. Smith]

Thank you very much for your helpful swift reply...

John

On Tue, Mar 5, 2013 at 11:55 AM, rajan verma wrote:

> Just remove starting space from the date
>
> On Tue, Mar 5, 2013 at 9:54 AM, John A. Smith wrote:
>
>> I have a download into Excel with start and stop cells which contain the
>> date/time.  I need help making my formula work please to get the NET
>> minutes.
>>
>> Please see my attached problem example.
>>
>> Thank you for being here (again).
>>
>> Very Grateful,
>>
>> John
>>
>> --
>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>> https://www.facebook.com/discussexcel
>>
>> FORUM RULES
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>> 2) Don't post a question in the thread of another member.
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>> 4) Acknowledge the responses you receive, good or bad.
>> 5) Jobs posting is not allowed.
>> 6) Sharing copyrighted material and their links is not allowed.
>>
>> NOTE : Don't ever post confidential data in a workbook. Forum owners and
>> members are not responsible for any loss.
>> ---
>> You received this message because you are subscribed to the Google Groups
>> "MS EXCEL AND VBA MACROS" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to excel-macros+unsubscr...@googlegroups.com.
>> To post to this group, send email to excel-macros@googlegroups.com.
>> Visit this group at http://groups.google.com/group/excel-macros?hl=en.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>>
>>
>
>
>
> --
> Regards
> Rajan verma
> +91 9158998701
>
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

$$Excel-Macros$$ Need to Subtract Two Date/Time Cells - Formula Issue

[John A. Smith]

I have a download into Excel with start and stop cells which contain the
date/time.  I need help making my formula work please to get the NET
minutes.

Please see my attached problem example.

Thank you for being here (again).

Very Grateful,

John

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.




Time and Date Subtraction Formula Needed.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ Re: Conditional Format Entire Row if Any Column has a specific value

[John A. Smith]

Thank you Viper ever so much!!!

John

On Wed, Feb 6, 2013 at 1:20 AM, The Viper  wrote:

> pfa
>
> you can modify the range on formula as desired.
>
>
> On Wed, Feb 6, 2013 at 9:34 AM, John A. Smith wrote:
>
>> Paul,
>>
>> #1) By using conditional formatting, highlight any row the EXACT target
>> value is in regardless of the column it's in
>> #2) Has to be an EXACT match
>> #3) Helper columns are okay
>> #4) Rows quantity varies but can be between several hundred to
>> approximately 8-9,000
>> #5) Can be either formula or VBA
>>
>> Each row is an individual event and I need to quickly see only the rows
>> where somewhere in the 180 columns is a specific target value (I wanted to
>> make cell A1 where you would enter the target value to trigger the
>> highlighting).
>>
>>
>> Thank you for your help.
>>
>> John
>>
>>
>>  On Tue, Feb 5, 2013 at 3:44 PM, Paul Schreiner 
>> wrote:
>>
>>>   questions:
>>> #1) What do you mean by "highlight the row"?
>>> #2) will this be an "exact" match? or partial contents of the cells?
>>> #3) can we add a "helper" column and filter?
>>> #4) How many rows are we talking about?
>>> #5) does it have to be a FORMULA? can you use VBA?
>>>
>>> If we add a new column (either at the beginning or end)
>>> then we can use a HLookup function to search for a matching value,
>>> combine that with aniferr() function, and we can then "flag" the row
>>> that contains the value.
>>> using Filters, then you can display only that row.
>>>
>>> This won't "highlight" the row
>>> and it won't work well with a large number of rows.
>>>
>>> I could easily create a VBA macro that could
>>> clear the highlighting
>>> search for the specified value
>>> highlight the row (in any way you wish)
>>>
>>> but that is not a formula.
>>>
>>>
>>>
>>> *Paul*
>>>
>>> -
>>> *“Do all the good you can,
>>> By all the means you can,
>>> In all the ways you can,
>>> In all the places you can,
>>> At all the times you can,
>>> To all the people you can,
>>> As long as ever you can.” - John Wesley
>>> *-
>>>
>>>
>>>  --
>>> *From:* JohnA 
>>> *To:* excel-macros@googlegroups.com
>>> *Sent:* Tue, February 5, 2013 1:56:21 PM
>>> *Subject:* $$Excel-Macros$$ Re: Conditional Format Entire Row if Any
>>> Column has a specific value
>>>
>>> I wasn't clear enough so I created an example of my question.  Thank you
>>> all for quick responses.
>>>
>>> My target value can be in none or one or many of the 180 columns, not
>>> interdependent on each other, and not in the same column on different
>>> rows.  Please see the attached example for a more clear view of my
>>> challange.
>>>
>>> Again thank you for your help.
>>>
>>> John
>>>
>>> On Tuesday, February 5, 2013 10:05:08 AM UTC-5, JohnA wrote:
>>>
>>>> I have a spreadsheet with 180 columns and 8,000 rows.  I would like to
>>>> highlight a row if a specific value is anywhere in the spreadsheet. (for
>>>> example in cell K23 = 234 and H112 = 234, then both row 23 and 112 are
>>>> highlighted).
>>>>
>>>> Please help with the formula to accomplish this.  Thank you.
>>>>
>>>> John
>>>>
>>> --
>>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be?
>>> It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>>> https://www.facebook.com/discussexcel
>>>
>>> FORUM RULES
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>> 2) Don't post a question in the thread of another member.
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>> 4) Acknowledge the responses you receive, good or bad.
>>> 5) Jobs posting is not allowed.
>>> 6) Sharing copyrighted material and their links is not allowed.
>>>
>>> NOTE : Don't ever p

Re: $$Excel-Macros$$ Re: Conditional Format Entire Row if Any Column has a specific value

[John A. Smith]

Paul,

#1) By using conditional formatting, highlight any row the EXACT target
value is in regardless of the column it's in
#2) Has to be an EXACT match
#3) Helper columns are okay
#4) Rows quantity varies but can be between several hundred to
approximately 8-9,000
#5) Can be either formula or VBA

Each row is an individual event and I need to quickly see only the rows
where somewhere in the 180 columns is a specific target value (I wanted to
make cell A1 where you would enter the target value to trigger the
highlighting).

Thank you for your help.

John


On Tue, Feb 5, 2013 at 3:44 PM, Paul Schreiner wrote:

> questions:
> #1) What do you mean by "highlight the row"?
> #2) will this be an "exact" match? or partial contents of the cells?
> #3) can we add a "helper" column and filter?
> #4) How many rows are we talking about?
> #5) does it have to be a FORMULA? can you use VBA?
>
> If we add a new column (either at the beginning or end)
> then we can use a HLookup function to search for a matching value,
> combine that with aniferr() function, and we can then "flag" the row
> that contains the value.
> using Filters, then you can display only that row.
>
> This won't "highlight" the row
> and it won't work well with a large number of rows.
>
> I could easily create a VBA macro that could
> clear the highlighting
> search for the specified value
> highlight the row (in any way you wish)
>
> but that is not a formula.
>
>
>
> *Paul*
>
> -
> *“Do all the good you can,
> By all the means you can,
> In all the ways you can,
> In all the places you can,
> At all the times you can,
> To all the people you can,
> As long as ever you can.” - John Wesley
> *-
>
>
>  --
> *From:* JohnA 
> *To:* excel-macros@googlegroups.com
> *Sent:* Tue, February 5, 2013 1:56:21 PM
> *Subject:* $$Excel-Macros$$ Re: Conditional Format Entire Row if Any
> Column has a specific value
>
> I wasn't clear enough so I created an example of my question.  Thank you
> all for quick responses.
>
> My target value can be in none or one or many of the 180 columns, not
> interdependent on each other, and not in the same column on different
> rows.  Please see the attached example for a more clear view of my
> challange.
>
> Again thank you for your help.
>
> John
>
> On Tuesday, February 5, 2013 10:05:08 AM UTC-5, JohnA wrote:
>
>> I have a spreadsheet with 180 columns and 8,000 rows.  I would like to
>> highlight a row if a specific value is anywhere in the spreadsheet. (for
>> example in cell K23 = 234 and H112 = 234, then both row 23 and 112 are
>> highlighted).
>>
>> Please help with the formula to accomplish this.  Thank you.
>>
>> John
>>
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group

$$Excel-Macros$$ Conditional Format Entire Row if Any Column has a specific value

[John A. Smith]

I have a spreadsheet with 180 columns and 8,000 rows.  I would like to
highlight a row if a specific value is anywhere in the spreadsheet. (for
example in cell K23 = 234 and H112 = 234, then both row 23 and 112 are
highlighted).

Please help with the formula to accomplish this.  Thank you.

John

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: $$Excel-Macros$$ Macro - Copy Range and Open Outlook Draft and Paste

[John A. Smith]

That was it.  I will go over this with a fine tuned comb and learn all I
can.  Thank you most graciously for your much appreciated help.

John

On Mon, Jul 23, 2012 at 12:52 PM, ashish koul  wrote:

> in tools -> reference -> choose Microsoft outlook..
>
>
> On Mon, Jul 23, 2012 at 9:57 PM, John A. Smith wrote:
>
>> Ashish,
>>
>> Thank you for the quick reply.  I get an error when I copy and paste your
>> macro in my spreadsheet.  I attached a screen print of the error. A user
>> defined parameter is missing???  When I first downloaded your sample it
>> opened my Outlook and pasted the table.  Now it won't work, it opens the
>> Outlook but it is blank (See Macro Error II).
>>
>> It opened a fresh blank email, and I was wondering if I could name the
>> draft and have it open one of those (if not, that's fine, getting it to
>> open an Outlook New E-mail and paste the highlighted range would be great...
>>
>> Thank you Ashish.
>>
>> John
>>
>>  On Mon, Jul 23, 2012 at 12:01 PM, ashish koul wrote:
>>
>>> check the attachment see if it helps
>>>
>>>
>>>
>>>
>>>
>>> On Mon, Jul 23, 2012 at 9:29 PM, John A. Smith 
>>> wrote:
>>>
>>>> Esteemed Excel Teachers - How would I go about creating a macro that
>>>> would open an Outlook draft and paste the highlighted range from my excel
>>>> spreadsheet into the body of the Outlook email draft?
>>>>
>>>> Thank you for your continueing help...
>>>>
>>>> John
>>>>
>>>> --
>>>> --
>>>> FORUM RULES (986+ members already BANNED for violation)
>>>>
>>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>>> will not get quick attention or may not be answered.
>>>>
>>>> 2) Don't post a question in the thread of another member.
>>>>
>>>> 3) Don't post questions regarding breaking or bypassing any security
>>>> measure.
>>>>
>>>> 4) Acknowledge the responses you receive, good or bad.
>>>>
>>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>>> signatures are prohibited.
>>>>
>>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>>> Forum owners and members are not responsible for any loss.
>>>>
>>>>
>>>> --
>>>> To post to this group, send email to excel-macros@googlegroups.com
>>>>
>>>> To unsubscribe, send a blank email to
>>>> excel-macros+unsubscr...@googlegroups.com
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>> --
>>> *Regards*
>>> * *
>>> *Ashish Koul*
>>>
>>>
>>>  *Visit*
>>> *http://www.excelvbamacros.com/*
>>> *http://www.accessvbamacros.com/*
>>>
>>> P Before printing, think about the environment.
>>>
>>>
>>>
>>>  --
>>> --
>>> FORUM RULES (986+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>>
>>> 4) Acknowledge the responses you receive, good or bad.
>>>
>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.
>>>
>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.
>>>
>>>
>>> --
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> To unsubscribe, send a blank email to
>>> excel-macros+unsubscr...@googlegroups.com
>>>
>>>
>>>
>>
>> --
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>

Re: $$Excel-Macros$$ Macro To Transpose Selected Row Data To Column Format

[John A. Smith]

Absolutely fantastic!  Thank you for your generous help.

John

On Tue, May 8, 2012 at 11:07 AM, Krishna Kumar wrote:

> Replace
>
> ReDim k(1 To UBound(ka, 1) * (UBound(Hdr) + 1) + UBound(Hdr) + 1, 1 To 1)
>
> with
>
> ReDim k(1 To 1, 1 To 1)
>
>
> Kris
> ExcelFox 
>
>  --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Macro To Transpose Selected Row Data To Column Format

[John A. Smith]

Kris,

You are amazing and gracious for your help and very quick repsonse.  Thank
you millions!

John




On Tue, May 8, 2012 at 10:09 AM, Krishna Kumar wrote:

> Hi
>
> Try this
>
>  Sub kTest()
>
> Dim ka, k(), i As Long, c As Long, n As Long, Hdr, x
> Dim wks As Worksheet, Flds
>
> Hdr = Array("NAME", "Specialty_name", "Office_name", "Address_1",
> "Address_2", _
> "Address", "Phone_number_1", "Fax_number")
>
> ka = Sheets("Sheet1").Range("a1").CurrentRegion.Value2
> Flds = Application.Index(ka, 1, 0)
> ReDim k(1 To UBound(ka, 1) * (UBound(Hdr) + 1) + UBound(Hdr) + 1, 1 To
> 1)
>
> For i = 2 To UBound(ka, 1)
> For c = 0 To UBound(Hdr)
> x = Application.Match(Hdr(c), Flds, 0)
> If Not IsError(x) Then
> If Len(ka(i, x)) Then
> n = n + 1
> k(n, 1) = ka(i, x)
> End If
> End If
> Next
> n = n + 1
> Next
>
> On Error Resume Next
> Set wks = Worksheets("Result")
> On Error GoTo 0
> If wks Is Nothing Then
> Set wks = Worksheets.Add
> wks.Name = "Result"
> End If
>
> wks.Range("a1").Resize(n) = k
>
> End Sub
>
> Kris
> ExcelFox 
>
>  --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Consolidate Two Weekly Reports into a Summary Report

[John A. Smith]

Don,

You are absolutely correct, I love it.  I got into it and analyzed each and
every line.  It is a perfect tool to learn from.

Thank you for your patience and teachings.

John

On Mon, Mar 19, 2012 at 3:26 PM, dguillett1  wrote:

>   You really should like this one
> No dupes and double click to sort by column
>
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@gmail.com
>
>  *From:* John A. Smith 
> *Sent:* Monday, March 19, 2012 1:03 PM
>   *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ Consolidate Two Weekly Reports into a
> Summary Report
>
>   Don,
>
> That was what I was hoping for, but when I click the button, it dulpicates
> some of the entries.  Is it me or the macro?
>
> Thank you.
>
> John
>
> On Mon, Mar 19, 2012 at 1:30 PM, dguillett1  wrote:
>
>>   See attached which works for as many sheets as you may have after the
>> summary sheet. It uses a macro to make a unique list and then get the data
>> for the unique list for each sheet.
>>
>>
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguille...@gmail.com
>>
>>  *From:* John A. Smith 
>> *Sent:* Monday, March 19, 2012 11:09 AM
>> *To:* excel-macros@googlegroups.com
>> *Subject:* Re: $$Excel-Macros$$ Consolidate Two Weekly Reports into a
>> Summary Report
>>
>>   Don,
>>
>> Thank you for such a quick response.  I like the formula and will analyze
>> it until I understand every part of it.  The problem with this approach is
>> the projects are actually names and therefore a clean list would have to be
>> manually sorted to eliminate duplicates and then pasted into Column "J" to
>> begin the lookups.
>>
>> Thank you for your continued help.
>>
>> John
>>
>> On Mon, Mar 19, 2012 at 11:55 AM, dguillett1 wrote:
>>
>>>   One possible formula to use vlookup if there is a match. See att
>>>
>>> =IF(ISNA((MATCH($J4,'Week One'!$A:$A,0))),0,VLOOKUP($J4,'Week
>>> One'!$A$2:$C$118,2,0))+IF(ISNA((MATCH($J4,'Week
>>> Two'!$A:$A,0))),0,VLOOKUP($J4,'Week Two'!$A$2:$C$118,2,0))
>>>
>>> Don Guillett
>>> Microsoft MVP Excel
>>> SalesAid Software
>>> dguille...@gmail.com
>>>
>>>  *From:* John A. Smith 
>>> *Sent:* Monday, March 19, 2012 10:00 AM
>>> *To:* excel-macros@googlegroups.com
>>> *Subject:* $$Excel-Macros$$ Consolidate Two Weekly Reports into a
>>> Summary Report
>>>
>>>
>>> I need help with a formula or macro to consolidate two weekly reports
>>> into a summary report.  The individual weekly reports have the same column
>>> structure and some rows share a common project name.  But every project
>>> name isn't necessarily on both reports, so matching them up is time
>>> consuming.
>>>
>>> Attached please find an example spreadsheet with two weekly tabs and the
>>> desired report.
>>>
>>> Thank you for your ongoing help.
>>>
>>> John
>>> --
>>> FORUM RULES (986+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>>
>>> 4) Acknowledge the responses you receive, good or bad.
>>>
>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.
>>>
>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.
>>>
>>>
>>> --
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>  --
>>> FORUM RULES (986+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don'

Re: $$Excel-Macros$$ Consolidate Two Weekly Reports into a Summary Report

[John A. Smith]

Don,

That was what I was hoping for, but when I click the button, it dulpicates
some of the entries.  Is it me or the macro?

Thank you.

John

On Mon, Mar 19, 2012 at 1:30 PM, dguillett1  wrote:

>   See attached which works for as many sheets as you may have after the
> summary sheet. It uses a macro to make a unique list and then get the data
> for the unique list for each sheet.
>
>
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@gmail.com
>
>  *From:* John A. Smith 
> *Sent:* Monday, March 19, 2012 11:09 AM
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ Consolidate Two Weekly Reports into a
> Summary Report
>
>   Don,
>
> Thank you for such a quick response.  I like the formula and will analyze
> it until I understand every part of it.  The problem with this approach is
> the projects are actually names and therefore a clean list would have to be
> manually sorted to eliminate duplicates and then pasted into Column "J" to
> begin the lookups.
>
> Thank you for your continued help.
>
> John
>
> On Mon, Mar 19, 2012 at 11:55 AM, dguillett1  wrote:
>
>>   One possible formula to use vlookup if there is a match. See att
>>
>> =IF(ISNA((MATCH($J4,'Week One'!$A:$A,0))),0,VLOOKUP($J4,'Week
>> One'!$A$2:$C$118,2,0))+IF(ISNA((MATCH($J4,'Week
>> Two'!$A:$A,0))),0,VLOOKUP($J4,'Week Two'!$A$2:$C$118,2,0))
>>
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguille...@gmail.com
>>
>>  *From:* John A. Smith 
>> *Sent:* Monday, March 19, 2012 10:00 AM
>> *To:* excel-macros@googlegroups.com
>> *Subject:* $$Excel-Macros$$ Consolidate Two Weekly Reports into a
>> Summary Report
>>
>>
>> I need help with a formula or macro to consolidate two weekly reports
>> into a summary report.  The individual weekly reports have the same column
>> structure and some rows share a common project name.  But every project
>> name isn't necessarily on both reports, so matching them up is time
>> consuming.
>>
>> Attached please find an example spreadsheet with two weekly tabs and the
>> desired report.
>>
>> Thank you for your ongoing help.
>>
>> John
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>  --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regard

Re: $$Excel-Macros$$ Consolidate Two Weekly Reports into a Summary Report

[John A. Smith]

Don,

Thank you for such a quick response.  I like the formula and will analyze
it until I understand every part of it.  The problem with this approach is
the projects are actually names and therefore a clean list would have to be
manually sorted to eliminate duplicates and then pasted into Column "J" to
begin the lookups.

Thank you for your continued help.

John

On Mon, Mar 19, 2012 at 11:55 AM, dguillett1  wrote:

>   One possible formula to use vlookup if there is a match. See att
>
> =IF(ISNA((MATCH($J4,'Week One'!$A:$A,0))),0,VLOOKUP($J4,'Week
> One'!$A$2:$C$118,2,0))+IF(ISNA((MATCH($J4,'Week
> Two'!$A:$A,0))),0,VLOOKUP($J4,'Week Two'!$A$2:$C$118,2,0))
>
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@gmail.com
>
>  *From:* John A. Smith 
> *Sent:* Monday, March 19, 2012 10:00 AM
> *To:* excel-macros@googlegroups.com
> *Subject:* $$Excel-Macros$$ Consolidate Two Weekly Reports into a Summary
> Report
>
>
> I need help with a formula or macro to consolidate two weekly reports into
> a summary report.  The individual weekly reports have the same column
> structure and some rows share a common project name.  But every project
> name isn't necessarily on both reports, so matching them up is time
> consuming.
>
> Attached please find an example spreadsheet with two weekly tabs and the
> desired report.
>
> Thank you for your ongoing help.
>
> John
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Chart Template Dynamic for Different Users

[John A. Smith]

Excellent!  I was looking into the belly of the beast to see if I could do
that.  Another lesson learned.

Thank you Don.

John

On Fri, Feb 24, 2012 at 2:53 PM, dguillett1  wrote:

>   Thanks for the kind words. I made a bit of improvement to not show a
> quarter if no values.
>
>
>
> Don Guillett
> SalesAid Software
> dguille...@gmail.com
>
>  *From:* John A. Smith 
> *Sent:* Friday, February 24, 2012 10:02 AM
>   *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ Chart Template Dynamic for Different Users
>
>   Don,
>
> I spent hours going through that spreadsheet and learned a lot.  Thank you
> for your help and teaching.
>
> John
>
> On Thu, Feb 23, 2012 at 12:16 PM, dguillett1  wrote:
>
>>   That’s because the defined name was not referenced in the chart
>> sources.
>> series=smith.xls!definedname
>>
>> Don Guillett
>> SalesAid Software
>> dguille...@gmail.com
>>
>>  *From:* John A. Smith 
>> *Sent:* Thursday, February 23, 2012 9:25 AM
>> *To:* excel-macros@googlegroups.com
>> *Subject:* Re: $$Excel-Macros$$ Chart Template Dynamic for Different
>> Users
>>
>>   Xlstime,
>>
>> I saw that but when I added another product it didn't show up on the
>> chart.
>>
>> John
>>
>> On Thu, Feb 23, 2012 at 10:20 AM, xlstime  wrote:
>>
>>> in Name Manager,
>>>
>>> press alt+i+n+d
>>>
>>>
>>> On Thu, Feb 23, 2012 at 8:47 PM, John A. Smith 
>>> wrote:
>>>
>>>> Xlstime,
>>>>
>>>> Where do I put that formula?
>>>>
>>>> John
>>>>
>>>>  On Thu, Feb 23, 2012 at 10:10 AM, xlstime  wrote:
>>>>
>>>>> Dear John,
>>>>>
>>>>> Please find the attachment
>>>>>
>>>>>
>>>>> use formula =OFFSET($A$2,0,0,COUNTA($A$2:$A$10),COUNTA($A$2:$F$2))
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> On Thu, Feb 23, 2012 at 8:13 PM, John A. Smith >>>> > wrote:
>>>>>
>>>>>> I would like to create a template whereby users could add any
>>>>>> varialbe of items to track and the chart work for the 5 item user as well
>>>>>> as the 9 item user.  Please see the attached example.
>>>>>>
>>>>>> Thank you again for your dedicated help.
>>>>>>
>>>>>> John
>>>>>> --
>>>>>> FORUM RULES (986+ members already BANNED for violation)
>>>>>>
>>>>>> 1) Use concise, accurate thread titles. Poor thread titles, like
>>>>>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need
>>>>>> Advice will not get quick attention or may not be answered.
>>>>>>
>>>>>> 2) Don't post a question in the thread of another member.
>>>>>>
>>>>>> 3) Don't post questions regarding breaking or bypassing any security
>>>>>> measure.
>>>>>>
>>>>>> 4) Acknowledge the responses you receive, good or bad.
>>>>>>
>>>>>> 5) Cross-promotion of, or links to, forums competitive to this forum
>>>>>> in signatures are prohibited.
>>>>>>
>>>>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>>>>> Forum owners and members are not responsible for any loss.
>>>>>>
>>>>>>
>>>>>> --
>>>>>> To post to this group, send email to excel-macros@googlegroups.com
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> .
>>>>>
>>>>>
>>>>> --
>>>>> FORUM RULES (986+ members already BANNED for violation)
>>>>>
>>>>> 1) Use concise, accurate thread titles. Poor thread titles, like
>>>>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need
>>>>> Advice will not get quick attention or may not be answered.
>>>>>
>>>>> 2) Don't post a question in the thread of another member.
>>>>&g

Re: $$Excel-Macros$$ Chart Template Dynamic for Different Users

[John A. Smith]

Don,

I spent hours going through that spreadsheet and learned a lot.  Thank you
for your help and teaching.

John

On Thu, Feb 23, 2012 at 12:16 PM, dguillett1  wrote:

>   That’s because the defined name was not referenced in the chart
> sources.
> series=smith.xls!definedname
>
> Don Guillett
> SalesAid Software
> dguille...@gmail.com
>
>  *From:* John A. Smith 
> *Sent:* Thursday, February 23, 2012 9:25 AM
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ Chart Template Dynamic for Different Users
>
>   Xlstime,
>
> I saw that but when I added another product it didn't show up on the
> chart.
>
> John
>
> On Thu, Feb 23, 2012 at 10:20 AM, xlstime  wrote:
>
>> in Name Manager,
>>
>> press alt+i+n+d
>>
>>
>> On Thu, Feb 23, 2012 at 8:47 PM, John A. Smith wrote:
>>
>>> Xlstime,
>>>
>>> Where do I put that formula?
>>>
>>> John
>>>
>>>  On Thu, Feb 23, 2012 at 10:10 AM, xlstime  wrote:
>>>
>>>> Dear John,
>>>>
>>>> Please find the attachment
>>>>
>>>>
>>>> use formula =OFFSET($A$2,0,0,COUNTA($A$2:$A$10),COUNTA($A$2:$F$2))
>>>>
>>>>
>>>>
>>>>
>>>> On Thu, Feb 23, 2012 at 8:13 PM, John A. Smith 
>>>> wrote:
>>>>
>>>>> I would like to create a template whereby users could add any varialbe
>>>>> of items to track and the chart work for the 5 item user as well as the 9
>>>>> item user.  Please see the attached example.
>>>>>
>>>>> Thank you again for your dedicated help.
>>>>>
>>>>> John
>>>>> --
>>>>> FORUM RULES (986+ members already BANNED for violation)
>>>>>
>>>>> 1) Use concise, accurate thread titles. Poor thread titles, like
>>>>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need
>>>>> Advice will not get quick attention or may not be answered.
>>>>>
>>>>> 2) Don't post a question in the thread of another member.
>>>>>
>>>>> 3) Don't post questions regarding breaking or bypassing any security
>>>>> measure.
>>>>>
>>>>> 4) Acknowledge the responses you receive, good or bad.
>>>>>
>>>>> 5) Cross-promotion of, or links to, forums competitive to this forum
>>>>> in signatures are prohibited.
>>>>>
>>>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>>>> Forum owners and members are not responsible for any loss.
>>>>>
>>>>>
>>>>> --
>>>>> To post to this group, send email to excel-macros@googlegroups.com
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> .
>>>>
>>>>
>>>> --
>>>> FORUM RULES (986+ members already BANNED for violation)
>>>>
>>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>>> will not get quick attention or may not be answered.
>>>>
>>>> 2) Don't post a question in the thread of another member.
>>>>
>>>> 3) Don't post questions regarding breaking or bypassing any security
>>>> measure.
>>>>
>>>> 4) Acknowledge the responses you receive, good or bad.
>>>>
>>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>>> signatures are prohibited.
>>>>
>>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>>> Forum owners and members are not responsible for any loss.
>>>>
>>>>
>>>> --
>>>> To post to this group, send email to excel-macros@googlegroups.com
>>>>
>>>
>>> --
>>> FORUM RULES (986+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answe

Re: $$Excel-Macros$$ Chart Template Dynamic for Different Users

[John A. Smith]

Xlstime,

I saw that but when I added another product it didn't show up on the
chart.

John

On Thu, Feb 23, 2012 at 10:20 AM, xlstime  wrote:

> in Name Manager,
>
> press alt+i+n+d
>
>
> On Thu, Feb 23, 2012 at 8:47 PM, John A. Smith wrote:
>
>> Xlstime,
>>
>> Where do I put that formula?
>>
>> John
>>
>>  On Thu, Feb 23, 2012 at 10:10 AM, xlstime  wrote:
>>
>>> Dear John,
>>>
>>> Please find the attachment
>>>
>>>
>>> use formula =OFFSET($A$2,0,0,COUNTA($A$2:$A$10),COUNTA($A$2:$F$2))
>>>
>>>
>>>
>>>
>>> On Thu, Feb 23, 2012 at 8:13 PM, John A. Smith 
>>> wrote:
>>>
>>>> I would like to create a template whereby users could add any varialbe
>>>> of items to track and the chart work for the 5 item user as well as the 9
>>>> item user.  Please see the attached example.
>>>>
>>>> Thank you again for your dedicated help.
>>>>
>>>> John
>>>>
>>>> --
>>>> FORUM RULES (986+ members already BANNED for violation)
>>>>
>>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>>> will not get quick attention or may not be answered.
>>>>
>>>> 2) Don't post a question in the thread of another member.
>>>>
>>>> 3) Don't post questions regarding breaking or bypassing any security
>>>> measure.
>>>>
>>>> 4) Acknowledge the responses you receive, good or bad.
>>>>
>>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>>> signatures are prohibited.
>>>>
>>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>>> Forum owners and members are not responsible for any loss.
>>>>
>>>>
>>>> --
>>>> To post to this group, send email to excel-macros@googlegroups.com
>>>>
>>>
>>>
>>>
>>> --
>>> .
>>>
>>>
>>> --
>>> FORUM RULES (986+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>>
>>> 4) Acknowledge the responses you receive, good or bad.
>>>
>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.
>>>
>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.
>>>
>>>
>>> --
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
>
>
> --
> .
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> He

Re: $$Excel-Macros$$ Chart Template Dynamic for Different Users

[John A. Smith]

Xlstime,

Where do I put that formula?

John

On Thu, Feb 23, 2012 at 10:10 AM, xlstime  wrote:

> Dear John,
>
> Please find the attachment
>
>
> use formula =OFFSET($A$2,0,0,COUNTA($A$2:$A$10),COUNTA($A$2:$F$2))
>
>
>
>
> On Thu, Feb 23, 2012 at 8:13 PM, John A. Smith wrote:
>
>> I would like to create a template whereby users could add any varialbe of
>> items to track and the chart work for the 5 item user as well as the 9 item
>> user.  Please see the attached example.
>>
>> Thank you again for your dedicated help.
>>
>> John
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
>
>
> --
> .
>
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Need A Macro To Move Rows

[John A. Smith]

Wow, thanks Sam.  When I was trying this myself, I started down this
approach and couldn't make it work.  Thanks for the lesson.

John



On Mon, Feb 6, 2012 at 3:06 PM, Sam Mathai Chacko wrote:

> If your rows go in to the thousands, using autofilter will add a bit more
> (~40%) efficiency
>
> Sub Consolidator()
>
> Application.ScreenUpdating = 0
> With Range("A2", Cells(Rows.Count, "J").End(xlUp))
> .AutoFilter 7, "Pickup"
> .Offset(1, 10).Resize(.Rows.Count - 1,
> 10).SpecialCells(12).FormulaR1C1 = "=R[1]C[-10]"
> .Rows(1).Offset(, 10).FormulaR1C1 = "=RC[-10]"
> .AutoFilter
> With .Offset(, 10).Resize(, 10)
> .Value = .Value
> .Offset(, -10).Copy
> .PasteSpecial xlPasteFormats
> .Columns(1).SpecialCells(4).EntireRow.Delete
> End With
> End With
> Application.ScreenUpdating = 1
>
> End Sub
>
> Regards,
>
> Sam Mathai Chacko
>
>
> On Mon, Feb 6, 2012 at 9:21 PM, dguillett1  wrote:
>
>>   Glad to help
>>
>> Don Guillett
>> SalesAid Software
>> dguille...@gmail.com
>>
>>  *From:* John A. Smith 
>> *Sent:* Monday, February 06, 2012 9:11 AM
>> *To:* excel-macros@googlegroups.com
>> *Subject:* Re: $$Excel-Macros$$ Need A Macro To Move Rows
>>
>>  Thanks again for your assistance Don, your help is very much
>> appreciated.
>>
>> John
>>
>> On Mon, Feb 6, 2012 at 10:03 AM, dguillett1  wrote:
>>
>>>   Place in a module and save the file as macro enabled. xls or .xlsM
>>> ‘==
>>> Option Explicit
>>> Sub lineemupSAS()
>>> Dim i As Long
>>> Application.ScreenUpdating = False
>>> For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -2
>>>   Cells(i, 1).Resize(, 10).Copy Cells(i - 1, "K")
>>>   Rows(i).Delete
>>> Next i
>>> Columns.AutoFit
>>> Application.ScreenUpdating = True
>>> End Sub
>>> ‘=
>>> Don Guillett
>>> SalesAid Software
>>> dguille...@gmail.com
>>>
>>>  *From:* John A. Smith 
>>> *Sent:* Monday, February 06, 2012 8:21 AM
>>> *To:* excel-macros@googlegroups.com
>>> *Subject:* $$Excel-Macros$$ Need A Macro To Move Rows
>>>
>>>   Thank you for your ongoing Excel help.  I need a macro to correct a
>>> download issue.  The download places one event on two lines in 10 columns.
>>> I need the second line of every event moved up next the first part of the
>>> event, thereby doubling the number of columns but cutting the number of
>>> rows in half.
>>>
>>> I have detailed an example in the attached file.  Thank you again for
>>> all your help and teaching.
>>>
>>> John
>>> --
>>> FORUM RULES (986+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>>
>>> 4) Acknowledge the responses you receive, good or bad.
>>>
>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.
>>>
>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.
>>>
>>>
>>> --
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>  --
>>> FORUM RULES (986+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>>
>>> 4) Acknowledge the responses you receive, good or bad.
>>>
>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> sign

Re: $$Excel-Macros$$ Need A Macro To Move Rows

[John A. Smith]

Thanks again for your assistance Don, your help is very much appreciated.

John

On Mon, Feb 6, 2012 at 10:03 AM, dguillett1  wrote:

>   Place in a module and save the file as macro enabled. xls or .xlsM
> ‘==
> Option Explicit
> Sub lineemupSAS()
> Dim i As Long
> Application.ScreenUpdating = False
> For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -2
>   Cells(i, 1).Resize(, 10).Copy Cells(i - 1, "K")
>   Rows(i).Delete
> Next i
> Columns.AutoFit
> Application.ScreenUpdating = True
> End Sub
> ‘=
> Don Guillett
> SalesAid Software
> dguille...@gmail.com
>
>  *From:* John A. Smith 
> *Sent:* Monday, February 06, 2012 8:21 AM
> *To:* excel-macros@googlegroups.com
> *Subject:* $$Excel-Macros$$ Need A Macro To Move Rows
>
>   Thank you for your ongoing Excel help.  I need a macro to correct a
> download issue.  The download places one event on two lines in 10 columns.
> I need the second line of every event moved up next the first part of the
> event, thereby doubling the number of columns but cutting the number of
> rows in half.
>
> I have detailed an example in the attached file.  Thank you again for all
> your help and teaching.
>
> John
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Need Formula for Lookup Based on a Matrix of Data

[John A. Smith]

Noorain, it is no wonder you are honored as most helpful so often.
Congratulations and thank you for your help.

John

On Thu, Jan 26, 2012 at 11:22 AM, NOORAIN ANSARI
wrote:

> Dear John,
>
> You can also use in F3...
>
>
> =INDEX(Data!$C$2:$C$37,MATCH(1,(Data!$A$2:$A$37=E3)*(Data!$B$2:$B$37="Total"),0))
> with ctrl+Shift+enter
>
> See attached sheet.
> --
> Thanks & regards,
> Noorain Ansari
>  *http://excelmacroworld.blogspot.com/*<http://excelmacroworld.blogspot.com/>
> *http://noorain-ansari.blogspot.com/*<http://noorain-ansari.blogspot.com/>
>  On Thu, Jan 26, 2012 at 8:48 PM, John A. Smith wrote:
>
>> Thank you for your valuable help.
>>
>> I need a formula that will lookup a value based on a column heading, a
>> date column/row and a row qualifier (the word "Total"). Please see the
>> attached spreadsheet.
>>
>> Thank you.
>>
>> John
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
>
>
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Need Formula for Lookup Based on a Matrix of Data

[John A. Smith]

Don, thanks again for your help and guidance.

John

On Thu, Jan 26, 2012 at 2:46 PM, dguillett1  wrote:

>   The dates are sorted so the match is looking for the LAST matching
> date. Look in the help index for MATCH.
>
>  Don Guillett
> SalesAid Software
> dguille...@gmail.com
>
>  *From:* Sourabh Salgotra 
> *Sent:* Thursday, January 26, 2012 11:04 AM
>   *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ Need Formula for Lookup Based on a Matrix
> of Data
>
>   plz sir explain this formula i have confusion in this. how it can take
> total values from data sheet.
>
>
> On Thu, Jan 26, 2012 at 10:19 PM, dguillett1  wrote:
>
>>   Simpler with no CSE
>>
>> '=INDEX(Data!$A$1:$L$37,MATCH($E3,Data!$A:$A),MATCH(F$2,Data!$1:$1,0))
>>
>> Don Guillett
>> SalesAid Software
>> dguille...@gmail.com
>>
>>  *From:* NOORAIN ANSARI 
>> *Sent:* Thursday, January 26, 2012 10:22 AM
>> *To:* excel-macros@googlegroups.com
>> *Subject:* Re: $$Excel-Macros$$ Need Formula for Lookup Based on a
>> Matrix of Data
>>
>>   Dear John,
>>
>> You can also use in F3...
>>
>>
>> =INDEX(Data!$C$2:$C$37,MATCH(1,(Data!$A$2:$A$37=E3)*(Data!$B$2:$B$37="Total"),0))
>> with ctrl+Shift+enter
>>
>> See attached sheet.
>> --
>> Thanks & regards,
>> Noorain Ansari
>>  *http://excelmacroworld.blogspot.com/*<http://excelmacroworld.blogspot.com/>
>> *http://noorain-ansari.blogspot.com/*<http://noorain-ansari.blogspot.com/>
>> On Thu, Jan 26, 2012 at 8:48 PM, John A. Smith wrote:
>>
>>> Thank you for your valuable help.
>>>
>>> I need a formula that will lookup a value based on a column heading, a
>>> date column/row and a row qualifier (the word "Total"). Please see the
>>> attached spreadsheet.
>>>
>>> Thank you.
>>>
>>> John
>>> --
>>> FORUM RULES (986+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>>
>>> 4) Acknowledge the responses you receive, good or bad.
>>>
>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.
>>>
>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.
>>>
>>>
>>> --
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>
>>
>>
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>  --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or li

Re: $$Excel-Macros$$ Need Formula for Lookup Based on a Matrix of Data

[John A. Smith]

Thank you very much Rajan.

John

On Mon, Jan 30, 2012 at 12:44 PM, Rajan_Verma wrote:

>  Use this
>
>
> =OFFSET(INDEX(Data!A:A,MATCH(Dashboard!E3,Data!A:A,0),1),2,MATCH(Dashboard!F2,Data!$A$1:$L$1,0)-1)
> 
>
> ** **
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *John A. Smith
> *Sent:* Jan/Thu/2012 08:49
> *To:* excel-macros@googlegroups.com
> *Subject:* $$Excel-Macros$$ Need Formula for Lookup Based on a Matrix of
> Data
>
> ** **
>
> Thank you for your valuable help.
>
>  
>
> I need a formula that will lookup a value based on a column heading, a
> date column/row and a row qualifier (the word "Total"). Please see the
> attached spreadsheet.
>
>  
>
> Thank you.
>
>  
>
> John
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Need Formula for Lookup Based on a Matrix of Data

[John A. Smith]

Don,

How is it picking up only the "Total" number? What part of the formula does
that?  I understand all but that...

Thank you.

John

On Thu, Jan 26, 2012 at 11:49 AM, dguillett1  wrote:

>   Simpler with no CSE
>
> '=INDEX(Data!$A$1:$L$37,MATCH($E3,Data!$A:$A),MATCH(F$2,Data!$1:$1,0))
>
> Don Guillett
> SalesAid Software
> dguille...@gmail.com
>
>  *From:* NOORAIN ANSARI 
> *Sent:* Thursday, January 26, 2012 10:22 AM
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ Need Formula for Lookup Based on a Matrix
> of Data
>
>   Dear John,
>
> You can also use in F3...
>
>
> =INDEX(Data!$C$2:$C$37,MATCH(1,(Data!$A$2:$A$37=E3)*(Data!$B$2:$B$37="Total"),0))
> with ctrl+Shift+enter
>
> See attached sheet.
> --
> Thanks & regards,
> Noorain Ansari
>  *http://excelmacroworld.blogspot.com/*<http://excelmacroworld.blogspot.com/>
> *http://noorain-ansari.blogspot.com/*<http://noorain-ansari.blogspot.com/>
> On Thu, Jan 26, 2012 at 8:48 PM, John A. Smith wrote:
>
>> Thank you for your valuable help.
>>
>> I need a formula that will lookup a value based on a column heading, a
>> date column/row and a row qualifier (the word "Total"). Please see the
>> attached spreadsheet.
>>
>> Thank you.
>>
>> John
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
>
>
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Returning a Value Based on a String Containing Specific Characters

[John A. Smith]

Thank you Don, I appreciate the help.

John

On Tue, Nov 29, 2011 at 5:04 PM, dguillett1  wrote:

>
>
> Don Guillett
> SalesAid Software
> dguille...@gmail.com
>
>  *From:* John A. Smith 
> *Sent:* Tuesday, November 29, 2011 3:08 PM
> *To:* excel-macros@googlegroups.com
> *Subject:* $$Excel-Macros$$ Returning a Value Based on a String
> Containing Specific Characters
>
>
> Excel Teachers,
>
> I need to look at a cell and if it contains specific characters (for
> example the three characters “410”), return a specific value and if not
> return a blank.  The string can be quite long or short and the “410” may
> or may not be in it and its location when it is in it could be at the
> beginning, in the middle or at the end.
>
> Please review the attached sample.  Thank you for your help and
> consideration.
>
> John
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

$$Excel-Macros$$ Returning a Value Based on a String Containing Specific Characters

[John A. Smith]

Excel Teachers,

I need to look at a cell and if it contains specific characters (for
example the three characters “410”), return a specific value and if not
return a blank.  The string can be quite long or short and the “410” may or
may not be in it and its location when it is in it could be at the
beginning, in the middle or at the end.

Please review the attached sample.  Thank you for your help and
consideration.

John

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


Return A Value Based on Specific Text in the String.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ Macro Needed to Uncheck All Checkboxes

[John A. Smith]

Thank you Sam, that is a clean approach.  Appreciate the help.

John

On Thu, Nov 17, 2011 at 11:19 AM, Sam Mathai Chacko wrote:

> Another way would be to use a single line without a for loop
>
> Sheets(1).Range("M5:Q17").Value = False
>
> Regards,
> Sam Mathai Chacko
>
>
> On Thu, Nov 17, 2011 at 8:22 PM, John A. Smith wrote:
>
>> Perfect!  Thank you Ashish. Thank you very much.
>>
>> John
>>
>>  On Thu, Nov 17, 2011 at 9:45 AM, ashish koul wrote:
>>
>>> Sub test()
>>> Dim rngap As Range
>>>
>>> For Each rngap In Sheets(1).Range("m5:q17")
>>>
>>> rngap = False
>>> Next
>>> End Sub
>>>
>>>
>>> On Thu, Nov 17, 2011 at 8:11 PM, John A. Smith 
>>> wrote:
>>>
>>>> Your help is appreciated.  I need a macro to uncheck all the check
>>>> boxes on the attached spreadsheet.  User will answer an unspecified number
>>>> of questions by selecting the corresponding check box and when finished it
>>>> will give them their average for that specific situation.  They will then
>>>> clear all check boxes and go to the next set.  The macro will make
>>>> resetting all the check boxes less of a pain.
>>>>
>>>> Thank you for your time in this matter.
>>>>
>>>> John
>>>>
>>>> --
>>>> FORUM RULES (934+ members already BANNED for violation)
>>>>
>>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>>> will not get quick attention or may not be answered.
>>>>
>>>> 2) Don't post a question in the thread of another member.
>>>>
>>>> 3) Don't post questions regarding breaking or bypassing any security
>>>> measure.
>>>>
>>>> 4) Acknowledge the responses you receive, good or bad.
>>>>
>>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>>> signatures are prohibited.
>>>>
>>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>>> Forum owners and members are not responsible for any loss.
>>>>
>>>>
>>>> --
>>>> To post to this group, send email to excel-macros@googlegroups.com
>>>>
>>>
>>>
>>>
>>> --
>>> *Regards*
>>> * *
>>> *Ashish Koul*
>>> *http://www.excelvbamacros.com/*
>>>
>>>
>>> P Before printing, think about the environment.
>>>
>>>
>>> --
>>> FORUM RULES (934+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>>
>>> 4) Acknowledge the responses you receive, good or bad.
>>>
>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.
>>>
>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.
>>>
>>>
>>> --
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>
>> --
>> FORUM RULES (934+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
&

Re: $$Excel-Macros$$ Macro Needed to Uncheck All Checkboxes

[John A. Smith]

Perfect!  Thank you Ashish. Thank you very much.

John

On Thu, Nov 17, 2011 at 9:45 AM, ashish koul  wrote:

> Sub test()
> Dim rngap As Range
>
> For Each rngap In Sheets(1).Range("m5:q17")
>
> rngap = False
> Next
> End Sub
>
>
> On Thu, Nov 17, 2011 at 8:11 PM, John A. Smith wrote:
>
>> Your help is appreciated.  I need a macro to uncheck all the check boxes
>> on the attached spreadsheet.  User will answer an unspecified number of
>> questions by selecting the corresponding check box and when finished it
>> will give them their average for that specific situation.  They will then
>> clear all check boxes and go to the next set.  The macro will make
>> resetting all the check boxes less of a pain.
>>
>> Thank you for your time in this matter.
>>
>> John
>>
>> --
>> FORUM RULES (934+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
>
>
> --
> *Regards*
> * *
> *Ashish Koul*
> *http://www.excelvbamacros.com/*
>
>
> P Before printing, think about the environment.
>
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (934+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Macro To Add a Cell From A List And Create A New Tab

[John A. Smith]

Thank you Don, your help is very much appreciated.

John

On Tue, Nov 8, 2011 at 2:57 PM, dguillett1  wrote:

>   Use this macro and be sure to save your file as an .xls or .xlsm
>
> Sub MakeSheetsFromVehicleList()
> Sheets("Vehicle List").Select
> Dim c As Range
> Dim lr As Long
> lr = Cells(Rows.Count, 1).End(xlUp).Row
> For Each c In Range("a2:a" & lr)
> With Sheets("form")
> Range("d56").Value = c
> .Copy after:=ActiveSheet
> End With
> Next c
> End Sub
>
>
> Don Guillett
> SalesAid Software
> dguille...@gmail.com
>
>  *From:* John A. Smith 
> *Sent:* Tuesday, November 08, 2011 1:16 PM
> *To:* excel-macros@googlegroups.com
>  *Subject:* $$Excel-Macros$$ Macro To Add a Cell From A List And Create A
> New Tab
>
>   I have a spreadsheet with a tab called Form.  There is a second tab
> called Vehicle List.  I need a macro to take the first vehicle on the list
> and put that number in cell D56 of the form tab and then copy the tab and
> repeat this process until all the vehicle numbers are on the form in their
> own tab.  The tab names are not important as the spreadsheet will be saved
> in the pdf format and the tabs will become pages.
>
> Thank you for your help.
>
> John
> --
> FORUM RULES (925+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (925+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (925+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Macro To Add a Cell From A List And Create A New Tab

[John A. Smith]

Thank you very much Paul.  Worked very well. I appreciate your time.

John

On Tue, Nov 8, 2011 at 2:33 PM, Paul Schreiner wrote:

>   Try this:
>
> Sub Make_Sheets()
> Dim vCnt, vNum
> ThisWorkbook.Activate
> Sheets("Form").Select
> Application.ScreenUpdating = False
> vCnt = Application.WorksheetFunction.CountA(Sheets("Vehicle
> List").Range("A1:A65000"))
> For vNum = 2 To vCnt
> Sheets("Form").Copy After:=Sheets(3)
> Range("D56").Value = Sheets("Vehicle List").Cells(vNum, "A").Value
> Next vNum
> Application.ScreenUpdating = True
> End Sub
>
> You'll have to save your file as .xlsb
> or .xlsm, but I prefer .xlsb
>
>
> *Paul*
>
> -
> *“Do all the good you can,
> By all the means you can,
> In all the ways you can,
> In all the places you can,
> At all the times you can,
> To all the people you can,
> As long as ever you can.” - John Wesley
> *-
>
>
>  --
> *From:* John A. Smith 
> *To:* excel-macros@googlegroups.com
> *Sent:* Tue, November 8, 2011 2:16:05 PM
> *Subject:* $$Excel-Macros$$ Macro To Add a Cell From A List And Create A
> New Tab
>
> I have a spreadsheet with a tab called Form.  There is a second tab called
> Vehicle List.  I need a macro to take the first vehicle on the list and put
> that number in cell D56 of the form tab and then copy the tab and repeat
> this process until all the vehicle numbers are on the form in their own
> tab.  The tab names are not important as the spreadsheet will be saved in
> the pdf format and the tabs will become pages.
>
> Thank you for your help.
>
> John
>
> --
> FORUM RULES (925+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (925+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (925+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

$$Excel-Macros$$ Macro To Add a Cell From A List And Create A New Tab

[John A. Smith]

I have a spreadsheet with a tab called Form.  There is a second tab called
Vehicle List.  I need a macro to take the first vehicle on the list and put
that number in cell D56 of the form tab and then copy the tab and repeat
this process until all the vehicle numbers are on the form in their own
tab.  The tab names are not important as the spreadsheet will be saved in
the pdf format and the tabs will become pages.

Thank you for your help.

John

-- 
FORUM RULES (925+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


Macro to Add a Cell From a List and Create a New Tab.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ Time Calculation From Two Different Formats

[John A. Smith]

Sam,

Thank you for that, it saves a column.

John

On Wed, Nov 2, 2011 at 9:25 PM, Sam Mathai Chacko wrote:

> If you only want the finish time without splitting it in to Date and Time,
> you could also use
>
> =LEFT(TRIM(A23),10)+TEXT(RIGHT(TRIM(A23),4),"00\:00")+(B23/(60*24)) in
> mm/dd/ hh:mm format
>
> Regards,
>
> Sam Mathai Chacko
>
>
> On Thu, Nov 3, 2011 at 5:52 AM, Haseeb Avarakkan <
> haseeb.avarak...@gmail.com> wrote:
>
>> Hello John,
>>
>> If the Arrival Date & Time is "09/08/2011 2310" & Length is 343 (Row 3
>> data) Finish date & time is 09/09/2011 04:53. Arrival & Finish Date are not
>> same.
>>
>> Here is the another way. See the attached.
>>
>> __
>> Haseeb
>>
>> --
>> FORUM RULES (925+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
>
>
> --
> Sam Mathai Chacko
>
> --
> FORUM RULES (925+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (925+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Time Calculation From Two Different Formats

[John A. Smith]

Haseeb,

Thank you for that.  That solved an unrelated issue and was very helpful,
thank you.

John

On Wed, Nov 2, 2011 at 8:22 PM, Haseeb Avarakkan  wrote:

> Hello John,
>
> If the Arrival Date & Time is "09/08/2011 2310" & Length is 343 (Row 3
> data) Finish date & time is 09/09/2011 04:53. Arrival & Finish Date are not
> same.
>
> Here is the another way. See the attached.
>
> __
> Haseeb
>
> --
> FORUM RULES (925+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (925+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Time Calculation From Two Different Formats

[John A. Smith]

Ashish,

Wonderful.  Works just as I need.  Thank you for your continued help.

John

On Wed, Nov 2, 2011 at 9:02 AM, ashish koul  wrote:

> see if it helps
>
>
>
>
> On Wed, Nov 2, 2011 at 4:38 PM, John A. Smith wrote:
>
>> I receive a download which shows Arrival Time as “09/14/2011 0938” and I
>> also receive a Process Length time in total minutes, like “133”.  I need
>> to show finish time of 1151 (the addition of the 2 hours and 13 minutes to
>> the 0938 Arrival Time).  Please see attached spreadsheet for further
>> explanation.
>>
>> Thank you for your guidance.
>>
>> John
>>
>> --
>> FORUM RULES (925+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
>
>
> --
> *Regards*
> * *
> *Ashish Koul*
> *http://www.excelvbamacros.com/*
>
>
> P Before printing, think about the environment.
>
>
>   --
> FORUM RULES (925+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (925+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Need to Format Date By Formula

[John A. Smith]

Excellent!  Thank you for your quick response and your continued teachings.

John

On Tue, Oct 25, 2011 at 11:56 AM, Sam Mathai Chacko wrote:

> =DATEVALUE(TEXT((LEFT(A2,11)),"MM/DD/"))
>
> OR
>
> =DATEVALUE(LEFT(TRIM(A2),10))
>
> rEGARDS,
>
> Sam Mathai Chacko
>
> On Tue, Oct 25, 2011 at 9:22 PM, John A. Smith wrote:
>
>> Excel Teachers, I need to format 09/14/2011 0938 into 9/14/11 (or a real
>> date format).  Please see attached spreadsheet.
>>
>> Thank you for your help.
>>
>> John
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> Sam Mathai Chacko
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

$$Excel-Macros$$ Need to Format Date By Formula

[John A. Smith]

Excel Teachers, I need to format 09/14/2011 0938 into 9/14/11 (or a real
date format).  Please see attached spreadsheet.

Thank you for your help.

John

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel


Date Formatting by Formula.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ Looking Up Words When You Only Have A Few Letters

[John A. Smith]

Noorain,

Thank you, I love using offset and never tried the wild card approach.  Two
excellent alternatives, thank you.

John

On Fri, Oct 21, 2011 at 7:42 AM, NOORAIN ANSARI wrote:

> Dear John,
>
> Ashish's Solution is Fentastic.
> Please see attached sheet with another options.
>
>  On Fri, Oct 21, 2011 at 4:49 PM, John A. Smith wrote:
>
>> Esteemed Group,
>>
>> I would like to know if there is a formula that would allow you to type "
>> ian" in a cell and have it look up and find "Giant Wing" in a list of
>> unique words.  Please see the attached example.
>>
>> Thank you very much for your help.
>>
>> John
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> Thanks & regards,
> Noorain Ansari
>  *http://excelmacroworld.blogspot.com/*<http://excelmacroworld.blogspot.com/>
> *http://noorain-ansari.blogspot.com/*<http://noorain-ansari.blogspot.com/>
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Looking Up Words When You Only Have A Few Letters

[John A. Smith]

Thank you Ashish for your quick and very enlightening response.

John

On Fri, Oct 21, 2011 at 7:28 AM, ashish koul  wrote:

> VLOOKUP("*"&H4&"*",E:E,1,0)
>
>
> On Fri, Oct 21, 2011 at 4:49 PM, John A. Smith wrote:
>
>> Esteemed Group,
>>
>> I would like to know if there is a formula that would allow you to type "
>> ian" in a cell and have it look up and find "Giant Wing" in a list of
>> unique words.  Please see the attached example.
>>
>> Thank you very much for your help.
>>
>> John
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> *Regards*
> * *
> *Ashish Koul*
> *http://www.excelvbamacros.com/*
>
>
> P Before printing, think about the environment.
>
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

$$Excel-Macros$$ Looking Up Words When You Only Have A Few Letters

[John A. Smith]

Esteemed Group,

I would like to know if there is a formula that would allow you to type "ian"
in a cell and have it look up and find "Giant Wing" in a list of unique
words.  Please see the attached example.

Thank you very much for your help.

John

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel


Looking up Words with a Few Letters.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ Formula to LOOKUP a value in a table and return the table header value

[John A. Smith]

Excellent!  Thank you Haseeb, that works like a charm.  Thank you for you
help.

John




On Fri, Oct 7, 2011 at 2:02 PM, Haseeb Avarakkan  wrote:

> Hi John,
>
> Try these,
>
> K3, copy down.
>
> =LOOKUP(LOOKUP(9E+300,C3:J3),N3:X3,N$2:X$2)
>
> L3, copy down.
>
> =LOOKUP(LOOKUP(9E+300,C3:J3),Z3:AJ3,Z$2:AJ$2)
>
> This will look the last entered value.
>
> HTH
> Haseeb
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Formula to LOOKUP a value in a table and return the table header value

[John A. Smith]

 *2011* * * PictOld   *Achievement   (1-10)* PictOld   *Improvement
(0-9)* *Jan-Mar* *Apr-Jun* *July-Aug* *Jul-Dec* *Jul11-Mar12* 100.00%
100.00% 100.00% 10 9

Yes, I want the formula to pick up the last entered value on the right (the
next period would be blank like the BLUE cell above).  Then when I enter the
next periods data, the formula picks up the new most righted non-blank cell.

Thank you sir for your patience and teachings

John
On Fri, Oct 7, 2011 at 12:07 PM, Dilip Pandey  wrote:

> You are welcome John.
>
> In order to include next set of data we can use defined name(s) but for
> this to work, we need to create a locator cell, refrencing which Excel can
> understand which period to pick up as I am not sure if you want right most
> data to be picked up always.
>
> Share your thoughts and then we can proceed . . Cheers.
>
> Regards,
> DILIPandey
>   On 7 Oct 2011 20:46, "John A. Smith"  wrote:
>
>> Dilip,
>>
>> Thank you for the quick response.  It works great.  One other question;
>> How could I change the formula to pick up the next period if data is added
>> to cell I3 (through I13)?
>>
>> I marked up the spreadsheet for my new question.
>>
>> Thanks for the help with the formula, it works great.
>>
>> John
>>
>> On Fri, Oct 7, 2011 at 10:00 AM, Dilip Pandey wrote:
>>
>>> Hi John,
>>>
>>> Please find attached the solved worksheet. Let me know if this works.
>>> Thanks.
>>>
>>> Regards,
>>> DILIPandey
>>>   On 7 Oct 2011 18:14, "John A. Smith"  wrote:
>>>
>>> --
>>>
>>> --
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><><><><>
>>> Like our page on facebook , Just follow below link
>>> http://www.facebook.com/discussexcel
>>>
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Re: Formula to LOOKUP a value in a table and return the table header value

[John A. Smith]

GoldenLance,

I attached the example.  It was detailed in the attachment.

John

On Fri, Oct 7, 2011 at 8:53 AM, GoldenLance  wrote:

> You might want to clarify 'I need the formula for the GREEN cells to
> lookup the value in the appropriate table', with an example.
>
> On Oct 7, 5:44 pm, "John A. Smith"  wrote:
> > I need the formula to lookup a value in a table and return the table
> column
> > header value.  Please see attached spread sheet.
> >
> > Thank you for your kind help.
> >
> > John
> >
> >  LOOKUP Value in a Table and Return Table Header Value.xlsx
> > 31KViewDownload
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

$$Excel-Macros$$ Formula to LOOKUP a value in a table and return the table header value

[John A. Smith]

I need the formula to lookup a value in a table and return the table column
header value.  Please see attached spread sheet.

Thank you for your kind help.

John

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel


LOOKUP Value in a Table and Return Table Header Value.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ How To Add A Year Field to a PivotTable Field List

[John A. Smith]

Chethan,

Thank you for your quick and enlightening response.  This opens up new
avenues for me.  Thank you very much!!!

John




On Mon, Oct 3, 2011 at 4:26 PM, B.N.Chethan kumar  wrote:

> Hi,
>
> you have option to group the file...Bring the data in excel pivit..and
> right clickselect group...and select group by year..
>
> you can also group my month & Quarter too..
>
>
> Regards
> Chethan Kumar BN
>
> On Tue, Oct 4, 2011 at 1:32 AM, John A. Smith wrote:
>
>> Attached is a spreadsheet from this forum.  I would like to know how the
>> "Year" field was added to a pivot table with only a date and amount column
>> in the Data Source (See Attached File).
>>
>> This would be very helpful in a lot of applications I run into.
>>
>> Thank you for your ongoing assistance and teachings.
>>
>> John
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> B.N Chetan kumar
>
>  --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Incremental Time Calculation from 11:30:00 AM Time Format

[John A. Smith]

Thank you all very much for taking your time to teach others.  I really
appreciate your help.

John



On Thu, Sep 29, 2011 at 4:06 PM, dguillett1  wrote:

>  =(D2-C2)*24
>
>  *From:* John A. Smith 
> *Sent:* Thursday, September 29, 2011 2:55 PM
> *To:* excel-macros@googlegroups.com
> *Subject:* $$Excel-Macros$$ Incremental Time Calculation from 11:30:00 AM
> Time Format
>
>   Excel Experts,
>
> I need to calculate the time differences between 11:30:00 AM and 11:37:00
> AM and show the 7 minutes in the decimal hour format.
>
> Please see attached sample spreadsheet for the solution I need.
>
> Thank you for your continuing Excel help.
>
> John
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

$$Excel-Macros$$ Incremental Time Calculation from 11:30:00 AM Time Format

[John A. Smith]

Excel Experts,

I need to calculate the time differences between 11:30:00 AM and 11:37:00 AM
and show the 7 minutes in the decimal hour format.

Please see attached sample spreadsheet for the solution I need.

Thank you for your continuing Excel help.

John

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel


Incremental Time Calculations.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ Calculate Product Sold To Date by Product

[John A. Smith]

Thank you Noorain.  I appreciate your help very much.

John

On Wed, Aug 31, 2011 at 1:26 PM, NOORAIN ANSARI wrote:

> Dear john,
>
> Please see attached sheet..
>
>  On Tue, Aug 30, 2011 at 9:39 PM, John A. Smith wrote:
>
>> Your help please.  I have a log of product sold and need to calculate the
>> different product sold to date, like a running accumulation field specific
>> to product listed in column B.
>>
>> Please see my attached spreadsheet.  Thanking you in advance for all your
>> great help.
>>
>> John
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> Thanks & regards,
> Noorain Ansari
> *http://noorain-ansari.blogspot.com/*<http://noorain-ansari.blogspot.com/>
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Calculate Product Sold To Date by Product

[John A. Smith]

Thank you very much for your quick response, your patience and your help.

John

On Tue, Aug 30, 2011 at 12:37 PM, dguillett1  wrote:

>  in d2 copied down
> =SUMIF($B$2:B2,B2,$C$2:C2)
>
>  *From:* John A. Smith 
> *Sent:* Tuesday, August 30, 2011 11:09 AM
> *To:* excel-macros@googlegroups.com
> *Subject:* $$Excel-Macros$$ Calculate Product Sold To Date by Product
>
>   Your help please.  I have a log of product sold and need to calculate
> the different product sold to date, like a running accumulation field
> specific to product listed in column B.
>
> Please see my attached spreadsheet.  Thanking you in advance for all your
> great help.
>
> John
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

$$Excel-Macros$$ Calculate Product Sold To Date by Product

[John A. Smith]

Your help please.  I have a log of product sold and need to calculate the
different product sold to date, like a running accumulation field specific
to product listed in column B.

Please see my attached spreadsheet.  Thanking you in advance for all your
great help.

John

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel


Accumulated Balance by Date.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ Rolling 12-Month Average Lookup Dashboard Problem....

[John A. Smith]

This is exactly what I was looking for.  Again thank you for teaching us!

John

On Thu, Aug 18, 2011 at 11:41 AM, ashish koul  wrote:

> try this
>
>
> On Thu, Aug 18, 2011 at 8:51 PM, John A. Smith wrote:
>
>> I have a dashboard that looks at a rolling average of the previous 12
>> months of data.  I paste a download into Excel and cut and paste till I have
>> the last month and the previous 11 for my 12-month rolling average.
>>
>> Please, can there be a dynamic look up based on the range selected?  A
>> sample of my dashboard and database is attached for your reference.
>>
>> Thank you for your continued guidance in my learning Excel quest.
>>
>> John
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> *Regards*
> * *
> *Ashish Koul*
> *akoul*.*blogspot*.com <http://akoul.blogspot.com/>
> http://akoul.posterous.com/
> *akoul*.wordpress.com <http://akoul.wordpress.com/>
> My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830>
>
>
> P Before printing, think about the environment.
>
>
>   --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

$$Excel-Macros$$ Rolling 12-Month Average Lookup Dashboard Problem....

[John A. Smith]

I have a dashboard that looks at a rolling average of the previous 12 months
of data.  I paste a download into Excel and cut and paste till I have the
last month and the previous 11 for my 12-month rolling average.

Please, can there be a dynamic look up based on the range selected?  A
sample of my dashboard and database is attached for your reference.

Thank you for your continued guidance in my learning Excel quest.

John

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel


Rolling 12 Month Average Lookup Dashboard.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

$$Excel-Macros$$ Add 1 to a cell every time the file is opened

[John A. Smith]

Excel guru's, can I make a receipt file that adds 1 to a specific cell
everytime the file is opened?

Please see attached.

Thank you.

John

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel


Material Receipt.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ Excel 2007 Quick Access Toolbar- Take it with me...

[John A. Smith]

So either this didn't post, or there is no way on earth for this to happen.

John

On Fri, Jul 15, 2011 at 2:39 PM, John A. Smith wrote:

> Is there a way to have my Quick Access Toolbar portable (like on a jump
> drive) so any computer with Excel 2007 can have my customized Quick Access
> Toolbar when I use it?
>
> Thank you.
>
> John
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

$$Excel-Macros$$ Excel 2007 Quick Access Toolbar- Take it with me...

[John A. Smith]

Is there a way to have my Quick Access Toolbar portable (like on a jump
drive) so any computer with Excel 2007 can have my customized Quick Access
Toolbar when I use it?

Thank you.

John

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

$$Excel-Macros$$ Formula to Calculate Hours Between Dates

[John A. Smith]

Dear Excel Teachers,

I need a formula to give me the decimal hours between two dates.  I have
attached a sample.

Thanking you in advance for your on-going help and wisdom.

John

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel


Decimal Hours Between Two Dates.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ Transpose Data by Formula

[John A. Smith]

STDEV(i),

Excellent!  Works like a charm.  If I would like to add a formula to the
total column, that would only pick up only the Order Qty amount, how would I
do that? (the order number is a six digit number and adds to the total)  I
would use =SUMIF(A$25:Z$25,"Order Qty",A26:Z26) after the fact, but how
would I change the VBA code to always do that?

I really appreciate the time you spend teaching others.  Thank you.

John



On Mon, Jun 13, 2011 at 1:30 AM, STDEV(i)  wrote:

> Dear  Mr. Smith,
>
> Please try and check this VBA Code, if it helps...
>
>
> Sub AbnormalizeYourTabel()
>' STDEV(i) / milis belajar-excel / 08 apr 2011
>' retouched for another table-structure
>' jakarta, Jun 12, 2011
>'---
>Dim Tbl As Range, NewTbl As Range
>Dim n As Long, r As Long, i As Long, tR As Long
>Dim c As Integer, u As Integer, TotQty As Double, ArtQty()
>Dim StrItm As String, Itm As String
>
>Set Tbl = Sheets("Data Transpose").Cells(1).CurrentRegion
>tR = Tbl.Rows.Count
>Set NewTbl = Tbl(tR + 6, 1)
>
>' searching UniqItems
>StrItm = "|"
>Application.Calculation = -4135 '=manual
>Application.ScreenUpdating = 0
>For i = 2 To tR
>   Itm = Tbl(i, 1) & "|"
>   If InStr(1, StrItm, Tbl(i, 1), 1) = 0 Then
>  r = r + 1
>  StrItm = StrItm & Itm
>  Tbl(i, 1).Resize(1, 2).Copy
>  NewTbl(r, 1).PasteSpecial 12
>   End If
> Next i
>
>' Repost all datas in New Table-Structure
>Application.CutCopyMode = False
>Set NewTbl = NewTbl.CurrentRegion
>ReDim ArtQty(1 To NewTbl.Rows.Count)
>For n = 1 To NewTbl.Rows.Count
>   c = 0: TotQty = 0
>   For i = 2 To tR
>  If NewTbl(n, 1) = Tbl(i, 1) Then
>  c = c + 3
> *TotQty = TotQty + Tbl(i, 3*)
> Tbl(i, 3).Resize(1, 3).Copy
> NewTbl(n, c).PasteSpecial 12
>  End If
>   Next i
>   *ArtQty(n) = TotQty*
>Next n
>Tbl.Resize(1, Tbl.Columns.Count - 2).Copy NewTbl(0, 1)
>u = (NewTbl.CurrentRegion.Columns.Count - 1)
>
>' Headings..
>Tbl(1, 3).Resize(1, 3).Copy
>For c = 3 To u Step 3
>   NewTbl(0, c).PasteSpecial xlAll
>Next c
>
> *   ' new column (Total Qty)*
>With NewTbl(0, c)
>   .Value = "TOTAL QTY"
>   .Font.Bold = True
>   .BorderAround Weight:=xlThin
>   .HorizontalAlignment = xlCenter
>   .VerticalAlignment = xlCenter
>   .WrapText = True
>End With
>For n = 1 To NewTbl.Rows.Count
>   NewTbl(n, c) = ArtQty(n)
>Next n
>
>Application.CutCopyMode = False
>Application.Calculation = -4105
>Application.ScreenUpdating = 1
>
> End Sub
>
>
>
>
> On Mon, Jun 13, 2011 at 2:07 AM, John A. Smith wrote:
>
>> Please, attached is an example of data which I need to transpose and a
>> pivot table doesn't do it...
>>
>> Thank you for your help.
>>
>> John
>>
>>
>>  --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

$$Excel-Macros$$ Transpose Data by Formula

[John A. Smith]

Please, attached is an example of data which I need to transpose and a pivot
table doesn't do it...

Thank you for your help.

John

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel


Transpose Data with a Formula.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ Formula Needed to Extract Data

[John A. Smith]

It helped very much Ashish, thank you again for your kind teachings.

John




On Fri, Jun 3, 2011 at 1:43 PM, ashish koul  wrote:

> see if it helps
>
>
> On Wed, Jun 1, 2011 at 7:07 PM, John A. Smith wrote:
>
>> Need help please extracting specific data from an Excel spreadsheet into
>> an Excel report.
>>
>> The arrangement of the data isn't always in the same order as shown in the
>> attached sample.
>>
>> Thank you again for your kind help.
>>
>> John
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> *Regards*
> * *
> *Ashish Koul*
> *akoul*.*blogspot*.com <http://akoul.blogspot.com/>
> http://akoul.posterous.com/
> *akoul*.wordpress.com <http://akoul.wordpress.com/>
> My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830>
>
>
> P Before printing, think about the environment.
>
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

$$Excel-Macros$$ Formula Needed to Extract Data

[John A. Smith]

Need help please extracting specific data from an Excel spreadsheet into an
Excel report.

The arrangement of the data isn't always in the same order as shown in the
attached sample.

Thank you again for your kind help.

John

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel


Formula Needed to Extract Data from Excel.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ Macro to Enter Current Time and Date

[John A. Smith]

Excellent!  Thank you very Much!

John



On Tue, May 24, 2011 at 11:44 AM, ashish koul  wrote:

>
>
>
> try this
>>
>> Sub test()
>>
>> ActiveCell.Value = Now()
>>
>>
>> End Sub
>>
>> or
>
>> Sub test()
>>
>> Range("B5029").Value = Now()
>>
>>
>> End Sub
>>
>>
>>
>>
>> On Tue, May 24, 2011 at 8:39 PM, John A. Smith wrote:
>>
>>> Sub NOWENTRY()
>>> '
>>> Please help with my macro to enter current time and date into any cell I
>>> choose.  It always goes back to the cell in the macro and I want it to work
>>> for the current cell the cursor is sitting on when I hit Ctrl+n.
>>>
>>> My Macro:
>>>
>>> ' NOWENTRY Macro
>>> ' Enters current time and date
>>> '
>>> ' Keyboard Shortcut: Ctrl+n
>>> '
>>> Range("B5029").Select
>>> ActiveCell.FormulaR1C1 = "=NOW()"
>>> Range("B5029").Select
>>> Selection.Copy
>>> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
>>> SkipBlanks _
>>> :=False, Transpose:=False
>>> End Sub
>>> Thank you again for your help.
>>>
>>> John
>>>
>>> --
>>>
>>> --
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><><><><>
>>> Like our page on facebook , Just follow below link
>>> http://www.facebook.com/discussexcel
>>>
>>
>>
>>
>> --
>> *Regards*
>> * *
>> *Ashish Koul*
>> *akoul*.*blogspot*.com <http://akoul.blogspot.com/>
>> http://akoul.posterous.com/
>>  *akoul*.wordpress.com <http://akoul.wordpress.com/>
>> My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830>
>>
>>
>> P Before printing, think about the environment.
>>
>>
>>
>
>
> --
> *Regards*
> * *
> *Ashish Koul*
> *akoul*.*blogspot*.com <http://akoul.blogspot.com/>
> http://akoul.posterous.com/
>  *akoul*.wordpress.com <http://akoul.wordpress.com/>
> My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830>
>
>
> P Before printing, think about the environment.
>
>
>  --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

$$Excel-Macros$$ Macro to Enter Current Time and Date

[John A. Smith]

Sub NOWENTRY()
'
Please help with my macro to enter current time and date into any cell I
choose.  It always goes back to the cell in the macro and I want it to work
for the current cell the cursor is sitting on when I hit Ctrl+n.

My Macro:

' NOWENTRY Macro
' Enters current time and date
'
' Keyboard Shortcut: Ctrl+n
'
Range("B5029").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("B5029").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub
Thank you again for your help.

John

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

$$Excel-Macros$$ Special Cursor Movement After the Enter Key is Pressed

[John A. Smith]

I need a macro that will move the curser in a specific pattern of; Right one
cell the first time, and down one and left one the second time and then
repeat.

It would save a lot of time entering the data in two side by side columns so
I need it to not be column specific (so I could use it in any two side by
side columns).

Thank you for your continueing dedication to helping others with Excel.

John

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel


Special Curser Movement Upon Hitting Enter.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ File Consolidation and Data SummarizationFormula Help Needed

[John A. Smith]

Again, thank you Ashish very much.  You have taught me a lot.

John



On Sat, Apr 9, 2011 at 6:27 AM, ashish koul  wrote:

>
> in excel 2007 format
>
>
> On Fri, Apr 8, 2011 at 2:49 AM, John A. Smith wrote:
>
>> Ashish,
>>
>> One more thing please; that macro in a 2007 format?  I ran out of columns.
>>
>> Thank you again.
>>
>> John
>>
>>   On Thu, Apr 7, 2011 at 11:22 AM, ashish koul wrote:
>>
>>> try this
>>>
>>> run macro to merge the data and check sheet 2 and 3 for formula
>>>
>>>   On Tue, Apr 5, 2011 at 11:10 PM, John A. Smith >> > wrote:
>>>
>>>> Ashish,
>>>>
>>>> I have attached the My Questions tab as a file by itself.  Thank you for
>>>> your kind help.
>>>>
>>>> John
>>>>
>>>>   On Tue, Apr 5, 2011 at 12:55 PM,  wrote:
>>>>
>>>>> Ashish,
>>>>>
>>>>> There are 22 employees. I don't need any blanks if no data for an
>>>>> employee. If their name isn't on the list they were off. If their name is 
>>>>> on
>>>>> the list but no orders pulled, they were assigned to a utility job and 
>>>>> don't
>>>>> need to be on the summary either.
>>>>>
>>>>> As soon as I can get to my computer I will send the file with the "My
>>>>> Questions" tab.
>>>>>
>>>>> Thank you.
>>>>>
>>>>> John
>>>>>
>>>>> Sent from my Verizon Wireless BlackBerry
>>>>> ------
>>>>> *From: *ashish koul 
>>>>> *Sender: *excel-macros@googlegroups.com
>>>>> *Date: *Tue, 5 Apr 2011 22:06:17 +0530
>>>>> *To: *
>>>>> *ReplyTo: *excel-macros@googlegroups.com
>>>>> *Subject: *Re: $$Excel-Macros$$ File Consolidation and Data
>>>>> Summarization Formula Help Needed
>>>>>
>>>>> i think my question tab is missing
>>>>>
>>>>> do you want to show the blank records if the data of the employ is
>>>>> missing in day
>>>>>
>>>>> also how many total employees you have
>>>>>
>>>>> On Tue, Apr 5, 2011 at 2:03 AM, John A. Smith 
>>>>> wrote:
>>>>>
>>>>>> I receive an Excel file download everyday (see >>>>> 032911.xls>,  and >>>>> Report
>>>>>> 040311.xls>.  I need to summarize them into one database and be able
>>>>>> to report specific trends and situations.  Please see the attached
>>>>>> file .  It has three tabs:  Data
>>>>>> Summary (as it would look if all reports were combined), My Questions 
>>>>>> (tab)
>>>>>> is where I need specific help.  I need a macro to consolidate the
>>>>>> files and formula help to extract the data.
>>>>>>
>>>>>> Thank you for your continuing help teaching the power of Excel.
>>>>>>
>>>>>> John
>>>>>>
>>>>>> --
>>>>>>
>>>>>> --
>>>>>> Some important links for excel users:
>>>>>> 1. Follow us on TWITTER for tips tricks and links :
>>>>>> http://twitter.com/exceldailytip
>>>>>> 2. Join our LinkedIN group @
>>>>>> http://www.linkedin.com/groups?gid=1871310
>>>>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>>>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>>>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>>>>
>>>>>> To post to this group, send email to excel-macros@googlegroups.com
>>>>>>
>>>>>> <><><><><><><><><><><><><><><><><><><><><><>
>>>>>> Like our page on facebook , Just follow below link
>>>>>> http://www.facebook.com/discussexcel
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> *Regards*
>>>>> * *
>>>>

Re: $$Excel-Macros$$ File Consolidation and Data SummarizationFormula Help Needed

[John A. Smith]

Ashish,

One more thing please; that macro in a 2007 format?  I ran out of columns.

Thank you again.

John

On Thu, Apr 7, 2011 at 11:22 AM, ashish koul  wrote:

> try this
>
> run macro to merge the data and check sheet 2 and 3 for formula
>
>   On Tue, Apr 5, 2011 at 11:10 PM, John A. Smith 
> wrote:
>
>> Ashish,
>>
>> I have attached the My Questions tab as a file by itself.  Thank you for
>> your kind help.
>>
>> John
>>
>>   On Tue, Apr 5, 2011 at 12:55 PM,  wrote:
>>
>>> Ashish,
>>>
>>> There are 22 employees. I don't need any blanks if no data for an
>>> employee. If their name isn't on the list they were off. If their name is on
>>> the list but no orders pulled, they were assigned to a utility job and don't
>>> need to be on the summary either.
>>>
>>> As soon as I can get to my computer I will send the file with the "My
>>> Questions" tab.
>>>
>>> Thank you.
>>>
>>> John
>>>
>>> Sent from my Verizon Wireless BlackBerry
>>> --
>>> *From: *ashish koul 
>>> *Sender: *excel-macros@googlegroups.com
>>> *Date: *Tue, 5 Apr 2011 22:06:17 +0530
>>> *To: *
>>> *ReplyTo: *excel-macros@googlegroups.com
>>> *Subject: *Re: $$Excel-Macros$$ File Consolidation and Data
>>> Summarization Formula Help Needed
>>>
>>> i think my question tab is missing
>>>
>>> do you want to show the blank records if the data of the employ is
>>> missing in day
>>>
>>> also how many total employees you have
>>>
>>> On Tue, Apr 5, 2011 at 2:03 AM, John A. Smith wrote:
>>>
>>>> I receive an Excel file download everyday (see >>> 032911.xls>,  and >>> 040311.xls>.  I need to summarize them into one database and be able to
>>>> report specific trends and situations.  Please see the attached file
>>>> .  It has three tabs:  Data Summary
>>>> (as it would look if all reports were combined), My Questions (tab) is 
>>>> where
>>>> I need specific help.  I need a macro to consolidate the files and
>>>> formula help to extract the data.
>>>>
>>>> Thank you for your continuing help teaching the power of Excel.
>>>>
>>>> John
>>>>
>>>> --
>>>>
>>>> --
>>>> Some important links for excel users:
>>>> 1. Follow us on TWITTER for tips tricks and links :
>>>> http://twitter.com/exceldailytip
>>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>>
>>>> To post to this group, send email to excel-macros@googlegroups.com
>>>>
>>>> <><><><><><><><><><><><><><><><><><><><><><>
>>>> Like our page on facebook , Just follow below link
>>>> http://www.facebook.com/discussexcel
>>>>
>>>
>>>
>>>
>>> --
>>> *Regards*
>>> * *
>>> *Ashish Koul*
>>> *akoul*.*blogspot*.com <http://akoul.blogspot.com/>
>>> *akoul*.wordpress.com <http://akoul.wordpress.com/>
>>> My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830>
>>>
>>>
>>> P Before printing, think about the environment.
>>>
>>>
>>> --
>>>
>>> --
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><

Re: $$Excel-Macros$$ File Consolidation and Data SummarizationFormula Help Needed

[John A. Smith]

Ashish,

Perfect! Thank you for your patience and your teaching.

John

On Thu, Apr 7, 2011 at 11:22 AM, ashish koul  wrote:

> try this
>
> run macro to merge the data and check sheet 2 and 3 for formula
>
>   On Tue, Apr 5, 2011 at 11:10 PM, John A. Smith 
> wrote:
>
>> Ashish,
>>
>> I have attached the My Questions tab as a file by itself.  Thank you for
>> your kind help.
>>
>> John
>>
>>   On Tue, Apr 5, 2011 at 12:55 PM,  wrote:
>>
>>> Ashish,
>>>
>>> There are 22 employees. I don't need any blanks if no data for an
>>> employee. If their name isn't on the list they were off. If their name is on
>>> the list but no orders pulled, they were assigned to a utility job and don't
>>> need to be on the summary either.
>>>
>>> As soon as I can get to my computer I will send the file with the "My
>>> Questions" tab.
>>>
>>> Thank you.
>>>
>>> John
>>>
>>> Sent from my Verizon Wireless BlackBerry
>>> --
>>> *From: *ashish koul 
>>> *Sender: *excel-macros@googlegroups.com
>>> *Date: *Tue, 5 Apr 2011 22:06:17 +0530
>>> *To: *
>>> *ReplyTo: *excel-macros@googlegroups.com
>>> *Subject: *Re: $$Excel-Macros$$ File Consolidation and Data
>>> Summarization Formula Help Needed
>>>
>>> i think my question tab is missing
>>>
>>> do you want to show the blank records if the data of the employ is
>>> missing in day
>>>
>>> also how many total employees you have
>>>
>>> On Tue, Apr 5, 2011 at 2:03 AM, John A. Smith wrote:
>>>
>>>> I receive an Excel file download everyday (see >>> 032911.xls>,  and >>> 040311.xls>.  I need to summarize them into one database and be able to
>>>> report specific trends and situations.  Please see the attached file
>>>> .  It has three tabs:  Data Summary
>>>> (as it would look if all reports were combined), My Questions (tab) is 
>>>> where
>>>> I need specific help.  I need a macro to consolidate the files and
>>>> formula help to extract the data.
>>>>
>>>> Thank you for your continuing help teaching the power of Excel.
>>>>
>>>> John
>>>>
>>>> --
>>>>
>>>> --
>>>> Some important links for excel users:
>>>> 1. Follow us on TWITTER for tips tricks and links :
>>>> http://twitter.com/exceldailytip
>>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>>
>>>> To post to this group, send email to excel-macros@googlegroups.com
>>>>
>>>> <><><><><><><><><><><><><><><><><><><><><><>
>>>> Like our page on facebook , Just follow below link
>>>> http://www.facebook.com/discussexcel
>>>>
>>>
>>>
>>>
>>> --
>>> *Regards*
>>> * *
>>> *Ashish Koul*
>>> *akoul*.*blogspot*.com <http://akoul.blogspot.com/>
>>> *akoul*.wordpress.com <http://akoul.wordpress.com/>
>>> My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830>
>>>
>>>
>>> P Before printing, think about the environment.
>>>
>>>
>>> --
>>>
>>> --
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><>&l

Re: $$Excel-Macros$$ File Consolidation and Data SummarizationFormula Help Needed

[John A. Smith]

Ashish,

I have attached the My Questions tab as a file by itself.  Thank you for
your kind help.

John

On Tue, Apr 5, 2011 at 12:55 PM,  wrote:

> Ashish,
>
> There are 22 employees. I don't need any blanks if no data for an employee.
> If their name isn't on the list they were off. If their name is on the list
> but no orders pulled, they were assigned to a utility job and don't need to
> be on the summary either.
>
> As soon as I can get to my computer I will send the file with the "My
> Questions" tab.
>
> Thank you.
>
> John
>
> Sent from my Verizon Wireless BlackBerry
> --
> *From: *ashish koul 
> *Sender: *excel-macros@googlegroups.com
> *Date: *Tue, 5 Apr 2011 22:06:17 +0530
> *To: *
> *ReplyTo: *excel-macros@googlegroups.com
> *Subject: *Re: $$Excel-Macros$$ File Consolidation and Data Summarization
> Formula Help Needed
>
> i think my question tab is missing
>
> do you want to show the blank records if the data of the employ is missing
> in day
>
> also how many total employees you have
>
> On Tue, Apr 5, 2011 at 2:03 AM, John A. Smith wrote:
>
>> I receive an Excel file download everyday (see > 032911.xls>,  and > 040311.xls>.  I need to summarize them into one database and be able to
>> report specific trends and situations.  Please see the attached file
>> .  It has three tabs:  Data Summary
>> (as it would look if all reports were combined), My Questions (tab) is where
>> I need specific help.  I need a macro to consolidate the files and
>> formula help to extract the data.
>>
>> Thank you for your continuing help teaching the power of Excel.
>>
>> John
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> *Regards*
> * *
> *Ashish Koul*
> *akoul*.*blogspot*.com <http://akoul.blogspot.com/>
> *akoul*.wordpress.com <http://akoul.wordpress.com/>
> My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830>
>
>
> P Before printing, think about the environment.
>
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel


My Questions Tab.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Re: Calculating An Average Score from Multiple Tabs

[John A. Smith]

Thank you GoldenLance.

John

On Tue, Mar 29, 2011 at 3:00 PM, GoldenLance  wrote:

> Try this
>
> =IF(ISERROR(AVERAGE('Dept A'!B10,'Dept B'!B10,'Dept C'!
> B10)),"",AVERAGE('Dept A'!B10,'Dept B'!B10,'Dept C'!B10))
>
> On Mar 29, 7:52 pm, "John A. Smith"  wrote:
> > I have like tabs from different departments and need to summarize an
> average
> > score by day.  But if there were no scores in a particular day, I get
> > #DIV/0!.  Please help with the formula that eliminates it.  I tries an
> > IFERROR in front of the AVERAGE but got a blank cell.
> >
> > Thank you.
> >
> > John
> >
> >  Calculating An Average from Multiple Tabs Question.xls
> > 42KViewDownload
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Calculating An Average Score from Multiple Tabs

[John A. Smith]

Thank you very much.  It works great.  One other question please; I have a
column with all formula's and I am looking for the smallest number in it by
using =MIN(range).  I get 0 (zero) because the formula is
=IF(DY9="GONE","",D9).  DY9 is an automatic status column that shows "GONE"
if a ship date is filled out in a previous column.  D9 is the value of the
shipment yet to ship.  =MAX(range) works but it senses the zero if it is
gone so the =MIN(range) doesn't.

Thank you for your kind teaching help.

John

On Tue, Mar 29, 2011 at 4:17 PM, STDEV(i)  wrote:

> Please try, and check if it helps
>
>  =IF(COUNT('Dept A:Dept C'!B3)=0,"",AVERAGE('Dept A:Dept C'!B3))
>
> learn 3D Reference more at:
>
> http://office.microsoft.com/en-us/excel-help/create-a-3-d-reference-to-the-same-cell-range-on-multiple-worksheets-HP010102346.aspx
>
>
>
> On Tue, Mar 29, 2011 at 9:52 PM, John A. Smith wrote:
>
>> I have like tabs from different departments and need to summarize an
>> average score by day.  But if there were no scores in a particular day, I
>> get #DIV/0!.  Please help with the formula that eliminates it.  I tries an
>> IFERROR in front of the AVERAGE but got a blank cell.
>>
>> Thank you.
>>
>> John
>>
>>
>   --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

$$Excel-Macros$$ Calculating An Average Score from Multiple Tabs

[John A. Smith]

I have like tabs from different departments and need to summarize an average
score by day.  But if there were no scores in a particular day, I get
#DIV/0!.  Please help with the formula that eliminates it.  I tries an
IFERROR in front of the AVERAGE but got a blank cell.

Thank you.

John

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel


Calculating An Average from Multiple Tabs Question.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Pivot Table Help

[John A. Smith]

Thank you Ashish and Viswanathan very much.  Problem solved.

John



On Sun, Mar 27, 2011 at 2:28 AM, ashish koul  wrote:

> check the attachment see if it helps
>
>
> On Sun, Mar 27, 2011 at 11:18 AM, ashish koul wrote:
>
>>
>>
>> On Sun, Mar 27, 2011 at 5:51 AM, Viswanathan M  wrote:
>>
>>> Dear Mr. John,
>>> =SUM(NOT(B2=""),NOT(D2=""),NOT(F2=""))
>>> The above formula will help you for finding out the Total Deliveries
>>>  *With warm regards*
>>> *Viswanathan M*
>>>   DE(Tech)
>>>   RGM TTC Chennai-600027
>>>  *Vande* *Mataram*
>>>
>>>
>>> On Sat, Mar 26, 2011 at 9:57 PM, John A. Smith 
>>> wrote:
>>>
>>>> I have an excel database where each line represents an entity and it's
>>>> data over about 70 columns.  There are multiple occurrences of delivery
>>>> information in these columns that I need to summarize in a pivot table.
>>>> Some lines may have only one occurrence of this delivery while some others
>>>> may have three or four.
>>>>
>>>> Please see my example attached.  Thank you for your continuing help.
>>>>
>>>> John
>>>>
>>>> --
>>>>
>>>> --
>>>> Some important links for excel users:
>>>> 1. Follow us on TWITTER for tips tricks and links :
>>>> http://twitter.com/exceldailytip
>>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>>
>>>> To post to this group, send email to excel-macros@googlegroups.com
>>>>
>>>> <><><><><><><><><><><><><><><><><><><><><><>
>>>> Like our page on facebook , Just follow below link
>>>> http://www.facebook.com/discussexcel
>>>>
>>>
>>>
>>>
>>> --
>>> *With warm regards*
>>> *Viswanathan M*
>>>   DE(Tech)
>>>   RGM TTC Chennai-600027
>>>  *Vande* *Mataram*
>>>
>>>  --
>>>
>>> --
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><><><><>
>>> Like our page on facebook , Just follow below link
>>> http://www.facebook.com/discussexcel
>>>
>>
>>
>>
>> --
>> *Regards*
>> * *
>> *Ashish Koul*
>> *akoul*.*blogspot*.com <http://akoul.blogspot.com/>
>> *akoul*.wordpress.com <http://akoul.wordpress.com/>
>> My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830>
>>
>>
>> P Before printing, think about the environment.
>>
>>
>>
>
>
> --
> *Regards*
> * *
> *Ashish Koul*
> *akoul*.*blogspot*.com <http://akoul.blogspot.com/>
> *akoul*.wordpress.com <http://akoul.wordpress.com/>
> My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830>
>
>
> P Before printing, think about the environment.
>
>
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

$$Excel-Macros$$ Pivot Table Help

[John A. Smith]

I have an excel database where each line represents an entity and it's data
over about 70 columns.  There are multiple occurrences of delivery
information in these columns that I need to summarize in a pivot table.
Some lines may have only one occurrence of this delivery while some others
may have three or four.

Please see my example attached.  Thank you for your continuing help.

John

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel


Pivot Table Help.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Create a Chart with Percents and Data Showing

[John A. Smith]

So I am guessing since I didn't get a single response from this posting that
it can't be done?

John

On Tue, Mar 1, 2011 at 2:41 PM, John A. Smith wrote:

> Hello,
>
> I need to create a chart that is based on the percent of calls closed.  But
> I also need to know if the 100% is one out of one or ten out of ten.  So I
> would like the numbers it took to make the percent to be at the bottom in a
> data table.  I can get it one way or the other and need some help getting
> what I need.
>
> I have attached an example.  Thank you for your continuing help in learning
> excel.
>
> John
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

$$Excel-Macros$$ Create a Chart with Percents and Data Showing

[John A. Smith]

Hello,

I need to create a chart that is based on the percent of calls closed.  But
I also need to know if the 100% is one out of one or ten out of ten.  So I
would like the numbers it took to make the percent to be at the bottom in a
data table.  I can get it one way or the other and need some help getting
what I need.

I have attached an example.  Thank you for your continuing help in learning
excel.

John

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel


Chart Needed Example.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Conditional Format Duplicate Rows

[John A. Smith]

This is excellent.  Thank you for your help.

John

On Thu, Feb 24, 2011 at 11:07 PM, Rohan Young  wrote:

> Hi,
>
> see the attachment if it help
>
> thanks & regds,
>
> ROHAN
>
>
> On Thu, Feb 24, 2011 at 8:05 PM, John A. Smith wrote:
>
>> Your kind help please.
>>
>> I have a database that I need to see duplicated rows but can't filter
>> because some columns can legitimately repeat.  If the entire row is a
>> duplicate, I need to know.
>>
>> Please see attached example.  Thanking you in advance for your continuing
>> help in learning Excel.
>>
>> John
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>  --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

$$Excel-Macros$$ Conditional Format Duplicate Rows

[John A. Smith]

Your kind help please.

I have a database that I need to see duplicated rows but can't filter
because some columns can legitimately repeat.  If the entire row is a
duplicate, I need to know.

Please see attached example.  Thanking you in advance for your continuing
help in learning Excel.

John

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel


Highlight Duplicated Rows Example.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Email Macro Based on an Address in a specific Cell

[John A. Smith]

Ashish,

Thank you for your quick response.  I got it to work thank you.  Could you
explain ("b" & I).Text  please as it refers to a location?  Thank you for
your excellent assistance learning Excel.

John



On Wed, Jan 26, 2011 at 7:40 AM, ashish koul  wrote:

> try this see if it helps
>
>
> With OutMail
> .to = ActiveSheet.Range("b" & I).Text
> .CC = ActiveSheet.Range("c" & I).Text
>
>
>
> On Wed, Jan 26, 2011 at 1:51 AM, John A. Smith wrote:
>
>> I use a file with a macro (which this wonderful group collectively
>> contributed to) which when I highlight a range and hit ctrl + s it drops it
>> into a new excel file, opens outlook and addresses it to the addresses in
>> the macro.
>>
>> I would like to let the user put an email address in a cell and when they
>> hit ctrl + s it does the same but goes to that specific email address.  I am
>> hoping to be able to go into the current macro and change the to="" into the
>> command to get the contents of the specific address cell.  Below is the
>> portion of the marco that needs to change:
>>
>> With Dest
>> .SaveAs TempFilePath & TempFileName & FileExtStr,
>> FileFormat:=FileFormatNum
>> On Error Resume Next
>> With OutMail
>> .to = ""
>> .CC = ""
>>
>> Thank you for your continueing help.
>>
>> John
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> *Regards*
> * *
> *Ashish Koul*
> *akoul*.*blogspot*.com <http://akoul.blogspot.com/>
> *akoul*.wordpress.com <http://akoul.wordpress.com/>
> My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830>
>
>
> P Before printing, think about the environment.
>
>
>  --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

$$Excel-Macros$$ Email Macro Based on an Address in a specific Cell

[John A. Smith]

I use a file with a macro (which this wonderful group collectively
contributed to) which when I highlight a range and hit ctrl + s it drops it
into a new excel file, opens outlook and addresses it to the addresses in
the macro.

I would like to let the user put an email address in a cell and when they
hit ctrl + s it does the same but goes to that specific email address.  I am
hoping to be able to go into the current macro and change the to="" into the
command to get the contents of the specific address cell.  Below is the
portion of the marco that needs to change:

With Dest
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.to = ""
.CC = ""

Thank you for your continueing help.

John

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Formula

[John A. Smith]

Dave,

I opened it in Excel 2007 and it worked fine.  One question please; in
=LOOKUP(2,FIND(A1,Sheet2!A:A),Sheet2!B:B) , What exactly does the "2" do?

Thank you for your excel help.

John

On Sat, Jan 15, 2011 at 2:16 AM, Dave Bonallack
wrote:

>  Hi John,
> I opened your file, but don't get a #NUM error. I get '98' as expected. I
> tried it in XL 2007 and 2003.
> Regards - Dave.
>
> --
> Date: Fri, 14 Jan 2011 14:28:41 -0500
> Subject: Re: $$Excel-Macros$$ Formula
> From: johnasmit...@gmail.com
> To: excel-macros@googlegroups.com
>
>
> Dave,
>
> Why am I getting #NUM! with the formula?  (See Attached)
>
> Thank you.
>
> John
>
> On Fri, Jan 14, 2011 at 10:35 AM, Dave Bonallack <
> davebonall...@hotmail.com> wrote:
>
> Hi,
> I would like to submit the following formula as one I like.
> It performs a case-sensitive Vlookup, and is non-array.
>
> *=LOOKUP(2,FIND(A1,Sheet2!A:A),Sheet2!B:B)*
>
> A1 contains the lookup value
> Sheet2!A:A is the lookup column
> Sheet2!B:B is the return column
>
> I hasten to add that this formula is not my own, but is a very neat
> solution to a common problem.
>
> Regards - Dave.
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
>
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Formula

[John A. Smith]

Dave,

Why am I getting #NUM! with the formula?  (See Attached)

Thank you.

John

On Fri, Jan 14, 2011 at 10:35 AM, Dave Bonallack
wrote:

> Hi,
> I would like to submit the following formula as one I like.
> It performs a case-sensitive Vlookup, and is non-array.
>
> *=LOOKUP(2,FIND(A1,Sheet2!A:A),Sheet2!B:B)*
>
> A1 contains the lookup value
> Sheet2!A:A is the lookup column
> Sheet2!B:B is the return column
>
> I hasten to add that this formula is not my own, but is a very neat
> solution to a common problem.
>
> Regards - Dave.
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel


Daves Lookup Formula.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Formula

[John A. Smith]

Dave,

Why am I getting #NUM! with the formula?  (See Attached)

Thank you.

John

On Fri, Jan 14, 2011 at 10:35 AM, Dave Bonallack
wrote:

> Hi,
> I would like to submit the following formula as one I like.
> It performs a case-sensitive Vlookup, and is non-array.
>
> *=LOOKUP(2,FIND(A1,Sheet2!A:A),Sheet2!B:B)*
>
> A1 contains the lookup value
> Sheet2!A:A is the lookup column
> Sheet2!B:B is the return column
>
> I hasten to add that this formula is not my own, but is a very neat
> solution to a common problem.
>
> Regards - Dave.
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel


Daves Lookup Formula.xls
Description: MS-Excel spreadsheet

$$Excel-Macros$$ Can the filter criteria come from the value of a cell?

[John A. Smith]

Is there a way to change a filtered view by changing the value of a cell
rather than clicking the drop-down arrow and scrolling to the data you want
to filter it to?

Or more specific, see attached for a formula to look up all instances of a
specific variable.

Thank you.

John

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel


Look Up All Instances of a Variable.xls
Description: MS-Excel spreadsheet