advanced excel help

AHarvey · 6 Jul 2010 at 19:27

Hi all,

I've just been made redundant from my position and have been replaced by an advanced team who basically know nothing.

As such, I have been asked to build a spread sheet that will allow them to write a shift report saying which employees are doing things at certain times.

I have so far figured out drop down lists so they just need to go in and select what jobs they are doing and where.

I then however would like to output this in to BB code format as we post the shift report on an internal forum.

Can this be done?

Pho · 6 Jul 2010 at 21:09

You could probably write some form of VBA script to process all the data into BBCode, might be pretty time consuming though.

AHarvey · 6 Jul 2010 at 21:25

well I've found this:

http://www.cpearson.com/excel/ImpText.aspx

it outputs to an TXT file, which to start with will work.

I've added a button to call the script however the problem I have is that no one has access to admin rights so can't save on to the PC directly, instead they have to save to their profile.

Of course each profile has a different address different and I have no idea how to change it to try and make it work

*unless I share it to the saved drive, hmmmm.

AHarvey · 6 Jul 2010 at 21:34

well, the script works and outputs to a folder on the shared drive.

Now I need to figure out how to ignore empty cells :S

Pho · 6 Jul 2010 at 21:34

You could always write it to a temporary worksheet.

Environ("USERPROFILE") should get you their c:\users\xx or c:\documents and settings\xx location no matter where it is.

Akempster · 21 Jul 2010 at 23:23

AHarvey said:
well, the script works and outputs to a folder on the shared drive.

Now I need to figure out how to ignore empty cells :S

for empy cell checks you could use the IFISNA function then have this ignored from the rest of the data you wish to use

fretted · 22 Jul 2010 at 18:05

ISBLANK() may be more appropriate than ISNA()