I need some help in Excel guys...

[warcrow]

Here is the scenario:

I need one sheet to be able to act like a database of information, while another sheet is were all the new data (new data will be posted in it everyday whil the 2nd sheet stays static).

I need to be able to link one sheet to another (you know how you can have multiple sheets in one book?). So, say:

sheet 1 - One column = state, second column = county, third column = X

sheet 2 - One column = state, second column = county, third colum will fill in X above

I hope this makes sense, sorry if it's confusing. I'm not very good with excel, so it's very appreciated.


Thanks in advanced guys! :beer:

 

[BlueWeasel]

So you just setting up a cell reference that pulls the appropriate data from one sheet to the other? It seems like all you need is a simple formula...unless I'm confused.

Assume that "Red Sheet" and "Blue Sheet" are the true sheet titles. Then to reference a cell or range of cells in Blue from Red, the formula would be:

='Red Sheet'!A1 ....where A1 is the particular cell or range of cells that you want to pull into the second sheet.

If it makes a difference, you could just email me an example spreadsheet with fictional data.

 

[warcrow]

Originally posted by: BlueWeasel
So you just setting up a cell reference that pulls the appropriate data from one sheet to the other? It seems like all you need is a simple formula...unless I'm confused.

Assume that "Red Sheet" and "Blue Sheet" are the true sheet titles. Then to reference a cell or range of cells in Blue from Red, the formula would be:

='Red Sheet'!A1 ....where A1 is the particular cell or range of cells that you want to pull into the second sheet.

If it makes a difference, you could just email me an example spreadsheet with fictional data.

Let me try that out, thanks BW!

 

[NeoV]

War, are you saying that the data entry you do for the 'new' data would always be in the same row? I think the solution is a bit more complex than what BW said - his example is going to make an exact copy of sheet 1 on sheet 2. You may need some VB for this

Sub Append_Data()
Dim maxrow As Long
maxrow = Application.WorksheetFunction.Max(Range("A65536").end(xlUp).Row, Range("B65536").end(xlup).Row, Range("C65536").End(xlup).Row)
Sheets("Original").Range("A2:C2").Copy Sheets("Destination").Range("A" & maxRow + 1)
End Sub

You would need to change the "original" to whatever the name is of your data entry sheet, in this case sheet 1, and change "destination" to sheet 2.

I'm assuming you clear the data off of the new page every time too.

PM me if you need help!

 

[warcrow]

Originally posted by: NeoV
War, are you saying that the data entry you do for the 'new' data would always be in the same row? I think the solution is a bit more complex than what BW said - his example is going to make an exact copy of sheet 1 on sheet 2. You may need some VB for this

Sub Append_Data()
Dim maxrow As Long
maxrow = Application.WorksheetFunction.Max(Range("A65536").end(xlUp).Row, Range("B65536").end(xlup).Row, Range("C65536").End(xlup).Row)
Sheets("Original").Range("A2:C2").Copy Sheets("Destination").Range("A" & maxRow + 1)
End Sub

You would need to change the "original" to whatever the name is of your data entry sheet, in this case sheet 1, and change "destination" to sheet 2.

I'm assuming you clear the data off of the new page every time too.

PM me if you need help!

Do I jsut cut and paste that info excel, then modify it?

 

[warcrow]

Originally posted by: BlueWeasel
So you just setting up a cell reference that pulls the appropriate data from one sheet to the other? It seems like all you need is a simple formula...unless I'm confused.

Assume that "Red Sheet" and "Blue Sheet" are the true sheet titles. Then to reference a cell or range of cells in Blue from Red, the formula would be:

='Red Sheet'!A1 ....where A1 is the particular cell or range of cells that you want to pull into the second sheet.

If it makes a difference, you could just email me an example spreadsheet with fictional data.

Well, I need the first sheet to "find" the right entry (IE: a column would have all of the states in it, so if the first sheet has California listed, it needs to check the whole column of "states", find california, and then find the info in the field next to it (call phone # xxx-xxxx) and feed it back to sheet 1) from the second sheet.

 

[Traire]

So you want sheet one to just be a form sheet that looks up data from the other sheet?

A little more explanation on what you want thes thing to do would help.

 

[BlueWeasel]

Would there only be one phone number for each state? If so, then it sounds like the VLOOKUP (vertical lookup) function would work.

 

[warcrow]

Ok, let me explain this more toroughly, sorry:

User get a spreadsheet emailed to her every morning. This sheet has a bunch of title and escro information in it for each state and almost each county (not country). I need to find a way to populate this sheet (there are roughly 3000 rows btw) with how they can contact the county and get more information. The choices are:

- Phone
- Website
- Phone and Website
- Other

So, I need one of those 4 options to automatically list which way that can contact someone. Make more sense?

 

[Concillian]

Okay I've done something like you want to do, but it's been a while.

I think I did something like
read in the information from one cell into a variable
do a compare on each cell in the appropriate column until it finds a match
Then copy read the data from the cell next to it and put that information in the appropraite cell of the working sheet

Basically :

===========

dim CompareVar as string
dim Contact as string
dim i as integer

i = 0

[C4].select
'or whatever is the appropriate cell that has the state/county info you need

CompareVar = activecell.value

sheets("database sheet name").select
[C4].select
' or whatever the top cell is in the column you need to match the info in

do until ComparVar = activecell.offset(i,0).value
i = i + 1
loop

contact = activecell.offset(i,1).value
' this assumes the contact info is one column right of the state or county info, if it's 2 columns right change 1 to 2, if it's one cloumn left, change 1 to -1, etc...

sheets("working sheet").select
[C5].value = Contact

==========

That's what I came up with off the top of my head. It can probably be improved upon, or done in a better way. There is always a better way than I usually come up with for a macro, I just never know the built-in functions well enough, and it's easier for me to make a macro like this in 20 minutes, than trying to lookup what specific built-in function does the same thing for me and learn the syntax.
If you want the user to NOT see the sheets switching, then right around i = 0 add the function:
application.screenupdating = false

then, of course add a statement with it true right before the End Sub.
disabling screenupdating will also make the macro marginally faster, but in a simple macro like this, it shouldn't make a noticeable difference.

 

[KLin]

Can you email a sample of the data that you're working with, or is the data confidential?

 

[warcrow]

Originally posted by: KLin
Can you email a sample of the data that you're working with, or is the data confidential?

It's confidential. :-/

 

[warcrow]

Originally posted by: Concillian
Okay I've done something like you want to do, but it's been a while.

I think I did something like
read in the information from one cell into a variable
do a compare on each cell in the appropriate column until it finds a match
Then copy read the data from the cell next to it and put that information in the appropraite cell of the working sheet

Basically :

===========

dim CompareVar as string
dim Contact as string
dim i as integer

i = 0

[C4].select
'or whatever is the appropriate cell that has the state/county info you need

CompareVar = activecell.value

sheets("database sheet name").select
[C4].select
' or whatever the top cell is in the column you need to match the info in

do until ComparVar = activecell.offset(i,0).value
i = i + 1
loop

contact = activecell.offset(i,1).value
' this assumes the contact info is one column right of the state or county info, if it's 2 columns right change 1 to 2, if it's one cloumn left, change 1 to -1, etc...

sheets("working sheet").select
[C5].value = Contact

==========

That's what I came up with off the top of my head. It can probably be improved upon, or done in a better way. There is always a better way than I usually come up with for a macro, I just never know the built-in functions well enough, and it's easier for me to make a macro like this in 20 minutes, than trying to lookup what specific built-in function does the same thing for me and learn the syntax.
If you want the user to NOT see the sheets switching, then right around i = 0 add the function:
application.screenupdating = false

then, of course add a statement with it true right before the End Sub.
disabling screenupdating will also make the macro marginally faster, but in a simple macro like this, it shouldn't make a noticeable difference.

Nice work! So, how do I impliment this? just copy into a cell? (Excel newb, sorry)

 

[KLin]

Having the reference data in microsoft access, and creating an import function for the other data into access would make it a lot easier than in excel. But that's just my opinion.

 

[warcrow]

Originally posted by: KLin
Having the reference data in microsoft access, and creating an import function for the other data into access would make it a lot easier than in excel. But that's just my opinion.

I would like to just stick with using excel. I don't know Access.

 

[Concillian]

That's a macro

In excel hit Alt-F11 to bring up the VB interface. This is where you can do things with macros.
Make a new macro by choosing Tools --> Macro --> Record new macro
then do something like enter 5 in a cell or something
Hit stop
In the modules for the VB interface, you will see Module 1 in the workbook you're in. Select that and you wil see the macro you made.
Delete what's in between the comments and 'End Sub' and replace it with what I wrote.
Change the specific references to suit your needs

To execute macros, you go Tools --> Macro --> Macros...
Or you can make a button on the worksheet that the user can hit when he/she wants to run the macro.

 

[Concillian]

Meh, I know there was an easier way:

vlookup function in excel:
=VLOOKUP(X,Y:Z,#,FALSE)
X= value to match
Y:Z = range of the table to lookup in
# = number of columns to the right to report the data
False = make an exact match of X

This requires the table on the other page to have the lookup value in the leftmost column.
It will look for the value in column one, then move over (#-1) rows and report that value back to the cell.

So if your 'database' page is set up so the state/county is left of the contact info you're set.
With Y:Z being on a different worksheet, it will have to be
sheetname!C4:E3000
or whatever the appropriate range is

 

[warcrow]

Originally posted by: Concillian
Meh, I know there was an easier way:

vlookup function in excel:
=VLOOKUP(X,Y:Z,#,FALSE)
X= value to match
Y:Z = range of the table to lookup in
# = number of columns to the right to report the data
False = make an exact match of X

This requires the table on the other page to have the lookup value in the leftmost column.
It will look for the value in column one, then move over (#-1) rows and report that value back to the cell.

So if your 'database' page is set up so the state/county is left of the contact info you're set.
With Y:Z being on a different worksheet, it will have to be
sheetname!C4:E3000
or whatever the appropriate range is

Thank you so much Concillian, let me tackle this later on today when I have some time and see if I can configure to work right.