I love helping. I was a newbie once too!
I'm looking at the file now, get back with you in a bit.
I love helping. I was a newbie once too!
I'm looking at the file now, get back with you in a bit.
You can e-mail it by pressing the e-mail link at the bottom of my post.
Welcome to the board.
Can you post an example of your spreadsheet?
Using the New Reply button, you will find a browse button at the bottom of the reply page. Attach your file and post your reply. DO NOT preview post after attaching file or you will have to reattach.
I will be glad to help on the pivot table if you wish.
Curses, foiled again! So sorry, I am not able to open the file. :banghead::banghead::banghead::banghead::banghead:
Glad it wasn't the file, I'll try again.:yes:
Nod,
I tried to download your file. When I tried unzipping it it appears to be corrupted. This may simply have been me, but thought you may want to check. :usaflag:
This graph is incredible!! I am amazed. Doing a search for "graphs" to answer a much simpler problem I was having and I stumble across this. I concur with AJW and Chris, the thoughts for applying this to projects I either am currently doing or plan to do boggles the mind.
Since OzGrid had approximately half as many members when this was originally posted, I thought I would add a reply in hopes that some of the 1200+ members who have joined since early March would have the chance to view this.
WOW!!:yes::yes::yes:
BTW, has Chris ever pursued the ship battle?
I assumed that Cronina wanted a running total. Excel will correctly add negative numbers. Check the formula in column C of the attachment. I entered bnix formula in B. I hope this helps.:yes::yes:
Marjan
In Will's U2U message to me, he pointed me to this thread because I have worked with Quickbooks before. I read through the thread and will offer any help you would like concerning setting up Quickbooks.
Like Will, I have a financial accounting background so a solution should be fairly simple and straight forward to set up. In addition to the ease of exporting reports that Roy mentioned, Excel makes it easy to create import files as well.
Take a look at the attachment and I think you'll see what I mean. :cool:
An over the road trucking client used it to post their payroll entry to Quickbooks. A third party processed the payroll and the reports that they returned with the checks were reconciled against the payroll submission from the HR department then forwarded to be input to Quickbooks by the accounting department.
The following text from the VBA help may be what you need. If you can tab to the button that needs clicking you can send the UserID, Password, tabs, and enter keystrokes.
CAUTION!!!!!!
You will need to know the application name that you are sending keystrokes to as well as being aware that the focus of the program shifts to the application you run using this process. The SendKeys routine probably should not have any other actions executed during it.
Practice with the calculator example until you feel comfortable with how it works.
SendKeys Statement Example
This example uses the Shell function to run the Calculator application included with Microsoft Windows. It uses the SendKeys statement to send keystrokes to add some numbers, and then quit the Calculator. (To see the example, paste it into a procedure, then run the procedure. Because AppActivate changes the focus to the Calculator application, you can't single step through the code.). On the Macintosh, use a Macintosh application that accepts keyboard input instead of the Windows Calculator.
Dim ReturnValue, I
ReturnValue = Shell("CALC.EXE", 1) ' Run Calculator.
AppActivate ReturnValue ' Activate the Calculator.
For I = 1 To 100 ' Set up counting loop.
SendKeys I & "{+}", True ' Send keystrokes to Calculator
Next I ' to add each value of I.
SendKeys "=", True ' Get grand total.
SendKeys "%{F4}", True ' Send ALT+F4 to close Calculator.
HTH
Edit 04/03/04 Attachments not available: You can follow the Brandtrock Files link to the downloads page in the signature area if you wish to download a copy of this file
Looking back through some old files I ran across this one. I changed the multi-step process for making a monthly adjusting journal entry.
The Fischetti method was being used by a partner when the account got handed to me. He liked pencils and paper, I liked Excel. The spreadsheet I designed required changing one cell (highlighted in light blue in the attached workbook) to get to the same end result as his multi-step method.
I have added some notes to the sheets in hopes that anyone who looks into it will understand what is being done. Looking back, this seems a tad clunky but it was a vast improvement over the method I inherited!
The client's Quickbooks g/l names have been changed. Since I have started my own company, I have written macros to import information into Quickbooks so I would improve it one more step by creating the import file if I was still using it today.
Check out the Newsletter archive. This may help you out. The subject is sorting by colors. To get to the Newsletter archive just go to the home page, click on the Newsletter tab. The Newletter archive link at the left takes you to the old ones, scroll to the bottom and click Issue 1.
HTH
Thanks!!
I was in one of those "can't see the forest in spite of the trees" moments and was forgetting to reference the sheet name using the Indirect method you illustrated above. :duh: Duh!!!
I never thought of naming the cell. :thumbcoo: I will do that and be very pleased.:clap2: Thanks again for opening my eyes. :o2
I swear I read a hint somewhere in this forum about a workaround to conditional formatting and referencing another sheet. Unfortunately I am unable to find it now that I want to try implementing it.
It seems like it was using the INDIRECT function in the Formula Is box but I have had no luck getting it to work. Does anyone out there:
A. Know which thread/hint I am referring to?
B. Have a suggestion or workaround?
I realize that I can put the information into a remote area of the sheet I want the conditional formatting done on - in fact, that is how it is done presently, I was just hoping to incorporate what I felt was a neat tip.
TIA for any assistance on this.
Quote
The attached workbook has my simple code to do the following:
I don't see an attachment.
I think the macro in the attached sheet will suit the purpose you need. If not, please post back with what needs tweaking and we can figure it out :usaflag:
Obviously the dynamic range name you have in your sheet should be used rather than the "dummy" MyDataRange that I set up in the example.
HTH
Using the New Reply button will get you to a screen that has a Browse button at the bottom. Click that button and attach your file from your computer. There is a 1MB size limit and using the Preview Post button will unattach the file (see the warnign next to the button).
HTH
I checked it out on my end. I should have removed Module 1 before attaching last time. This time it is omitted. Sorry about that.
As for the other error, a couple of questions:
Are you running the attachment from my post?
The dynamic range names Data1 and Data2 are defined in that workbook and would cause an error if the code were cut and pasted into a workbook without them.
Look under Insert>Name>Define to see how these are set up.
Also, what O/S are you running?
What Version of Excel?
I am going to bed now but will check back first thing in the morning.
Found it, finally!
The following code is in Module 1 of the attached file. I assumed that you want to continue copying down when you paste your data. I hope that that was correct. If not, post back.
Sub CopyData()
Application.Goto reference:="Data1"
On Error Resume Next
Range(Data1).Select
Selection.Copy
Sheets("Pivot").Select
Range("A1").Select
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = "" Then
ActiveCell.Select
Else
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
End If
ActiveSheet.Paste
Sheets("Data2").Select
Range("Data2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Pivot").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
HTH :usaflag:
Didn't realize the country field was going to be a problem, sorry. Working on getting some code for you.
The dynamic ranges are still going to be a great help in this effort.
Under Insert>Name>Define, I added dynamic ranges for Data 1 and Data 2 tabs. These will expand as data is added in each sheet.
I will post back shortly with some code for the copying and pasting portion for you. I had a similar project recently and simply need to locate the disk it is on so I can copy it for you to see :usaflag: