If, Then Statement Needed?

[Jermyn Davidson]

I am creating a dbase and here is what I want to do. I want to be able to enter the name of any 1 of my 41 hospitals on one sheet (cell D2) and have the corresponding point of contact show up in cell G7 on any one of the hundreds of other sheets I will be creating, all part of the same massive workbook.

Let me try to be clearer.

sheet 1, cell D2 = ACME Medical Center

sheet 2 - sheet 300, cell G7 = Frank Meyers
sheet 2 - sheet 300, cell G8 = 407-555-1212
sheet 2 - sheet 300, cell G9 = 407-777-3434


sheet 1, cell D2 = Springfield Community Hospital

sheet 2 - sheet 300, cell G7 = Molly Thurston
sheet 2 - sheet 300, cell G8 = 407-248-1632
sheet 2 - sheet 300, cell G9 = 407-525-3500


sheet 1, cell D2 = Farm Valley Surgical Center

sheet 2 - sheet 300, cell G7 = Hanna Barbera
sheet 2 - sheet 300, cell G8 = 407-321-1234
sheet 2 - sheet 300, cell G9 = 407-888-7777


For sheet 1, cell D2, I have over 40 different variables (hospitals).


Is there anyone out there who undesrtands what I am trying to set up?

It is possible?

Will you PLEASE help me fix this?


Sincerely,
Jermyn Davidson

[Don]

Hi and welcome to the forum!

This is possible, but it can kind of be a pain. You could use a vlookup in sheet 2 to find the desired information based on the lookup value (hospital name) on sheet 1. Then, just create sheet 3 so that it links to the cells that return the data using the vlookup functions on sheet 2. Once you've done that and made sure that it works, just copy sheet 3 as many times as you need. That way, if you ever need to change the vlookup function, you will only have to change it on sheet 2 and not on every single sheet in the workbook.

That said, this is generally not very efficient. Why do you need so many sheets in one workbook?

[Jermyn Davidson]

Each one of the other sheets is a fax that is generated from the information entered on sheet 1.

I just spent the last few hours trying to tinker with Access, but I'm clueless there. I know a little bit about EXCEL and want to finish what I've started.

It would be real cool if you were still up!

I think I am going to need a walk thru on what you have prescribed, despite the fact that it is not very efficient.

[Ourpat1]

I am creating a dbase and here is what I want to do. I want to be able to enter the name of any 1 of my 41 hospitals on one sheet (cell D2) and have the corresponding point of contact show up in cell G7 on any one of the hundreds of other sheets I will be creating, all part of the same massive workbook.

Let me try to be clearer.

sheet 1, cell D2 = ACME Medical Center

sheet 2 - sheet 300, cell G7 = Frank Meyers
sheet 2 - sheet 300, cell G8 = 407-555-1212
sheet 2 - sheet 300, cell G9 = 407-777-3434


sheet 1, cell D2 = Springfield Community Hospital

sheet 2 - sheet 300, cell G7 = Molly Thurston
sheet 2 - sheet 300, cell G8 = 407-248-1632
sheet 2 - sheet 300, cell G9 = 407-525-3500


sheet 1, cell D2 = Farm Valley Surgical Center

sheet 2 - sheet 300, cell G7 = Hanna Barbera
sheet 2 - sheet 300, cell G8 = 407-321-1234
sheet 2 - sheet 300, cell G9 = 407-888-7777


For sheet 1, cell D2, I have over 40 different variables (hospitals).


Is there anyone out there who undesrtands what I am trying to set up?

It is possible?

Will you PLEASE help me fix this?


Sincerely,
Jermyn Davidson

Hi and welcome to the forum!

This is possible, but it can kind of be a pain. You could use a vlookup in sheet 2 to find the desired information based on the lookup value (hospital name) on sheet 1. Then, just create sheet 3 so that it links to the cells that return the data using the vlookup functions on sheet 2. Once you've done that and made sure that it works, just copy sheet 3 as many times as you need. That way, if you ever need to change the vlookup function, you will only have to change it on sheet 2 and not on every single sheet in the workbook.

That said, this is generally not very efficient. Why do you need so many sheets in one workbook?

Each one of the other sheets is a fax that is generated from the information entered on sheet 1.

I just spent the last few hours trying to tinker with Access, but I'm clueless there. I know a little bit about EXCEL and want to finish what I've started.

It would be real cool if you were still up!

I think I am going to need a walk thru on what you have prescribed, despite the fact that it is not very efficient.

[Ourpat1]

Too many sheets.......trust me.can be done with one Master sheet and a few macros or pivots

send me sample data

Pat Savage