Over 1 million tech questions and answers.

Solved: Pivot multiple sheets for text, date and decimal fields

Q: Solved: Pivot multiple sheets for text, date and decimal fields

EXCEL SOS!!! I have an urgent deadline which I'm likely to fail if I can't get my head around this issue so please please feel free to comment / offer suggestions...I'll try anything!

I have around 10 - 15 sheets within the same excle file which is effectively acting as one tab per resource within my consultancy team. These are acting a a record of the billable & non billable hours per resource and therefore each line is primarily driven by date (2011 - 2013), with 1:N ratio items of the following: Client, Contract, Role, Days & hrs (effectively 1 line per date, resource, client & contract combination + time billed within the std 8 hr working day).

I have no problems creating individual pivot tables in the individual resource name's relevant tab, however the next step is to roll this information togather to show a holistic view for all resources, all clients etc (in what I anticipated to be a pivot table via the consolidation of multiple ranges function) for all resources by exactly the same fields:

Year, Date, Contract/Type, Resource, & Date - at row level (with possible additional options of Month, Week, Day being added as a row label)

AND

CLIENT , Non Billable - at column level

Naturally there may well be some slicing and dicing of the info needed to understand the figures at different levels, ie per resource (days billed & rate), how many hours used per contract, how we've used time in a contract (by role), & whom (by resource) per client, and time range (Annually, Qtrly, H1/H2, Monthly etc).

I've followed numerous you tube videos on how to perform the consolidation from mutlpile data sources (ie the multiple tabs) to try and understand what I'm doing wrong, from both the raw data lists and the individual pivots that are created correctly at one tab/resource level, but I just cannot get the data to display correctly (grr!). There are 3 things I've noticed that suspect may be causing the problem:

1) I am limited to 4 page fields and although I've researched into what these are exactly I only get vague explanations (East / West tab) if any, and no real context to help understand how that could possibly translate to my situation. I've tried using Client, Contract, Resource, Day as page fields but this doesn't bring any values back in the final pivot...but when I set page field 1 as the resource name (ie as per the tab structure) and move the first column of each sheet to contain the column with the resource name, it at least shows the underlying values of the resource/tab names but nothing else.

2) All the examples on you tube are all purely figures and therefore SUM correctly. My data is a combination of text, date and figures I'd like to sum. As I only ever get COLUMN, ROW, and Page Fields 1 - 4 (dependant on how many I've specified) I cannot break either the rows or the columnal underlying data out...

3) I can't seem to get the row level structure that I can get in the individual sheet pivot tables...No idea why!

I apologies for the long explanation but as I'm sure you can appreciate, as this is relatively confidential data I'm not in a position to provide the file easily, so I've sent a screenshot or two that hopefully will outline the pivot I have successfully acheived along with what I'm seeing on the consolidated pivot view, in addition to the above explanation..

Any help you can provide would be VERY VERY much appreciated!

RELEVANCY SCORE 200
Preferred Solution: Solved: Pivot multiple sheets for text, date and decimal fields

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Solved: Pivot multiple sheets for text, date and decimal fields

Hi there, welcome to the board!

I feel your frustration. Unfortunately your data structure isn't conducive for a PivotTable data model. But there is hope! You have some options here. First of all we need to know what version you're using. If you have 2010, well, you're going to really like what I'm about to tell you, and if you don't have it, it's a good reason to upgrade! 2010 has a new add-in called PowerPivot. Think of it as PivotTable's on bulk steroids. The benefit you would see - the ability to select multiple data sources. This means you can have multiple data tables/sheets as your data source, and select their relationships. Think of it like an Access table/relationship. It's the best feature we've seen in Excel since the PivotTable itself.

So if you have 2010, this is good news. However, if you don't have 2010, we need to consolidate your data to a single data structure. Whether that is putting it all on one sheet, or exporting to Access, or whatever, that's what needs to happen.

I'm not entirely sure what you mean about not getting the row level structure. Is there any way you can post the file?

Read other 2 answers
RELEVANCY SCORE 100

Hello VBA experts. Need your help. I have a workbook with 14 worksheets. Each worksheet has several fields all named the same. I need to sort 3 fields in each worksheet. Those are ascending in this order; Patient ID (in column A), DOS (in column E) and Code (column B).
Does anyone know of a code that can easily sort all 14 tabs at once? The range of data in each worksheet are different. Some worksheets have very little data to sort while other worksheets have maybe a 2,000 rows of data.
Any help is greatly appreciated.
 

A:Solved: Excel 2010 - Sort multiple fields in multiple sheets

Hi

Here is a macro that will sort all spreadsheets each with 3 levels of sort, all ascending assuming that the number of rows in all columns is the same.

Give this a try on a copy of your file (can't stress this enough) and let us know what it didn't do right
Code:
[SIZE=1]Sub SortSheets()[/SIZE]
[SIZE=1][/SIZE][SIZE=1]'[/SIZE]
[SIZE=1][/SIZE][SIZE=1]' Macro1 Macro[/SIZE]
[SIZE=1][/SIZE][SIZE=1]'[/SIZE]
[SIZE=1][/SIZE][SIZE=1]For i = 1 To Sheets.Count[/SIZE]
[SIZE=1][/SIZE][SIZE=1] ActiveWorkbook.Worksheets(i).Sort.SortFields.Clear[/SIZE]
[SIZE=1][/SIZE][SIZE=1] ActiveWorkbook.Worksheets(i).Sort.SortFields.Add Key:=Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row) _[/SIZE]
[SIZE=1][/SIZE][SIZE=1] , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal[/SIZE]
[SIZE=1][/SIZE][SIZE=1] ActiveWorkbook.Worksheets(i).Sort.SortFields.Add Key:=Range("E2:E" & Range("A" & Rows.Count).End(xlUp).Row) _[/SIZE]
[SIZE=1][/SIZE][SIZE=1] , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal[/SIZE]
[SIZE=1][/SIZE][SIZE=1] ActiveWorkbook.Worksheets(i).Sort.SortFields.Add Key:=Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row) _[/SIZE]
[SIZE=1][/SIZE][SIZE=1] , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal[/SIZE]
[SIZE=1][/SIZE][SIZE=1] With ActiveWorkbook.Worksheets(i).Sort[/SIZE]
[SIZE=1][/SIZE][SIZE=... Read more

Read other 2 answers
RELEVANCY SCORE 91.6

Hi, All,

I am using Excel 2007 and have a table with 3 columns: StudentID, Test, and Score. StudentID is a number and Test and Score are text values -- score is sometimes a pass/fail and sometimes a number, so this is why this is text.

I use pivot tables all the time for numeric values. Is there a way to do this with text? What I am hoping to do is show StudentID as my row value, Test as my column value, and put the scores in the middle.

Thank you!
Lisa
 

A:Excel Pivot/Crosstab using text fields?

Do you mean something like this. Its an example so we can see what you want.

In the example I am counting the items rather than summing.
 

Read other 3 answers
RELEVANCY SCORE 88.4

I have a field that reads 2009-12-30-15.42.22.81298 as text. i want to make in be a date of 12/30/2009 15:42:23 and ignore the milliseconds. Is this possible?

Thanks
Ted
 

A:Converting complex date text fields into date or numbers

Ted, what software are you talking about?
Are there always 8 values in the date part?
 

Read other 3 answers
RELEVANCY SCORE 74.8

Hi,

Here is my problem. I'm making a countdown timer in Excel. The process is working fine, but my problem is when I call my Sub to set the time at which I want the countdown to end, I receive the following error:
-------------------
Run-time error '6':

Overflow
-------------------

Here is my code:

Dim TimerEnd as Date
Dim WorldPop as Integer 'This value is picked from my Excel worksheet

TimerEnd = Now() + ((1500 - (3 * WorldPop / 8)) / (60 * 60 * 24))

Note: WorldPop was tested and the value from the cell is really loaded because I was able to modify another cell value by assigning WorldPop value to it.
---------------------------------------------------------------------

Basically, I store in TimerEnd the time at which I want the countdown to reach 0 (obtained by doing TimerEnd - Now() )

The 1st part of the expression:
(1500 - (3 * WorldPop / 8)) determines in how many seconds the timer should end

The 2nd part of the expression:
/ (60 * 60 * 24) divides the 1st expression to obtain the seconds in decimal value which is added to Now() to get the end time.

What I don't understand is that when I type this expression in a cell of my worksheet that have been formated as date:

=NOW()+(1500-(3*850/8))/(60*60*24)
Note: 850 is just a random number which I tested the result. It can be any number ranging from 0 to 2000

It gives me a valid time in the cell.

What am I doing wrong so that in the code of my subroutine it gives me Overflow?

Is there ... Read more

A:Solved: Excel: Overflow error when converting decimal to date

I found the problem.

The following expression was too long:
TimerEnd = Now() + ((1500 - (3 * WorldPop / 8)) / (60 * 60 * 24))

I simplified it to:
TimerEnd = Now() + ((1500 - (3 * WorldPop / 8)) / 86400)

and it resolved my problem.
 

Read other 1 answers
RELEVANCY SCORE 73.6

So, I've been looking up the topic for quite a while and it's suddenly become critical to what I am doing. Everywhere I have looked, I see lots of ways to copy multiple sheets into one sheet. This doen't do me the least bit of good. I have two 70+ tab spreadsheets. One for actuals, one for forecast. I need to copy the actuals for the first 5 fiscal periods of the year into the Forecast file. As there are 70+ sheets, I would prefer not to do this one at a time. In know in the previous versions of Excel, I could simply highlight all the sheets, highlight the data I needed, hit copy, and paste the data in the new sheet. So long as the sheets were laid out the same, it was never a problem.

Do you have any idea how to do this now?

Thank you.
 

A:Excel 2010 copying multiple sheets into multiple sheets

Read other 7 answers
RELEVANCY SCORE 73.6

I have a text box [Text45] being used in a form as a general number with 2 decimals. I have another text box I am calculating into that is 1 decimal.

the formula I am using is =IIf(([Text45]*0.8)>=20,20,Int([Text45]*0.8)) because there is a 20 max on the calculation.

When 24.5 is put into text box 45 the new text box should say 19.6 but I cannot get it to calculate to the .6 it rounds down to 19 even.

This is extrememly frustrating. Anyone have a suggestion? There should be an easy way to lock in on decimal integrity.
 

A:Solved: Access 2000 Decimal Integrity in a Text Box

In your formula you are using Int() which is Whole number, so there is never any decimal values to display.
 

Read other 1 answers
RELEVANCY SCORE 70

I'm sure some of this problem is just my numbskull approach to how I've begun to develop and structure pivot tables in Excel 2000.

In pivot tables where you do (or don't) have page fields, but where you have a fairly small number of items that your want to variably select or filter, I know you can use the pulldown arrow and individually select the items that you want to include or exclude from your column or row results, using the checkboxes.

Is there any universal command that will allow you to comprehensively check or uncheck all the items under a row or column field, if say, you have 50 items, and you want to filter the results down to just one or two items? Again, you probably wouldn't have a situation like this with quite so many items being used in a data summary capacity, but I suppose it could happen and I just wanted to know if this capability was available in Excel.
 

A:Solved: Pivot Tables - Selecting and De-selecting Items in Column/Row Fields in Excel 2000

For Excel 2000, no. They added to the 2002 version an option to check or uncheck them all off.
 

Read other 3 answers
RELEVANCY SCORE 69.6

I have a pivot table in Excel 2007 with grouped data based on the year and the quarter, so the date info looks like this:

Code:
Years Date
2009 Qtr1
Qtr2
Qtr3
Qtr4
2010 Qtr1
Qtr2
Qtr3
Qtr4
2011 Qtr1
(I know it's not really code but I didn't know how else to maintain the columns)
I have a chart based on this data but I need the dates (on the horizontal axis) to appear in a very specific format of Q109, Q209 and so on. Is there a way I can either alter what's in the pivot table to achieve this, could I use this data combined with the TEXT function somewhere else on my worksheet, or is there another workaround?

My source data is by month only and I'm using the pivot table to calculate the quarterly averages for me.
 

A:Solved: Excel 2007 pivot table date format for chart

Read other 8 answers
RELEVANCY SCORE 68.8

I exported a list of invoices to excel from my accounting program. One of the fields, of course, is a date field mm/dd/yyyy.

I am trying to figure out how I can sort, pivot, something with the goal of totaling by month.

I've tried formatting the column to mm-yyyy and text-to-columns to isolate the month and it's just not working right.

Suggestions?

Thanks
 

A:Solved: Extracting or Focusing on the Month part of a Date for a Pivot Table? Excel

Read other 6 answers
RELEVANCY SCORE 68.4

Tech Support Guy System Info Utility version 1.0.0.1
OS Version: Microsoft Windows 7 Professional , Service Pack 1, 32 bit
Processor: Intel(R) Core(TM) i5 CPU M 520 @ 2.40GHz, x64 Family 6 Model 37 Stepping 5
Processor Count: 4
RAM: 2993 Mb
Graphics Card: Intel(R) HD Graphics, 1272 Mb
Hard Drives: C: Total - 234274 MB, Free - 193142 MB; E: Total - 2048 MB, Free - 2010 MB;
Motherboard: Hewlett-Packard, 1722, KBC Version 29.36, CND037022T
Antivirus: McAfee VirusScan Enterprise, Updated and Enabled

I would like to add a formula that would automatically calculate a date field from one cell and add three years. See example attached, Thanks in advanced
 

A:Solved: Calculating Date Fields in Excel

Read other 7 answers
RELEVANCY SCORE 66.8

I have an Access database where the data is being input in Access and displayed by Lotus Notes. Originally I had a number of Yes/No fields e.g. "Outside Storage" in the table. The person doing the Notes interface requested that I change these to Text fields which I did. The table values remained as Yes or No when I did this. The input forms kept their check boxes when I did this but as one enters data via the checkboxes the values are 0 and -1. If I delete the field from the form and add it again it comes as a text box which then displays Yes or -1, etc depending what is in the table.
The Notes person says data tables should not contain Yes/No fields or Combo Boxes like solid/liquid. My question is what is the best way to handle this assuming the Notes developer wants text fields in the table? Should I delete all the checkboxes from the forms and replace with text boxes showing Yes or No? Right now I have the validation of the table set to accept yes, no, 0 or -1. Finally, is the field on the form able to be changed back to a checkbox without changing the table field back to a Yes/No field? It seems as if the type of field placed on the form is inherited from the table.
 

A:Solved: MS Access Yes/No or Text Fields?

Read other 7 answers
RELEVANCY SCORE 66.4

Hi there,
I have an MS Access database (2000, but primarily operating on PC with Access 2010). The database logs wagons coming to and from our site. Mostly, these wagons are in and out in the same day, but it is possible for them to park up overnight, for which we charge a small fee.

The situation has now changed slightly, in that we now accept wagons staying on site for longer periods (usually a weekend), so I've been asked to introduce an Exit Date field as well, and correct the existing calcualtion of time on spent site.

Currently, the following fields appear in the main table (called Stays):

Arrival Date
Arrival Time
Exit Time

There is also a field called Duration, which (for some reason) is autofilled by an Update Query, with the Update To value set to:

IIF([Arrival Time]>[Exit Time],1440+DateDiff("n",[Arrival Time],[Exit Time]),DateDiff("n",[Arrival Time],[Exit Time]))​

This is clearly inaccurate - it assumes that if a wagon's exit time is earlier in the day than its arrival time, then it must have stayed for 1440 minutes + the (negative) difference between the two times, and if the exit time is later or equal to the arrival time, the wagons has been in and out of site in the same day.

As wagons could be on site for multiple days (so the 1440 mins figure could be wrong) and could leave x amount of days plus x amount of minutes after arriving (so the assumption that it's exited the same day as it arrived could be wrong),... Read more

A:Solved: MS Access modifying a date and time calc in separate fields

Read other 8 answers
RELEVANCY SCORE 66.4

I have this problem which occurred this past few days. I don't recall changing any character encoding settings.

Below are sample pictures of the unknown characters.

When you copy these characters and paste it another field, it will be normal english again.

A:[SOLVED] Unknown Characters in Some Text Fields

Hi,

Can you check your character settings to make sure that you are set to Times New Roman. Also are you getting re-directed to different sites if you go to google?

Cheers!

Read other 6 answers
RELEVANCY SCORE 66

Hi good day
i have a workbook consisting of several sheets. each sheet depicts data for several critera related to that specific sheet (the row fields are the same across each sheet) which is further broken down into categories specific for that area. in the summary sheet which also share the same rows i should be able to select a category (from a dropdown) which will give me a summary of those fields in each category.
i.e
the headings for the categories on each sheet would be 1 to 100. for sheet 1(town1) - area 1, area 2, .., area4, sheet 2(town2) - area 5, area6, area7, and so on.
fields making up the rows for those headings are e.g apples, pears, oranges, etc
so the sheet displays how many of each item was sold in a specific area.
areas might overlap in town sheets as a salesperson from town1 might have sold an item in an area belonging to a different town.
the sheets are named by town (town 1, town 2, etc)
the summary sheet - i wish to see how many apples, pears, oranges was sold in a specific area across all the towns.
if i select say 'area 2' in the area dropdown, it will search across the sheets and display the totals of each of the items sold for area 2.
i have used the formula 'sumproduct' which works well but it only works if i have all 108 areas on each sheet. this is not viable as it opens the data up to errors. i wish to know is there a way if the user select an area as a heading then populate the corresponding item with the amount sold. tha... Read more

A:Solved: count values across multiple sheets

Read other 16 answers
RELEVANCY SCORE 65.6

I have a worksheet with many pivot tables on it. I am trying to get all the pivot tables to change when I update a drop down list. Below is the code that I am using. I am missing a piece because nothing is happening. I put this code on the tab. My tab name is "Survey". The data field that is changing is "Brand" for all tables. At this point I do not care if I use a drop down list or have the user change to first table and have all the tables change. Any help on what I may be missing..... I am no expert with Excel....

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String
strField = "Brand"
On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
If Target.Address = Range("e2").Address Then

For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(strField)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
Next ws

End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
 

A:Solved: Multiple Many Pivot Table changes with change in Drop Down

For starters, after you run the routine you posted.
Go to one of the pivot tables, Record a new macro, name it PivotRefresh or whatever and refresh the data of the Pivot table, (Right Mouse button and refresh).
Stop the recorder

If you look at the code maybe you can use it so that after your macro has run, you write a routine to go through every sheet that has a pivot table or every pivto tabel in a sheet and have it run the code for each one.

I think this should do the trick and it's programmable.
Alternative, attach a sample so we / I can give it a shot.
 

Read other 1 answers
RELEVANCY SCORE 65.6

Version: IE 6(About dialog causes an error. Error 96, Unspecified error in the dialog)
Operating System: Windows XP SP3(2600.xpsp.080413-211)

Problem:
Internet Explorer 6:
About dialog causes an Unspecified error, Error number 96.
Unable to use and text fields or highlight text

Internet Explorer 7:
Refuses to even open.
When it did open, it would not load any webpages(just white where it should be) and about dialog crashed IE.


Troubleshooting:
First happened when I installed IE8 beta 2. I have since uninstalled it.

I have tried rolling back to IE6(see IE6 problems for what happens there) and reinstalling IE7. Neither seem to have worked multiple times.

With IE7 installed, other programs that use IE as the built in browser work fine until they want to open an actual IE window.
With IE6 installed, I have all the problems of highlighting text and using text fields .

A:[SOLVED] IE6 - Text fields broken. IE7- wont even load?!

I found out what was wrong. iexplore.exe wasnt being replaced, only the files were.. Found iexplore.exe in C:\windows\ie7 and relpaced c:\programs files\internet explorer's iexplore.exe with this one.. works now, was just an old ie8 file in the way.

Read other 2 answers
RELEVANCY SCORE 65.6

After removing several parasites and other junk from my win-2000pro-sp4 machine, Internet Explorer fails to focus on text fields of forms. I am NOT able to enter any text like e.g. user, password etc. this happens on ANY site which asks for text. Netscape works fine. I checked and rechecked security settings, scripts enable, java, etc
I did NOT reinstall IE6 because that is not so trivial, and off the point, I guess !!??

ALSO, in the TEMP folder I am NOT able to remove some stuff... folders like "history", "webshots temp" and a few others... !!?? Even tried in fail-safe, and escaping from current shell, to no avail, not even in command mode !
All with admin privileges of course.
On other identical machine/systems, I have NO problem cleaning the C:\WINNT\TEMP folder, anytime in normal mode.

PLEASE HELP !!
 

A:Solved: Internet Explorer 6 cannot enter text in fields

How to Repair or Return to Previous Internet Explorer Installation
If the Internet Explorer Repair Tool Verification does not work, or if Internet Explorer 5 is not listed in the Add/Remove Programs tool in Control Panel, you may be able to start the Internet Explorer Repair tool from a command prompt. To do so, click Start, click Run, and then in the Open box, type the following command, and then click OK:
rundll32 setupwbv.dll,IE6Maintenance "C:\Program Files\Internet Explorer\Setup\SETUP.EXE" /g "C:\WINDOWS\IE Uninstall Log.Txt"
NOTE: This command is case sensitive.
If this does not work, you might want to uninstall Windows Explorer and reinstall, the link has directions.

http://support.microsoft.com/default.aspx?scid=kb;en-us;293907
 

Read other 3 answers
RELEVANCY SCORE 65.6

Ok I have been struggling on this one all morning.

What I am tring to do is this.

I have a combo box, when the name is selected in the combo box, I would like it to auto populate the Addres/City/State/Zip of the persons name.
 

A:Solved: Access - Combo Boxes and Text Fields

Read other 16 answers
RELEVANCY SCORE 65.6

I'm making a simple SELECT query in Access 2007, but I need to only select those records where one of the fields is LIKE the text entered into a text field on a form. Hitting a button next to the text field executes the query.

The problem is that when I "build" the criteria in the query, it doesn't seem to want to work when I throw in the wildcards around the field name. For instance, if I wanted to search for records with a field like "simple" then I'd put stars around simple inside the quotes like this "*simple*". That works perfectly fine. But if I try to make that criteria dynamic based on the text entered into the text entry box, it doesn't want to work: "*[Forms]![SearchForm]![SearchText]*" . It only works if I take out the stars and quotes: [Forms]![SearchForm]![SearchText].

Any help? Maybe I'm just not using the correct syntax?

Note: I know how to get this to work in VBA, but I need it to work in pure SQL without the help of string concatenation.
 

A:Solved: Access 2007 - Referencing text fields in SQL... possible?

No worries, I found a work around that doesn't involve anything dealing with this question. Answer it if you wish, but the only reason it's still listed here is because I can't figure out how to delete it.
 

Read other 3 answers
RELEVANCY SCORE 65.2

Hello..

type
xyz
xyz
xyz
ccc
ccc
ccc
ccc
ccc
ccc
zzz
zzz
zzz
zzz

etc... but several thousand rows each with their own "type" ordered by type

how can i create a separate spreadsheet for each type...

dont know what this process is called...

surely there is a faster way than copying each type and pasting into a new xls doc...

(dont want to create tabs)

Thanks for any advice
 

A:Solved: how can you create multiple separate sheets from one sheet xls?

Read other 6 answers
RELEVANCY SCORE 65.2

How can i print multiple worksheets in an Excell 2007 workbook. Thanks in advance.
 

A:Solved: Print multiple sheets in Excel workbook

Hi there!

Yes, you can print multiple worksheets. Just select the ones you want by clicking on the tabs at the bottom of each worksheet that you'd like (holding down Ctrl will let you select more than one, or Shift will select all sheets in between the first and last one that you click on). If you go to your Print dialog box, it should have the 'Active Sheets' radio button selected in the 'Print what' section. This will print every worksheet you've selected. Alternatively, you could select 'Entire workbook' to have every worksheet in the file print out.

Hope that helps!
 

Read other 2 answers
RELEVANCY SCORE 65.2

I have an issue I have not been able to find an answer to, so I hope someone can help. I have a workbook, where there are many worksheets that i would like to consolidate into one sheet. The problem is I'm dealing with a lot of data. Each worksheet represents a day of the month, so there are 30-31 worksheets for each workbook.

Ultimately what i'm looking to acheive is to some how find a way to compile the date from all worksheets so i can do a quick search and pull from all the data from all the worksheets and see the results in one place. The search results would often find many results (hundreds even), and i still need to see all the data from those results from that row. I like the idea of using a pivot table, but once you use it for multiple sheets i cant get it to show the actual data.

I hope I have been clear with what i'm trying to do, if not, please let me know. Thanks for any help you can provide.
 

A:Solved: EXCEL - Consolidate data from multiple sheets

Read other 11 answers
RELEVANCY SCORE 65.2

Hello Everyone,
I would like to thank Bomb 21 for the help I received with the Excel spreadsheet earlier. I have 2 other dilemmas that I need help with. I am not experienced at all with VB. I searched on the net for a basic answer but all I could find were answers that did way more than I needed. Here it goes. (Please see the attached zipped spreadsheet)

I have a spreadsheet with a VB script that mostly runs as I would like on the first sheet. I would like this script to:

1.) I would like the script to calculate the results it gives me in column D as follows, count the number entries that range in price from $0-$199,999, $200,000-$249,999, $250,000-$349,999, $350,000-$499,999, $500,000-$749,999, $750,000-$999,999, $1000000+ and place those into their corresponding place holders in column G (for each section, Active, Pending, WIthdrawn, Sold etc.)

2.) Perform this script on all of the sheets in the Spreadsheet.
Thank you in advance for your help with this.
 

A:Solved: Excel Question: How to run same script in multiple sheets

Read other 16 answers
RELEVANCY SCORE 64.4

I have three sheets of data that I want to combine into one based off of criteria in column "A", however I only want the data that is duplicated across each sheet (ie if the three sheet have the name "Bob" in column A, I want the name "Bob" to be in the new list). Some of the sheets may contain duplicated data within itself (ie. the name "John" may be on three different rows in one sheet.) I only want to keep the names that show up on all three sheets, not just multiple times within one sheet.

Any suggestions would be great.
 

A:Solved: Condensing multiple sheets in Excel into one for data duplicated across all s

Read other 14 answers
RELEVANCY SCORE 64.4

I don't know and it may sounds strange but lately I encounter problems importing greek text in internet explorer's text fields.
For example when I import text in google's search bar in english I have no problem. But when I import a greek word (in Greek Google or English google) I see chinese (^&#@) characters. Therefore I cannot search anything in Greek.
I have the same problems filling forms in Greek.
I can write Greek in all other microsoft programs (word, excel, outlook express).
PLEASE HELP!
I forgot to mention I run IE6 on win2000
 

Read other answers
RELEVANCY SCORE 64

For instance, if I highlight a word in a text-entry field, it's a very bright blue. This is also true in Firefox, Thunderbird, Word, OneNote, various fora, etc. but not in Excel.

Prior to installing Windows 10, the highlight in a text-entry field was either pale grey (on dark backgrounds) or darker grey (on light backgrounds), which is what I'd like to have back.

Any ideas?

A:Would like to change the text highlight color in text-entry fields

Are you talking about doing this, but with gray and not red?

Open up Notepadcopy the registry settings in the code box(left click 3 times slowly)Paste into NotePadYou will need to find the RGB values(if the ones I have supplied are not what you want) for the color you want, and change them.Create a registry file by saving(to Desktop or Documents) as TextColor.regDouble click the reg file.Sign out, sign in and your finished.To change the values just right click the file and choose edit, make the changes, save, close and see point #6


Code:
Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Control Panel\Colors]
"Hilight"="192 192 192"
"MenuHilight"="192 192 192"
Light Gray RGB=192 192 192
Darker Gray RGB= 105 105 105

Here is the reg file in case you have problems doing it yourself, using light gray: GrayHighlights.zip

Just download. Right click open Properties. Check unblock. Click OK. Extract(unzip) to where ever you want. Double click the Icon. Sign out and back in. Highlight something

Read other 5 answers
RELEVANCY SCORE 63.2

G'day,

I have a Microsoft Excel 2003 file that contains two spread-sheets. In one, I have a list of clients and a column next to each client name that I want to display the amount of money made from that particular client. Then, on the second spread-sheet I have a list of all credit and debits relating to the various clients. So, we might have received $100.00 from a client (and that would be in the credit column next to the client name), and then we may have spent $50.00 on that client (and that would be in the debit column next to the client name). So, obviously, the total amount made from that client would reflect the credit minus the debit.

Anyway, what I need is for the cell on the first spread-sheet that says the total made for that client to look at the second spread-sheet, look for any row that has that client's name and then adds the credits and subtracts the debits, then leaving the total back on the first spread-sheet.

I hope that makes sense. I have attached a demonstration to help, and I have also done the formula for the first client to show you what outcome I am looking for (although the formula is not what I want because it doesn't automatically add all of the rows from the second spread-sheet that share that client name).

Anyway, I hope I've explained it well enough and if anyone can help me I'd really appreciate it!
 

A:Solved: Adding Selected Values Together Over Multiple Work-Sheets In Microsoft Excel

hi blujein,

Attached is a quick solution.

Copy the formula I have added to the Total Amounts spreadsheet in Col B, down in new rows as you add new clients.

lol
Hew
 

Read other 3 answers
RELEVANCY SCORE 63.2

Okay - I officially miss my brain - it's too hot here and my overheated brain goes into freeze mode.
I have a table that has residential area and location of contact. Both these are stored in the table as a code and in a related table called ResidentialAreas they are decoded.
For my query I need to display the clients residential Area and the location of contact (these may be different) and I want to display the full (decoded) name of the area. How do I do an Inner Join on the the table for the one field and then the other field?
THis is what I have in my query - it doesn't work
FROM (Lead INNER JOIN OPCStaff ON Lead.opcstaffid = OPCStaff.staffid INNER JOIN ResidentialAreas ON Lead.location = ResidentialAreas.RID) INNER JOIN ResidentialAreas on Lead.rarea = ResidentialAreas.RID
 

A:Solved: Multiple Inner joins to the same table for different fields - how and is it p

Read other 8 answers
RELEVANCY SCORE 63.2

Ok...I know there is a simple way to do this but can't come up with it.

I would like to check multiple Combo Boxes (40) to see if the same value has been entered. In the example below it is "ClassEquivalent". (The same code would work for a NULL value) If the value is True then I would like to display a msgbox. I know that there is more simple code than this...and I only want one Message Box to be displayed if any of the Combo Boxes contain that value.

If Me.ComboBox1.value = "ClassEquivalent" Then
MsgBox("Please Fill in Type of Equivalent",vbcritical,"ERROR") =vbok
Me.LabelComboBox1.forecolor = 255
Else
If Me.ComboBox2.value = "ClassEquivalent" Then
MsgBox("Please Fill in Type of Equivalent",vbcritical,"ERROR") =vbok
Me.LabelComboBox2.forecolor = 255
etc.....
 

A:Solved: MS Access: Check Multiple Fields for One Value

There is a set of videos here that show you how to unlock and lock a group of controls, from a basic method setting each control individually, right up to using a class module that monitors the command button and takes action. I think you could adapt this code to work for your 40 Controls. The videos about the class module method are the videos numbered 5 6 and 7.

Cheers Tony
 

Read other 2 answers
RELEVANCY SCORE 63.2

Hello all,
I have a db where I have a list of projects. The projects are separated into two tables with the first containing information about the client and a related table with information pertaining to the individual projects done for each client. This is a one to many relationship.

I am currently trying to establish a resume creator for my db where I would have an employee table which would be related to individual client's projects. My goal is to create a form that I can select what projects each employee worked on and give a report on all the information given in the client and project tables for the projects any particular employee worked on.

What I cannot figure out is how to relate the employee table. I can't do it to just the client table because the employee is not on every project the client has and I cannot relate it just to the project table because I need the client information in my report as well.

Any help would be greatly appreciated,
Madctch
 

A:Solved: Access: Bind to multiple fields

Read other 6 answers
RELEVANCY SCORE 62.8

Hi guys,

I'm trying to hide the drop down button on all pivot tables across all worksheets in a workbook.

Any ideas on how to expand the code below to assist with this? Thanks guys.

Sub Hide_Button()
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)
For Each pf In pt.PivotFields
pf.EnableItemSelection = False
Next
End Sub

Thanks,
Albert
 

A:Solved: Hide pivot table drop down on ALL pivot tables

Read other 9 answers
RELEVANCY SCORE 62.4

Hello,

Using mixture of Windows XP with Office 2003 and Windows 7 with Office 2013 (so the database is in Office 2003 format).

We have a database showing companies that we have spoken to in regards to working with us. Each company will have shown interest in covering several postcode areas.

I have created a "Yes / No" (tick box) field for each postcode area ("AB", "AL", etc). A total of 121 fields.
I have also created a combo box "PostcodeFilter" which works from a table with all the Postcodes in (matching the 121 fields)

Google has brought up some code that works "On Exit" from the combo box that filters the records...

Me.Filter = "EquipmentID = '" & Me.ComboName & "'"
Me.FilterOn = True

This would look at a single field (in the example "EquipmentID") and filter where this equals what has been selected in the combo box.

What I need is that it would look at whichever field is named the same as selected in the combo box and filter where that field = Yes (ticked)

Example: Select "AB" in the combo box and it will filter the records to only show when the field "AB" is ticked.

Is this possible?

TIA
SBF
 

A:Solved: Access: Filter using Combo box and multiple fields

This is not quite as straight forward as the Google example because you have to convert the combo selection to a field name.
I had the code to do this a few years ago, but I don't think I do now.
I will see if I can recreate it.
 

Read other 3 answers
RELEVANCY SCORE 62.4

I am running genuine windows xp sp3. Installed ie8 a few days ago but this morning most of my text input fields have shrunk to an extremely small size and the text itself is about as big as a pixel. I cant see what I am typing. Some sites are OK and others are not.
Has anyone experienced this or can offer any suggestions.

Read other answers
RELEVANCY SCORE 62

I have a spreadsheet of invoices exported from my accounting program. I created a column and copied the invoice date to it then formatted the column with Custom, mmmm-yy. I want to create a pivot table that says for Apr-14 the total for object Code 6630 $$$$. I want to see that in April, 2014 we spent X for Food from Shamrock, Muder, and Sysco. Then what we spent in May, etc. I might not even care about the Vendor but I do want it summarized by month no matter how I do it, and I am trying to isolate our food costs.

It is not combining all of the Apr-14 lines though, but listing them separately.

Thanks
Carla
 

A:Excel Date Format Won't Pivot Right

i would use the group function , and just use the date field - no need to change
 

Read other 1 answers
RELEVANCY SCORE 61.6

My pivot graph X axis won't change it's date format. I've tried changing the raw data format, data format in the pivot table but when I try to change the graph data format it will not change. I looked the date choices and it seems like no matter what I do the pivot table will not change the date format! HELP!

The pivot date is suck in format
Saturday, July 07, 2007

I want it to be
07/07/2007 or anything but what is above!
 

A:Pivot Graph won't change Date Format

Click on a cell in your original data and see what it says in the formula bar at the top. Even though a cell reads, for instance, Sunday, July 01, 2007, if it is just formatting, it should read 7/1/2007 in the formula bar. If it does not (and I suspect yours will not), then the problem needs to be addressed there, in the original data. A pivot table graph takes the data that is there and displays that.
If that proves to be the case, do the following (this assumes your dates are in column A).
Highlight your column of dates, go to data, Text to columns, Delimited, put a check in commas.(BEWARE!! Text to columns will write over any other data, and this will write to 3 columns, so insert blank columns - extras don't usually hurt - after your dates and before any other data)!!!
The data will split out as Saturday|July 07| 2007 (where | indicates a new cell). Insert a new column between July 07 and 2007. Highlight the column with July 07 in it and go to data, Text to columns, delimited by spaces. Now you will have your data in 4 columns - day, month, day and year. Delete the column with the day (if you can) so the data is in columns A-C.
In the 4th column over (D, I am assuming), enter the following formula:
=CONCATENATE(A1," 0",B1,", ",C1) and drag it down next to your date columns.
(If you can't delete the days, it would be B1, C1, and D1, etc.) This will give you data that looks like July 01, 2007. In the next column over, enter this formula:
=DATE... Read more

Read other 1 answers
RELEVANCY SCORE 61.2

I have a table of people data with multiple fields for the name: Prefix (Mr., Mrs., etc.), FirstName, MiddleName, LastName and Suffix (Sr., Jr., etc.). All fields are required except MiddleName and Suffix which often don't exist. I want to make this set of fields a multi-field unique index to prevent duplicate entries, and I have created the index with properties as follows: Primary=No (I already have an autonumber primary index), Unique=Yes, and Inglore Nulls=No. But the Suffix field is giving me trouble when it is blank as it often is. If a value is entered in this field (as with any other field) I am properly prevented from creating another record with the same values in the corresponding name fields, including this Suffix field. But if the Suffix field is blank/null (most are, because only a few people use a suffix on their names) I need that null value to be treated as a value for the purposes of determining uniqueness. Mr. Joe S. Blow should be the same as Mr. Joe S. Blow when both are missing a Suffix value, and I want the database to treat those names as the same, and prevent input of the second of them because one has already been entered.

As I understand it from Access Help, setting the index property Ignore Nulls=No should accomplish this. I understood this setting to mean that uniqueness is then determined on the remaining index fields, as though Null were being treated as a value for the purposes of testing uniqueness. That isn't the case. It lets me e... Read more

A:Solved: Null fields in multiple-field indexes in Access 2003

Read other 16 answers
RELEVANCY SCORE 60.8

Hello Excel Experts. I've been visiting this forum often and appreciate the help recieved. Got one that seems fairly simple but I can't pin it. Here's the deal, I have a pivot table that has 4 columns. In the Row label field, I have a "Date received" field with dates. In the Values field I have "count of date received" and below that "count of date completed". Then I have a calculated field that divides those two fields to come up with Percent Complete. So ideally, date recieved should have an equal amount of date completed (or 100%).

What I'm looking to do is identify the OLDEST date IF the Percent Complete is less than 90%. There can be multiple dates that are less than 90% complete in the Row Label, but I just want to list the oldest date. This way I can use a "datedIF" statement which tells me how much of a backlog by days I have. I'll attach a sample of table. Any help is greatly appreciated.
 

A:Excel Pivot Table IF Statement to return a date

1. Do you want to list every date that has less than 90% or only when total is less than 90% and just list one date?
2. Do you want this new field to be part of pivot or as normal formula?
 

Read other 3 answers
RELEVANCY SCORE 60

Mentors,
In my access database have a field that defined as date from the table. my users want the field as a choice either a date or text words .Can that possible?
 

A:Solved: drop box between a date and text?

Read other 8 answers
RELEVANCY SCORE 60

in my data source the date field is custom ddd mmm dd yyyy

When I create the pivot table it shows as dd-mmm but when I try to format the field it already shows the format correctly but the sample show dd mmm

ie. Source table shows Thu Mar 26 2015
Pivot Table Shows 26-Mar

I am unable to reformat it to show the source.

So how do I get the pivot table to display the right date format?
 

A:Excel Pivot Table Date is in wrong Format and I can't get it to change

Have you definitely ensured all cells in that column are formatted the same in the source table?
I formatted the current date as you describe and created a pivot table with that data and Excel automatically used the same formatting. I'm using Excel 2013.
 

Read other 3 answers
RELEVANCY SCORE 60

Hi

Can someone show me how to covert a date e.g. dd/mm/yyyy so that it reads as mm/yyyy only and so that a pivot table doesn't recognise the orignal long date of dd/mm/yyyy.

Tried using custom to mm/yyyy but pivot table still reads the date as dd/mm/yyyy.

Thanks

NCFC (Newbie)
 

A:Excel 2007 - Change date format for pivot tables

hi NCFC,

Don't know about Excel 2007, but in 2003:

Right Click the Pivot Table,
Field Settings,
Select the field name that holds the dates,
Number,
Choose your format.

lol
Hew
 

Read other 2 answers
RELEVANCY SCORE 60

another problem i'm having with IE8 that i cant seem to find a solution for just solutions for web masters, is that certain web sites with text fields (for example myspace profile editor, or comcast email login) the text fields are small and the text that is displayed inside is MICROSCOPIC. I cant seem to find any answers when i search anything on Compatibility Views. The Compatibile View button is nowhere to be found when i have these issues... I would go an reinstall 7 but ever since 8 was released i've had nothing but problems with 7
 

Read other answers
RELEVANCY SCORE 59.6

Am having difficulty in combining Text and a date contained in another cell (A16)into one cell.
Text is - Percentage Growth YTD to
Date is in B4 (custom d-mmm-yy) and is manually input (not today or Now)

Have tried
=”Percentage growth YTD TO “&TEXT(B4,”DD/MM/YY”)
formatted as Text without success , formula just remains in CELL A16.

Any suggestions

Pedro
 

A:Solved: Combine text and date in one cell

That should work - if B4 really has a date in

can you attach a sample sheet , so we can see what you are doing please
 

Read other 5 answers
RELEVANCY SCORE 58.8

Hello,
I have a column of dates that I have formatted like this:
\S\Ayymmdd
The date needs to look like this:
SA160327
This is exactly what I need, except I have to insert these in a csv file, and when I try to copy and paste the date, the formatting is gone, and I just get numbers like this:
42456
Does anyone know how I could copy my dates so that they're just text but in that format?
I'm currently retyping all of them, and I have hundreds....
Any help is much appreciated. Thanks in advance.
Fern.
 

A:Solved: Converting custom date format into text

Read other 8 answers
RELEVANCY SCORE 58.8

A predecessor designed a database that stores a text field that the end user fills with a date, like "Monday, August 13, 2007". Now of course the end user wants to sort the output by date (previously events were entered in date order so that retrieving alread had things in order). The web page that hits the database is written in VB Script under ASP. I'm looking for a function that can sort these dates either within the SQL query or afterwards in VB Script. I could kludge something together, but I'd prefer something elegant.

My current SQL query:

strSQL = "SELECT Event, EventDate FROM AcademicCalendar ORDER BY EventID;"

where EventDate is a text field (not a true SQL date field).

Thanks in advance!
 

A:Solved: Sorting text string as date in SQL/VBScript

I solved this one on my own. Probably far from the most elegant, but this is what I did:

The overall strategy is to pull the records from the database into a new array. That array consists of the fields I'm interested in plus an additional column that is a sortable date. That new date column has be be constructed from the text field in my database that contains a date as described in my original post. I use VBScript's regular expressions to extract the date portion of the text field then convert it to a useable date using CDate. Then the whole array is sorted on the new CDate field before showing the results.

First, I query the database and retrieve the recordset into oRS. The recordset has a forward-only cursor so I use the method described here to count the records to set up my array size. Note the need to use the .MoveFirst method to move back to the beginning of the record set.

The key to sorting is the function at the end (QuickSort). That function was "borrowed" from here.
Code:

<%
Dim regEx, strMatchDate, arrResults()
arrTemp = oRS.GetRows()
intRecordCount = UBound(arrTemp,2)
oRS.MoveFirst
ReDim arrResults(intRecordCount,4) ' I have 4 field to display
Set regEx = New RegExp
With regEx
.Global = False
.IgnoreCase = True
.Pattern = "[a-z]+\s[0-9]+,\s[0-9]{4}" ' matches on pattern Month Date, Year
End With
intCount = 0
Do While Not oRS.EOF
strDate = oRS("Dates")
arrResults(intCount,0) = strDate
arr... Read more

Read other 1 answers
RELEVANCY SCORE 58.4

This site looks great...thought I saw question a while back when I googled here... After reading thru many programming questions here, I realize I know lots less than I thought! ha. Think its a simple Excel formatting, but its been a while.

I've downloaded UK bank files to Excel, and need to change all dates to US format (12/6/2012 to 6/12/2012), with their corresponding text & numbers columns, and then sort top to bottom (oldest to newest), rather than how they came in (newest to oldest) to complete 2 yr. accounting summary. Excel 2007 on Windows 7 laptop.

Thanks much, guys! Jillian

I've included a sample of the columns from Excel sheet. As you can see, there are 5 columns, (oops maybe it won't show as cols) so all corresponding info has to move with the date, when I sort earliest to latest, after changing format for the 2 yrs I have to do. The format options given did not work, even tho' it IS indate format, not text, but when importing, it was already imported as US, not UK. Need to change. Not sure how to set that up, and have all info follow on corresponding cols. Thanks.

27/06/2012 20-71-64 5346xxxx -3.7 PAYMENT EAST MIDLANDS TRAI REF 177 1969574810 BCC
26/06/2012 20-71-64 5346xxxx -28.16 PAYMENT W M MORRISON PLC REF 174 7731688810 BCC
26/06/2012 20-71-64 5346xxxx -13.99 PAYMENT SAINSBURY'S S/MKT REF 174 7884931810 BCC
26/06/2012 20-71-64 5346xxxx -10 PAYMENT LUL TICKET OFFICE. REF 177 7365217810 BCC
25/06/2012 20-71-64 5346xxxx -422... Read more

A:Solved: Excel date format from UK to US & accompanying text files

Read other 16 answers