References

[fr4gm0nk3y]

Ok so I have information from several people, all of which are in the exact same format. What I'm trying to do is create a spread sheet with the person name in the A columb, then accross the first row I have information to be pulled from each persons data. I want to do so by creating a different sheet for each person.

How do I set it so that it pulls the data for the appropriate columb from the appropriate sheet? I was planing on renaming each sheet the same as the persons name in the A columb so I could use that to reference in each columb what sheet to use... Problem is I'm very new with excel and I suck with these equations =/

I hope all of that made sense...

 

[ThomasLafferty]

The formula you will need will depend on where you have each person's data stored. (same sheet, different sheet but same book, different book, etc.)

Could you be a bit more specific about exactly where each piece of data lives, what columns you need to pull into the sheet, etc. You might get away with a simple 3D reference, but then again, you might have to use a VLookup or HLookup function. Just depends, again, on how your data is structured...

Tom

Live once die twice; live twice die once.

 

[fr4gm0nk3y]

Well for those of you who are familiar with the .nfo reports that are generated by the system information utility, that's what I'm doing. I have a bunch of .nfo files with information from each user, and I'd like to have the user name on the left, and along the top the processor speed, ram, memory etc.

I want to set it so that when I add a user in the A column* the users name is the same as the sheet name that has all of the .nfo information. From there each column* looks up the data from the appropriate cells in each sheet, ex: CPU info is in cell E9.

 

[ThomasLafferty]

Sorry to be such a meathead, but I still need a bit of clarification:

I want to set it so that when I add a user in the A column* the users name is the same as the sheet name that has all of the .nfo information.

By this, do you mean:
A) You enter a user name in the A column and it automatically changes to be the name of the sheet that contains the user's information (ie the value you typed in cell A1 for example changes to a value corresponding to the sheet name)
or do you mean:
B) You enter a value in the A column which corresponds to the name of the sheet which contains the user's information?
C) Something else?

Please clarify...

Tom

Live once die twice; live twice die once.

 

[fr4gm0nk3y]

 

[fr4gm0nk3y]

I want each row to pull data in the columns from the appropriate sheets. I was thinking the best way to do it was to give each sheet the same name I'd use in the A column. Also in case you haven't already noticed from the screen shot, I'm having a problem where it doesnt always do what I put in the cell. I'm assuming I have something set wrong, so if you know what that setting is that'd help as well. Thanks for your help!

 

[ThomasLafferty]

Your cells displaying a formula are probably set to Text format. Format them as general format, then click in the formula bar at the end of the formula and hit enter (this forces Excel to update and reevaluate the cell entry).

This should fix the formula display problem, and if need be, when you are done press F9 to calculate the book and force the update.

I will look into an idea I have which doesn't involve VBA and will get back to you.

Tom

Live once die twice; live twice die once.

 

[ThomasLafferty]

I would suggest removing spaces from your sheet names - it can cause havoc with formulas since refs to the sheet need to be surrounded with apostrophes 'Trevors Laptop' and make it TrevorsLaptop and RobsLaptop

You can then use this formula to reference values:

=IF(A6<>"",INDIRECT(A6&"!E9"),"")

Hope this helps. If you want to insert sheet names automatically, let me know.

Tom

Live once die twice; live twice die once.

 

[ThomasLafferty]

Here's a little VBA to play with. It assumes that you will write sheet names to the A column in the first available blank cell. You will have to restructure it a bit:

Public Sub Workbook_NewSheet(ByVal Sh As Object)
'*****VARIABLE DECLARATIONS*****
Dim strSheetName As String

strSheetName = InputBox("Enter a new name for this sheet")
ActiveSheet.Name = strSheetName
Sheets("Control").Select
Range("A1").Select
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select
With ActiveCell
    .Value = strSheetName
End With
ActiveCell.Offset(0, 6).Select
ActiveCell.Formula = "=" & strSheetName & "!E9"
End Sub

Have fun!
Tom

Live once die twice; live twice die once.