Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Microsoft Excel: Automating Entries Between Sheets?


  • Please log in to reply
4 replies to this topic

#1 Johnz414

Johnz414

  • Members
  • 452 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Milwaukee, WI
  • Local time:02:46 PM

Posted 07 January 2008 - 04:54 PM

Hi Anyone of an Excel Nature,

I have a question for anyone versed in using excel.

I keep a months records of my hours that I work as apartment manager for the owner on a single excel sheet.

However, the owner wants weekly time sheets and he wants them sent to him now.

I have at least half a years monthly time sheets that he wants in a weekly format.

I would still like to keep my records of my hours in a monthly format so that I can see what I've done at an entire month's glance.

So, my question is does anyone know of a way to automate the excel monthly sheet so that when I enter data into the monthly sheet that it automatically duplicates itself in the weekly sheets?

It is kind of a lot of copy and pasting other wise.

If you don't know maybe you know where there might be an answer?

Thanks.

John :thumbsup:

Edited by Johnz414, 07 January 2008 - 04:57 PM.

John

"Genius is nothing other than pointing out the obvious",
Albert Einstein.

"I am what I am and that is all that I am, I am Popeye the Sailor Man", Popeye.

BC AdBot (Login to Remove)

 


m

#2 usasma

usasma

    Still visually handicapped (avatar is memory developed by my Dad


  • BSOD Kernel Dump Expert
  • 25,072 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Southeastern CT, USA
  • Local time:03:46 PM

Posted 08 January 2008 - 09:13 AM

Yes, but not having used Excel in this way for about 5 years I can't recall the exact details.

It has to do with linking sheets. Here's how I seem to recall doing it:
- go to the new sheet and select a cell and type "=" (without the quotes)
- go to the old sheet and click on the cell that has the data that you want on the new sheet
- go back to the new sheet and you'll see that it has the Sheet number, an !, and the cell reference from the old sheet. (my Excel screwed up the Sheet number so I had to change it manually).
- press Enter and you'll see the sheet's contents reflected there (there may be some difficulty with the cell formatting tho')

Once you've achieved that you can use it to start new calculations.
There's also a way to enter the calculations with the cell reference embedded so that the data from above isn't needed on the sheet (but that'll take some research to do).

Edited by usasma, 08 January 2008 - 09:14 AM.

My browser caused a flood of traffic, sio my IP address was banned. Hope to fix it soon. Will get back to posting as soon as Im able.

- John  (my website: http://www.carrona.org/ )**If you need a more detailed explanation, please ask for it. I have the Knack. **  If I haven't replied in 48 hours, please send me a message. My eye problems have recently increased and I'm having difficult reading posts. (23 Nov 2017)FYI - I am completely blind in the right eye and ~30% blind in the left eye.<p>If the eye problems get worse suddenly, I may not be able to respond.If that's the case and help is needed, please PM a staff member for assistance.

#3 tos226

tos226

    BleepIN--BleepOUT


  • Members
  • 1,568 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:LocalHost
  • Local time:03:46 PM

Posted 08 January 2008 - 11:51 AM

Usasma, yeah, your memory is correct, that'll work :flowers:

Another way, without any data duplication, is filtering.
Keep all the data on one sheet, contiguous rows and columns - meaning no blank rows, no blank columns. Assuming you have heading, use Data, then Auto filter menu. That will put little dropdown arrows on every heading. Click the arrow, select Custom filtering from the dropdown for whatever you want to select. Print the filtered data. Select All when done and the data is intact and you didn't change the file size.

You may want to format the data sheet for printing in a way that will indicate today's date, you can put such information in the header, and embelish it with fancy formatting, for the manager to admire :thumbsup:

There's also Advanced filter mode - read Help.

#4 Johnz414

Johnz414
  • Topic Starter

  • Members
  • 452 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Milwaukee, WI
  • Local time:02:46 PM

Posted 09 January 2008 - 12:38 AM

OK, I've checked out both approaches described here. They both show some promise.

But the filtering method I don't think will serve my purpose. It's nice but the owner wants a record of every day/week even if I don't do any work on certain days. So there are going to be blank rows at least in the copy for him. He wants to see everything to get an idea of how I'm using my time.

And I've tried the method put forth by Usasma only to find that it's pretty much the same thing as copy and paste. IE., it doesn't work like a "Formula" where when I enter data on the "Monthly" sheet (after setting up the "Formula" on a template) then the data automatically gets copied to the weekly sheets from the Monthly sheet.

I'm looking more for a "Formula" that permanently reduces my work load. I just make a template and wallah when I keep track of my hours on the monthly sheet the data gets duplicated automatically to the weekly sheets. Also, I need and would like it if I change data on the monthly sheet then it automatically updates on the weekly sheets.

I already know that I can copy and paste.

Oh, I also would like to do the same thing with the "descriptions" - what I write up to describe what I did during the hours worked that I include on my work sheets.

That would be when I write up the descriptions on the monthly sheet then it gets duplicated automatically onto the weekly sheets.

Of course if I'm doing something wrong please do Enlighten me.

Here is the "formula"? that I've ended up using to duplicate what Usasma advised me to try: "='[Nov 2007]Nov 2007'!F13" (without the quotes).

1. "Nov 2007" is the monthly sheet that I'm copying data from and "Week 1" is the first weekly sheet that I'm copying the data too.

2. "Weekly 1" is conspicuously absent in the formula however the data does transfer when setting this "formula"? up in "Weekly 1".

3. However, the data will not update automatically on the weekly sheet when I change it on the monthly sheet. i need/want it to update automatically.

The question then is this the right formula: "='[Nov 2007]Nov 2007'!F13"? ("F13" being the column and row of the data being duplicated).

Does that help?

John :thumbsup:
John

"Genius is nothing other than pointing out the obvious",
Albert Einstein.

"I am what I am and that is all that I am, I am Popeye the Sailor Man", Popeye.

#5 usasma

usasma

    Still visually handicapped (avatar is memory developed by my Dad


  • BSOD Kernel Dump Expert
  • 25,072 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Southeastern CT, USA
  • Local time:03:46 PM

Posted 10 January 2008 - 06:53 AM

It'll take some experimentation to do what you want - but it is possible. You'll have to mess with the formatting and the formula's to get it to work. In this case the Help files are your best asset. Also, you can try this Google search to see if there's more info out there: http://www.google.com/search?q=excel+suppo...lient=firefox-a
My browser caused a flood of traffic, sio my IP address was banned. Hope to fix it soon. Will get back to posting as soon as Im able.

- John  (my website: http://www.carrona.org/ )**If you need a more detailed explanation, please ask for it. I have the Knack. **  If I haven't replied in 48 hours, please send me a message. My eye problems have recently increased and I'm having difficult reading posts. (23 Nov 2017)FYI - I am completely blind in the right eye and ~30% blind in the left eye.<p>If the eye problems get worse suddenly, I may not be able to respond.If that's the case and help is needed, please PM a staff member for assistance.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users