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

Advance Excel


  • Please log in to reply
5 replies to this topic

#1 DeCipher

DeCipher

  • Members
  • 21 posts
  • OFFLINE
  •  
  • Local time:02:57 PM

Posted 10 April 2005 - 12:02 AM

I'm trying to make a excel file for work. It's kinda long so I'll post a few questions at a time.

I have 2 excel files. The first one file name is the date, mmddyy. That file has times, and days of week data in it. IE, 2:30 is one cell and the other is FRI-SUN. This file has multiple sheets, which are called the days of the week, ie FRI-SUN, SUN-THUR, sometimes it has 3 sheets.

The second file is called Times. I want the times file to pull the times from file 1, the date file. But the thing is that file 1 is updated every week, aka the file name changes every friday.

So i was thinking, having a cell in the Times file that had the value of the date. Then in the cell that i wanted to copy would have the name of the file + the other cell's value. Here let me give an example.

Time file: Cell A1 value: ='[My File B1.xls]Sheet1'!$A$1 -
Cell B1 value: 04092005

File trying to grab value from: Files name "My File 04092005.xls"
Remember that the file i'm trying to get the value from changes every friday, the file name does.

Hope this makes sense. I've tried all kinds of stuff for A1, but cant get it.

Keith
Posted Image

BC AdBot (Login to Remove)

 


#2 jgweed

jgweed

  • Members
  • 28,473 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Chicago, Il.
  • Local time:02:57 PM

Posted 10 April 2005 - 09:22 AM

When a cell is linked to another, any data in the first cell that is changed should transfer to the second when you update the worksheets.
Regards,
John
Whereof one cannot speak, thereof one should be silent.

#3 Grinler

Grinler

    Lawrence Abrams


  • Admin
  • 43,656 posts
  • ONLINE
  •  
  • Gender:Male
  • Location:USA
  • Local time:02:57 PM

Posted 10 April 2005 - 09:26 AM

To even go into more detail, in the cell you want to link data to, click on the = key and then choose the cell in the other sheet or workbook that you want to link to and press enter. Now those two cells will be linked.

So if you have a file called stats.xls and a file called overview.xls, you can click on a cell in overview, press the = key, then select a cell in the stats file and press enter. Now those two cells are linked

#4 DeCipher

DeCipher
  • Topic Starter

  • Members
  • 21 posts
  • OFFLINE
  •  
  • Local time:02:57 PM

Posted 10 April 2005 - 10:33 AM

Yeah i know how to do that but the file i want to link to file name is always changing. So how do i get each cell to link to a different file without having to change each cell to point to the different file?

Keith
Posted Image

#5 Grinler

Grinler

    Lawrence Abrams


  • Admin
  • 43,656 posts
  • ONLINE
  •  
  • Gender:Male
  • Location:USA
  • Local time:02:57 PM

Posted 10 April 2005 - 11:05 AM

You cant as the filename is the desginator.

#6 Rimmer

Rimmer

  • Members
  • 2,159 posts
  • OFFLINE
  •  
  • Location:near Sydney, Australia
  • Local time:05:57 AM

Posted 11 April 2005 - 03:47 AM

DeCipher your problem lies here:

But the thing is that file 1 is updated every week, aka the file name changes every friday.


You are overwriting the data in the same 'book' each week. The cells you are trying to link to have been renamed and essentially no longer exists.

Is it not possible to copy the book and change the name of the copy to the new date, this will preserve all the formatting, etc. Thus books for every date so far will still exist and you can link to them in your Times book. You can link to files of future dates, you'll get a REF# error, but when the book is created the data will appear.

:thumbsup:

Edited by Rimmer, 11 April 2005 - 03:49 AM.


Soltek QBIC, Pentium 4 3.0GHz, 512MB RAM, 200GB SATA HDD, ATI Radeon 9600XT 256MB, Netgear 54Mb/s WAP, ridiculously expensive Satellite Broadband
Windows XP Home SP2, Trend Micro Internet Security, Firefox, Thunderbird, AdAwareSE, Spybot S&D, SpywareBlaster, A-squared Free, Ewido Security Suite.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users