thevikas87

iTrader: (2)

Quick question for you guys.

My manager wants me to setup a spreadsheet we have so that when its opened, we have to sign in with a user name and password, and to where it records who signed in at what time.

Just to keep a record of the fact the spreadsheet is being checked and how many times they checked it etc...

Anyone know how to do this?

Thanks in advance!

Bearcat94

You can protect the sheet/workbook with a password, but I don't know how to get it to log a user and record who used the sheet and when.

swoosh

iTrader: (5)

OP you need MSACCESS....

65 Fury Convert

I can't help but to tell you some years ago the place I worked at had an Excel worksheet that the entire department used. It was saved on the network and we could see who was in it and who saved it last. I'm just telling you this to say I think it can be done

Lightus

Easy, create three more columns. One column should be titled "username", another "password", and the last "time". Then just have everyone type their username and time when they check the page. Make sure to tell everyone to type their passwords as asterisks so that others won't steal it.

#1 STUNNA

Q1911

I don't know about having it record sign ins, but you can probably modify this to work.

www.ozgrid.com/forum/showthread.php?t=32869

hornyleprechaun

will have something shortly

hornyleprechaun

https://docs.google.com/open?id=0B2j...QwODFmYTE0YWQ1

Password is "whatever" and you can change that if you go to the Visual Basic section by pressing Alt + F11. The user tab will be protected after each entry is made after each log on.

phee

iTrader: (5)

hornylep. are you in the finance business?

hornyleprechaun

Accounting

Whiskers

4?

hornyleprechaun

Meant to add that when you click the link make sure to download the file. It won't work in Google Docs

gatrhumpy

iTrader: (1)

hornyleprechaun

OP you should also considering hiding that Users tab. I would add the following code.

Right before the End Sub in VB add the following

This will hide the sheet so that normal users can't unhide it to view it. It can only be made visible with the following code.

If you just want to hide it and still be able to unhide it by right clicking on a worksheet and going to Unhide then add the following code before the End Sub in VB.

thevikas87

iTrader: (2)

Thanks for the info, I'll try this and report back!

Majofo

stogie1020

another "need excel assistance" request...

OK, I have a spreadsheet I use to keep track of billings for thing.
Columns are as follows:
Date, QTY, Description

The description cell is a drop down list (Data Validation from static list on sheet2) that contains the rate for the particular description choice. Choice 1 might be "eggs @$2/egg" and choice 2 might be "toast @$1/slice".

What I would LIKE to be able to do is have the choice in the drop down list actually poll the QTY column for that row and then insert that value into the description such as "eggs 4@$2" if the qty value was 4. FYI there are multiple (20+) choices in the drop down list.

How do I make this happen?

Majofo

By eggs.. you mean gasoline, and by toast.. you mean matches.

swoosh

iTrader: (5)

stogie1020

for real though, I know some of you are wicked excel wizards.

Anachostic

I think the function you are looking for is VLOOKUP. That's all I have to offer. I'm sure someone will give you a full solution, but this info might be enough to get your own answer first and piss them off.

stogie1020

Thanks bud. Will look into it.

hornyleprechaun

Vlookup won't work for that. You need to create a macro. I'm really busy this week but will try to remember to get back to this thread.

The Dougler

iTrader: (1)

Since this is for billings why not add another column "extended cost" and multiple your qty by rate in the description so the true cost for that service is clear.

hornyleprechaun

Temp solution....

Cell D2 formula =left(C2,find("@",C2,1)-1)&B2&mid(C2,Find("@",C2,1),99)

That will combine QTY and description for you.

stogie1020

Horny, thanks for the work.

The issue I have is that the cell I need to put the QTY data in is variable, meaning it's content is a drop down listing that has 20 options to choose from that are all different services (data validation list).

As an example:
(Current)
Date QTY Description
6/12/13 3 Pieces of toast @$1/slice
6/12/13 5 Eggs @$2/egg

(Desired)
Date QTY Description
6/12/13 3 Pieces of toast 3@$1/slice
6/12/13 5 Eggs 5@$2/egg

But the description cells are a dropdown data validation list on a different sheet.

I basically need to be able to pull the digit from two cells to the left of the current cell and insert the digit in a specific spot in the cell text. I can't assign a specific cell to pull from since the dropdown list is available in each cell in the column.

Dougler, why not let excel do the math and create a column for total cost? Makes sense. Truth is, this is simply a guide for a bookkeeper to use in entering data into quickbooks, and she knows anything in parenthesis in the Description cell is for her info only and not for the actual invoices. I am trying not to rock that boat too much.

swoosh

iTrader: (5)

Create a column to pull the data (from the validation sheet), hide it and use concat in the adjacent cell...

nfnsquared

No bacon?? Phail...

stogie1020

If it helps to see it:

I have a sheet with three columns, the third being a "description" column.

Each cell in the description column is a drop down selection from a data validation list on sheet 2.

I am trying to find a way to, upon choosing a selection from the drop down list, have excel automatically pull the number from the QTY cell to the left (i.e. B2) and put it into the description cell (i.e. C2).

Goal is to have C2 show Hash Browns 5@$2/serving

Is this possible if C2 (all of C column cells) is a data validation list from elsewhere?

Is there a way to insert into the data validation list entries some code to pull the number from a different cell in the B column for the row that the description is chosen for?

RenoTL

This seems a bit more complicated than it needs to be but I understand you are working within a given system. You indicated the the contents of the DESCRIPTION drop-down list comes from another sheet. On that sheet, can you create a new column that takes the last entry in the QUANTITY column and converts that entry to text followed by creating a string from the each row within the DESCRIPTION list and adds in the quantity (text) string right before the "@" symbol. For example, if the current column in the DESCRIPTION cell contains, "Hash Browns @$2/serving", the next column would then become, "Hash Browns 5@$2/serving". You can use the text manipulation functions to achieve this. Of course you would then use this new column in the 2nd sheet to populate your drop-down list on the 1st sheet.

stogie1020

Thanks Reno, problem is, ANY of the 20 selections for the drop down are available in EACh cell in the c column, so I need to find a way to add in relative data, i.e. data from the cell "1 to the left" of the current, no matter where the current one is.

Majofo

stogie1020

Well, it looks like the Substitute function was what I was looking for. Unfortunately it leaves the cell as a formula value and therefore wont work since the cell needs to be copied from and pasted elsewhere... Too much of a PITA to manually convert it to static text each time.

The formula as was provided ona different forum is
=IF(C2<>"",SUBSTITUTE(C2,"@",B2&"@"),"")

hornyleprechaun

You need a macro....

Would you have a problem clicking a button before each selection or would you rather click a button after all selections to update column c.

nfnsquared

Free Excel 2010 course, coupon good today only

stogie1020

To update the content from formula to value? After and only once would be good. What would happen if I executed the macro on column C with c1-c5 filled and then later needed to use c6 for the original purpose (drop dpwn selection, formula)?

hornyleprechaun

PM me the file or your email. I'll get it to work with a macro.

Whiskers

Bacon

stogie1020

Excel question:

I have a sheet (sheet1) with a single column containing 3500 text entries each in their own cell.

I have a list of 35 cell numbers (randomly selected) that I would like to extract from the 3500 list.

What I want to do is on sheet2, have a list of the 35 selected cell numbers in column A and then in column B a formula to pull the text content from the sheet2 column A cell # on sheet1.

I tried using =Sheet1!(Sheet2!A1) where A1 is the first item in sheet 2 list of 35 but it just sequentially references when I copy down to the remaining 34 items instead of pulling the actual value form the Sheet2CellA2, etc.

Summary:

Sheet 1 has 3500 items in a single column
Sheet 2 has 35 random numbers between 1 and 3500.

I want to populate sheet 2 with the cell value from sheet 1 that corresponds to the row number listed in sheet 2.