# Posts by lyambor

• ## Nested IF Mutiple Logical Tests

Re: Nested IF Mutiple Logical Tests

Thank you!
Yes - I thought of vlookup - that was plan B
But wanted to try the nested formula since I usually have issues with parenthesis

I appreciate you taking the time
Cheers
Leslie

• ## Nested IF Mutiple Logical Tests

I have read through a few threads to match some IF Formulas to my challenge and thought I came pretty close
I'm hoping it has to do with a , or ) misplaced since the error message is "too many arguments" and I am using 2013.

Basically - based on cell A43 where employees calculated score is they are :
[TABLE="width: 419"]

[tr]

[TD="colspan: 6"]Highly Effective: 3.5 - 4.0
Effective: 2.5 - 3.4
Needs Improvement: 1.5 - 2.4
Ineffective: less than 1.5[/TD]

[/tr]

[/TABLE]

My Formula try
=IF(AND(A43>=3.5,A43<=4.0),"Highly Effective",IF(AND(A43>=2.5,A43<=3.4),"Effective"),IF(AND(A43>=1.5,A43<=2.4),"Needs Improvement"),IF( (A43<1.5,”INEFFECTIVE” ,"N/A")))))

What am I doing wrong?
Thank you

• ## Parse URL

Re: Parse URL

Thank you so much
Have a wonderful day

• ## Parse URL

Re: Parse URL

Thank you so much
Worked perfectly

Quick follow-up question
what does the ~ do in the first formula?

Thank you
Leslie

• ## Parse URL

I have a list of very long URLs which vary in length
I need to parse the URL to 2 other columns breaking up the URL to its Path and (last occurrence of /)File name = 3 column URL/Path/FileName
I have been playing with Right/Mid/Len Find but seem to miss the right combination

I have attached a sample

Thank you
Leslie

• ## Pivot table date counts

Re: Pivot table date counts

Many thanks -

Makes perfect sense - I must have been over complicating it
Got it
Appreciate your time

-

Quote from iwrk4dedpr;708385

Your file you attached does not have the source data for the pivot table.

Add a new column to the data table .... and add it to the pivot table

• ## Pivot table date counts

Re: Pivot table date counts

The names might change every day depending on the date of the filter
can you give me an example of the countif formula I use it all the time but here I don't want a specific student if it is changing every day and the cell location might also change depending on the number of students and dates associated with each student

thank you

• ## Pivot table date counts

Working with a pivot table displaying a list of students and their tardy dates from the beginning of the semester
each date is displayed and a total of tardies for each student
Administration sometimes wants to filter only on a specific date so obviously the count changes

Principal wants to display for each student their totals for the semester as well as filter on a specific date - not losing the totals column

I know I can create a formula but if student list changes not sure if that is a solution

Any thoughts

Thank you

• ## Pivot table updating to linked workbooks

Re: Pivot table updating to linked workbooks

Afraid of that
Thank you for your time
I am familiar with PowerPivot and use it

However these inventory files are in a SharePoint Library and our organization has not moved to supporting PowerPivot for SharePoint servers yet

• ## Pivot table updating to linked workbooks

Re: Pivot table updating to linked workbooks

Ok I will try these files

Bottom line - Pivots don't update on refresh when source data range is correct and the items are viewed in the row but do not display in pivot for correct calculation

Thank you

• ## Pivot table updating to linked workbooks

Re: Pivot table updating to linked workbooks

Not sure if files can help-too big to send and smaller versions breaks all the links

Master inventory -links to all school files (Thousands of rows some blank to add new items - not entirely blank as to break the source data range)
Monthly inventory - is a Base school linking to multiple schools that report to it
(These sheets are thousands of rows)

Pivot tables are reporting out the multiple school inventory totals

Problem
source data does not change -- range is correct for pivot tables
contents of the added inventory items display in the monthly sheet correctly

Excel simply does not refresh the contents of these cells for calculations in the pivot table
I understand the issue with pivots and adding/deleting rows/columns
but if the pivot table is pointing to the right cells why not see the correct contents for calculation on refresh? or edit links update values?

I tried to send files but links broke or file is too big

Thank you

• ## Pivot table updating to linked workbooks

I have created a manual inventory system (Dept won't buy real inventory system) in excel.

I have monthly sheets linked to the inventory items workbook. When new items are added (always) the monthly sheets display the new items.

the pivot tables have been setup for each month.

I would think that refresh updates all the links. I have even gone to Data > edit links and updated values.

Problem -- the links are updated on the monthly sheets for all new items/values Perfect
but the pivot table does not update the items/values. on refresh or edit links to these new items
I have to re-create the pivot table for the items/values to be current.

Since I have over 127 sites - the idea of re-creating the pivot tables each month for each site is impossible

Any ideas?

Thank you

• ## Convert SUMIF to SUMProduct

I am building a summary report from files on a SharePoint Library.
SUMIF does not work unless the linking files are open which I can't do.
I searched and found out that SUMProduct is the function to use.

I am having trouble converting my SUMIF formula to fit the SUMPRODUCT argument

This is the model example I found online:

=SUMIF('Z:\WUT\Daily Report\[2010 Monthly Rpt - CPM - Thru November only.xls]2010 Expense'!\$E\$231:\$E\$332,"M&R Parts D",'Z:\WUT\Daily Report\[2010 Monthly Rpt - CPM - Thru November only.xls]2010 Expense'!AE231:AE332)

To

=SUMPRODUCT(('Z:\WUT\Daily Report\[2010 Monthly Rpt - CPM - Thru November only.xls]2010 Expense'!\$E\$231:\$E\$332="M&R Parts D")+0,'Z:\WUT\Daily Report\[2010 Monthly Rpt - CPM - Thru November only.xls]2010 Expense'!AE231:AE332)

This is my formula:

=SUMIF('https://intranet.aacps.org/private/FoodServices/Inventory/Schools/[Annapolis Middle.xlsx]August'!\$S\$7:\$S\$638,"Frozen/Refrigerator",'https://intranet.aacps.org/private/FoodServices/Inventory/Schools/[Annapolis Middle.xlsx]August'!\$R\$8:\$R\$638)

My SUMPRODUCT attempt:

=SUMPRODUCT(('https://intranet.aacps.org/private/FoodServices/Inventory/Schools/[Annapolis Middle.xlsx]August'!\$S\$7:\$S\$638="Canned and Dry Goods")+0,'https://intranet.aacps.org/private/FoodServices/Inventory/Schools/[Annapolis Middle.xlsx]August'!\$R\$8:\$R\$638)

Any Thoughts. I have attached a sample of my report each school has a separate file in sharepoint on our network - I would be linking to. Thank you

• ## Date fill series

Re: Date fill series

I thought as much
I did a similar formula but yours is much better!
Many thanks for your time
Cheers
Leslie

• ## Date fill series

Greetings-

I am well versed in the auto fill for dates and standard fill series such as weekdays etc

But what I am trying to do seems like an easy request but I cant get it to work

I am trying to fill a series of dates for only tuesdays and thurdays
I can do tuesdays only by a series step of 7 or thursday with a series step
but not both

Such as:
[TABLE="width: 195"]

[tr]

[td]

2/05/13

[/td]

[td]

Tue

[/td]

[/tr]

[tr]

[td]

2/07/13

[/td]

[td]

Thu

[/td]

[/tr]

[tr]

[td]

2/12/13

[/td]

[td]

Tues

[/td]

[/tr]

[tr]

[td]

2/14/13

[/td]

[td]

Thu

[/td]

[/tr]

[/TABLE]

I thought if I selected the cells that had the date sequence it would follow it so only give me dates for tue / thursday/tuesday, thurday
but it doesent and any combination of series steps and stop values does not seem to work

what amI missing?

Im sure it can be done in VB but this is for a user not me

Many Thanks

• ## Word 2010 Mail Merge multiple Rows to 1 Label

Re: Word 2010 Mail Merge multiple Rows to 1 Label

______________

many Thanks - Im sure others have a similar need
My workaround I'm thinking Access or Crystal reports

• ## Hours Worked Sum Pivot

Re: Hours Worked Sum Pivot

Quote from Herbds7;629866

Subtotal(9/109,xxx) includes/ignores hidden values.

Never knew that thank you!
Leslie

• ## Hours Worked Sum Pivot

Re: Hours Worked Sum Pivot

Quick Question in the Subtotal formulas--
Why 109?

=SUBTOTAL(109,[Pay per Day])
=SUBTOTAL(109,[Hours Worked])

• ## Hours Worked Sum Pivot

Re: Hours Worked Sum Pivot

Thanks so much - got it
The right custom format makes all the difference
Much appreciated
Cheers
Leslie

Quote from Herbds7;629764

Excel 2010 Table, PivotTable
Custom Format
http://8936e6d9f85be098ae08-f2…rackcdn.com/09_24_12.xlsx
If you get *.zip, don't unzip, just rename *.xlsx

• ## Hours Worked Sum Pivot

Trying to help timekeeper to tally total hours worked by employee
When doing a pivot for sum total hours worked for FY13 it does not calculate correctly
I understand they formatted that column/custom h:mm
but when I change the 8:00 hours to a number I come up with 0.33

I am attaching a sample file
I just want them to get a running total of hours worked/pay

What am I doing wrong?

Thank you