Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-30-2012, 11:24 AM
userman userman is offline organize files Windows XP organize files Office 2003
Advanced Beginner
organize files
 
Join Date: Sep 2010
Posts: 52
userman is on a distinguished road
Default organize files

hello!

I need to organize documents that relate to specific products, in EXCEL.
Each product, has some specific documents for it, eg specifications, usage directions, cautions, etc.

I have created a top row that is constand, and below I enter the products.
Then, I save in each cell, the specific document, for the specific product.

The problems are these:
1) I want each cell, where a document will be saved, to be red if empty and green if filled with a file
2) Under a specific document type, ie a specific column (let's say "specifications"), I need to store a various number of files, so that the cell, for each product, must be splitted in various subcells.


Now the tricky part is this: I want to load a simple text file with some text lines in that cell, then EXCEL will automatically read it and split the cell in a number of subcells of the number of the lines of the text file and also the names of the cells will be the text lines. Is it possible??

any solution?

thanks!
Reply With Quote
  #2  
Old 05-01-2012, 12:15 AM
macropod's Avatar
macropod macropod is offline organize files Windows 7 64bit organize files Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

hi userman,

No. 1 is easily resolved by using conditional formatting.
No. 2 depends on how the data in the text files are formatted (eg each specification on a separate line). It is also not clear from your post what you mean by a file, cell or a sub-cell. Are you trying to embed whole files in a cell, or just a piece of information about a file (eg its name).
If you provide more information about #2, someone here might be able to help you.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 05-01-2012, 05:32 AM
userman userman is offline organize files Windows XP organize files Office 2003
Advanced Beginner
organize files
 
Join Date: Sep 2010
Posts: 52
userman is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
No. 2 depends on how the data in the text files are formatted (eg each specification on a separate line).
the text file is a simple txt file (so no special character/paragraph format) with 10-15 lines of text, one below the other (seperated by newlines)

each line of the text file is 2-5 alphanumeric (maybe with some commas and -'s in them) words seperated by space

Quote:
Originally Posted by macropod View Post
It is also not clear from your post what you mean by a file, cell or a sub-cell.
by document/file I mean some pdf files that will be stored inside the relevant cells (eg a pdf of the cautions of product 1, a pdf of the usage directions of product 4, etc)
*not to be confused with the text file that I mentioned above

by cell, I mean the regular EXCEL cell, that in my case, will contain a specific pdf (for example the cell in row for PRODUCT2, under column CAUTIONS, will contain the pdf of the cautions of product 2)

by subcell, I mean the cells that will result, after splitting the cell under column SPECIFICATIONS for the row of a product

the splitting procedure will be automatic, I will just "somehow load" the text file, and then EXCEL will split that cell into a number of cells, according to each line of the text file

by "load" I dont mean I will store that text file into that cell, I will just give the directions to EXCEL, in how many subcells it must split that cell, and how to name each of the resulting cells (each line of the text file must have its relevant subcell, and the "label" of the subcell will be the text of the line, then I will store one pdf file to each of these subcells)

Quote:
Originally Posted by macropod View Post
Are you trying to embed whole files in a cell, or just a piece of information about a file (eg its name).
I will embed a (one) whole file in each cell

hope this helps

PS:
I think by explaining my whole project, I make it complicated, so I sum up my goal, without mentioning products, pdfs, etc:

- what I basicaly want is to split a specific cell into a number of cells and each of the resulting cells to have a text in them
- now, how EXCEL will know in how many cells to split and which text to place in each resulting text?
- it will find the all the necessary info in a simple text file of text lines seperated by "newlines"
- the number of the cells will be the number of text lines of the text file
- the text inside each resulting text will be each of the text lines of the text file

thanks
Reply With Quote
  #4  
Old 05-01-2012, 03:41 PM
macropod's Avatar
macropod macropod is offline organize files Windows 7 64bit organize files Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi userman,

IMHO, the simplest and most effective solution is to not put any of the data into the workbook but, instead, keep the data for each product in the separate files and simply have hyperlinks in the workbook to open those files. Amongst other things, this is must easier for you to maintain - if the PDF or the specifications for a given product change, simply replace the files with new ones having the same names.

If that is not suitable, you will need to provide a sample workbook with some embedded PDFs and specifications, plus some of their files, so I can see how you want the data processed.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 05-02-2012, 03:47 AM
userman userman is offline organize files Windows XP organize files Office 2003
Advanced Beginner
organize files
 
Join Date: Sep 2010
Posts: 52
userman is on a distinguished road
Default

okay

do you have any innovative idea how to display info like the below?
Attached Files
File Type: xls Book2.xls (16.5 KB, 11 views)

Last edited by userman; 05-02-2012 at 10:47 AM.
Reply With Quote
  #6  
Old 05-02-2012, 05:57 AM
macropod's Avatar
macropod macropod is offline organize files Windows 7 64bit organize files Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi userman,

When you want to provide files, please attach them here. You can do that via the paperclip symbol on the 'Go Advanced' tab.

In your spreadsheet, you say 'in each of the yellow cells, a number of pdfs will be linked'. That simply isn't possible. Excel can only hyperlink to one location per cell.

You also have a block of yellow cells with the notation 'here should go pdf#', where # is a number. While it is possible to attach files to cell locations, this isn't a good idea for a number of reasons including that it makes the Excel workbook:
• harder to maintain;
• slower to open and use;
• much larger (by an amount greater than the embedded file sizes);
• more prone to coruption - which could lose all your data;
• more likely to become out of date, with the contents no longer reflecting what's held in external PDFs, with all the consequences that can entail.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 05-02-2012, 10:48 AM
userman userman is offline organize files Windows XP organize files Office 2003
Advanced Beginner
organize files
 
Join Date: Sep 2010
Posts: 52
userman is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
Hi userman,

When you want to provide files, please attach them here. You can do that via the paperclip symbol on the 'Go Advanced' tab.

In your spreadsheet, you say 'in each of the yellow cells, a number of pdfs will be linked'. That simply isn't possible. Excel can only hyperlink to one location per cell.

You also have a block of yellow cells with the notation 'here should go pdf#', where # is a number. While it is possible to attach files to cell locations, this isn't a good idea for a number of reasons including that it makes the Excel workbook:
• harder to maintain;
• slower to open and use;
• much larger (by an amount greater than the embedded file sizes);
• more prone to coruption - which could lose all your data;
• more likely to become out of date, with the contents no longer reflecting what's held in external PDFs, with all the consequences that can entail.
so any alternative??
Reply With Quote
  #8  
Old 05-02-2012, 03:50 PM
macropod's Avatar
macropod macropod is offline organize files Windows 7 64bit organize files Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

As I've already said, you can achieve the same funtionality, without the embedding headaches via hyperlinks to the external files.

Of course, you'll need to figure out what you want to do about the fact you can't have more than one hyperlink per cell. You might, for example, have multiple rows for that particular item, with the cell in each row having a different hyperlink. In that case, of course, you'll need as many rows as the maximum number of hyperlinks. Alternatively, you might prefer to merge the files for that item into just one file.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #9  
Old 05-03-2012, 01:30 AM
userman userman is offline organize files Windows XP organize files Office 2003
Advanced Beginner
organize files
 
Join Date: Sep 2010
Posts: 52
userman is on a distinguished road
Default

- yes, I already decided to save hyperlinks (as shown in my sample xls as well)

- merging the files is not an option

- having multiple rows seems to be the only option, but, in different columns (ie in different products) the number of rows will be different and at the same time the total height of all those rows must be exact the same as the height of the 7. DETAILS cell

so, if in column G there must be 15 cells for 7. DETAILS and in column K there must be 4 cells for 7. DETAILS, I need to somehow correspond all those cells to the dimensions of 7. DETAILS, so to see that they belong to the 7. DETAILS category

any idea?
Reply With Quote
  #10  
Old 05-03-2012, 02:30 AM
macropod's Avatar
macropod macropod is offline organize files Windows 7 64bit organize files Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

As I've already said, you will need to have the same number of rows for each entry. Does it really matter if some rows for a given product are unused?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #11  
Old 05-03-2012, 02:43 AM
userman userman is offline organize files Windows XP organize files Office 2003
Advanced Beginner
organize files
 
Join Date: Sep 2010
Posts: 52
userman is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
Does it really matter if some rows for a given product are unused?
yes :/ ,
Reply With Quote
  #12  
Old 05-03-2012, 02:52 AM
macropod's Avatar
macropod macropod is offline organize files Windows 7 64bit organize files Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

In that case, you'll need to use something else. Excel cannot provide a variable number of rows per item. Neither can any other spreadsheet product.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #13  
Old 05-04-2012, 09:13 AM
userman userman is offline organize files Windows XP organize files Office 2003
Advanced Beginner
organize files
 
Join Date: Sep 2010
Posts: 52
userman is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
In that case, you'll need to use something else. Excel cannot provide a variable number of rows per item. Neither can any other spreadsheet product.
I know its ms office forums, but is there any other program that can offer this?
Reply With Quote
  #14  
Old 05-04-2012, 03:49 PM
macropod's Avatar
macropod macropod is offline organize files Windows 7 64bit organize files Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

You could use a table in Word. In Word, table rows can have multiple paragraphs, each with its own hyperlink.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Exporting .csv files into Outlook as sep files Antonio44 Outlook 1 09-22-2023 11:55 PM
organize files convert multiple csv files to multiple excel files mit Excel 1 06-14-2011 10:15 AM
organize files How to organize and insert blank cells between a huge number of data? tareq Excel 12 09-29-2010 02:12 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:54 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft