ANmarS - Search results

Excel/VBA expert since Sep 1997 (21 years 2 months)

We are moving ...

We are moving to another location, better, faster and more resources .... check it out ANmar.Systems

5 Q & A posts tagged with pivot

Thu
Nov 17
2011

Refresh a read-only spreadsheet?

I have a large spreadsheet that I use for construction bids.

On bid day, my partner opens up a read-only copy so that he can follow along and help compare bid prices with what we are currently using.

Is there a way for him to refresh his read-only copy of the excel worksheet without having to save the original and then close and re-open the read-only copy?


Answer

When you say "Refresh" you mean "Calculate", correct?
If so, then F9 will do an entire calculation for all opened workbooks

If by "Refresh" you mean "PivotTable" refresh, then each PivotTable has its own "Refresh" button

Otherwise, please advise on what "Refresh" means

Thursday 11/17/2011 11:14:16 PM
Thu
Apr 9
2009

I want to know the Ms-excel formulas. to use excel.?

I want to know different formulas in excel like vlookup,hlookup,pivot table,conditioning formats & links ect.,


Answer

There are a lot of websites that can help you
try these

http://MrExcel.com
http://www.File1.net

VBAXLMan

Thursday 4/9/2009 12:16:34 AM
Fri
Apr 3
2009

CountIF, DCount or DcountA, please help?

Hello
How are you? Hope you are fine & will receive this message at your best.

I have a problem & I need your help.
I have created a work sheet in MS excel where I have entered all our transactions during the last year processing by our different sales representatives in different cities of country.
Column D contains CITY which may b like Lahore, Karachi or Islamabad where they have sold the items, & column I contains sales representative name like Ali, Asad, Waqas etc who have sold the items.
I need to do a count of all the transations where the city is "Islamabad" AND the Sales representative is "Ali"

Please help


Answer

You have these options:

1- If you are in Excel2007, you can use COUNTIFS, which will count based on more than one condition (up to 30)
2- If you have Excel2003, you can do DCOUNTA, since the cells are having strings
3- You can also do DCOUNTA in Excel2007.
4- You can add a column having the criteria you want using simple IF, then count that column, something like this
=IF(AND( A1=T1, B1=U1),1,0)
Assuming
A1 has the city name and the list goes down to whatever
B1 has the sales man and it goes down also
T1 has the city you want to search for
U1 has the sales man you want to search for
Then do a SUM for that column to have the count
5- You still have another option, which is PivotTable, but I don't recommend using that powerful tool for something simple like this

let me know

VBAXLMan

Friday 4/3/2009 12:45:16 AM
Wed
Jan 21
2009

Multiple pivot tables controlled by a single set of page fields to automatically update all tables?

Hi,

Is it possible in Excel to update multiple pivot tables, all using the same page fields, by changing the page fields just once instead of having to change them for each table?

Thanks


Answer

Sure
When you build the pivot tables, make them reading the values from that source table

Once you update your fields, you may need to refresh each pivot table, or use the macro to make updation for all you pivot tables like this

ActiveSheet.PivotTables( "PivotTable1").PivotCache.Refresh

All you need to add is a loop through all your sheets with pivot tables and refresh each one

For i=1 to ThisWorkbook.Worksheets.Count
Worksheets( i).PivotTables( "PivotTable1").PivotCache.Refresh
Next i

Also you may need to make sure that each Pivot is named "PivotTable1" in each sheet

VBAXLMan is back to feed your Excel needs

Wednesday 1/21/2009 5:28:24 AM
Fri
Jan 16
2009

Excel, Update sheets automatically?

Hi,

I have this sheet which i am currently working on - what i would like to do is every time a new record is added to Sheet1, it automatically updates Sheet2.

Sheet1 has 6 categories but every record is just added to the list. Sheet2 has records by categories. It has to be able to find the correct category in Sheet2 and update it with the new record.

Thanks.


Answer

This can not be done in Excel formulas
You will need to do one of the following:
1- Do a Pivot Table in Sheet2 to get the result of each category in certain columns (Might need to do more than pivot table)
2- Do a macro that is refreshed every time you change a cell in sheet1

I can not show you how to do the PivotTable here since it is different between Excel2007 and Excel2003

Also the macro thing cannot be done here, since I need more details from you, but it can be something like this:

- In Excel, Press ALT+F11
- Double click the sheet name (Sheet1 in your case) in the left tree list
- Paste this

Private Sub Worksheet_Change(ByVal Target As Range)
' Macro should be here
End Sub

The macro you need to put here is the macro that I will do it for you for free, if you send me your file to
XL@VBAXLMan.com
with some additional details

VBAXLMan

Friday 1/16/2009 8:13:22 AM

Tags

History