Log in

View Full Version : How can I connect one sheet 1 in excel to sheet 2 so it will update the information.


rbutler6
Nov 16, 2009, 04:02 PM
What I am trying to do is I have a schedule with drop down boxes I want to beable to click on a name in the dropdown box next to a time slot for appointments which are listed under the days date like a calendar, but once clicked I need it to update the list on the first sheet next to the same name that was clicked. So that the name will show the current appointment that was just scheduled. Then I need to attach the calendar to the switchboard that I have in my access database so that I have a button to open the excel appointment schedule with. I think that explains it :confused: so if any one has any idea on how to connect the two sheets in excel by updating I would appreciate it then I can work on my other problem, switchboard:eek:

majika
Nov 16, 2009, 04:41 PM
go to the cell that you want to link to the cell from the other sheet and type =
then go to the other sheet and select the cell you want it to relate to then press enter. It now points to that sheet.

Nope this helps

JBeaucaire
Nov 16, 2009, 11:32 PM
Post up your workbook showing the sheet where you want your Daily calendar and the sheet where you're doing the drop boxes to select appointments. I'm sure can get them connected in a way that's usable.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

rbutler6
Nov 17, 2009, 08:22 AM
I don't see the GO ADVANCED or a paper clip, where would it be.

rbutler6
Nov 17, 2009, 08:41 AM
Post up your workbook showing the sheet where you want your Daily calendar and the sheet where you're doing the drop boxes to select appointments. I'm sure can get them connected in a way that's usable.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

I hope I am doing this right:confused: but I found the paper clip am attaching the file please let me know what I am doing wrong. I have worked on this for a while and still am confused.

Thank you even if you can't help, I will have to start over if I can't figure it out.

JBeaucaire
Nov 17, 2009, 04:48 PM
PATIENT NAMES

I added a dynamic named range called Patients that will expand itself automatically as you add names to column E of the Patients List sheet.

I removed the unfriendly and hard to maintain combo boxes and replaced them with simple in-cell drop down validation lists that use the same dynamic named range created above. Those validation lists are now in columns B, D, etc.

======
FORMULAS IN PATIENTS LIST

(Thanks to DonKeyOte from ExcelForum.com for providing 99% of this part.)

The array formula in O2 is:
=IF(OR($E2="",COUNTIF(Scheduler!$B$3:$P$26,$E2&"")=0),"",INDEX(Scheduler!$A$1:$O$1,MIN(IF(Scheduler!$B$3:$ P$26=$E2,COLUMN($A$3:$O$23)))))

This formula is confirmed by pressing CTRL-SHIFT-ENTER. You'll see curly braces {} appear around your formula indicating the array is active in that cell.

This formula will find the FIRST appointment for the name in that row in the Scheduler columns and show you the date.

In P2, this regular formula provides the matching time:
=IF(O2="","",INDEX(Scheduler!$1:$100,MATCH(E2,INDEX(Scheduler! $1:$100,0,MATCH(O2,Scheduler!$1:$1,0)+1),0),MATCH( O2,Scheduler!$1:$1,0)))

rbutler6
Nov 17, 2009, 06:20 PM
PATIENT NAMES

I added a dynamic named range called Patients that will expand itself automatically as you add names to column E of the Patients List sheet.

I removed the unfriendly and hard to maintain combo boxes and replaced them with simple in-cell drop down validation lists that use the same dynamic named range created above. Those validation lists are now in columns B, D, etc.

======
FORMULAS IN PATIENTS LIST

(Thanks to DonKeyOte from ExcelForum.com for providing 99% of this part.)

The array formula in O2 is:
=IF(OR($E2="",COUNTIF(Scheduler!$B$3:$P$26,$E2&"")=0),"",INDEX(Scheduler!$A$1:$O$1,MIN(IF(Scheduler!$B$3:$ P$26=$E2,COLUMN($A$3:$O$23)))))

This formula is confirmed by pressing CTRL-SHIFT-ENTER. You'll see curly braces {} appear around your formula indicating the array is active in that cell.

This formula will find the FIRST appointment for the name in that row in the Scheduler columns and show you the date.

In P2, this regular formula provides the matching time:
=IF(O2="","",INDEX(Scheduler!$1:$100,MATCH(E2,INDEX(Scheduler! $1:$100,0,MATCH(O2,Scheduler!$1:$1,0)+1),0),MATCH( O2,Scheduler!$1:$1,0)))

Thank you so much, I have been working on this for 3 weeks and still not done. If you know where I can find out how to connect this to a switchboard in Access so that it will open when button is clicked please let me know. That is I think one of the last things I have to do.

Thank you so much
Rose:D:p;):)

JBeaucaire
Nov 17, 2009, 09:16 PM
No, I don't use Access. Sorry.

But there are many good Access forums out there:

Access Tables & Databases - Excel Help Forum (http://www.excelforum.com/access-tables-and-databases/)

Microsoft Access - MrExcel Message Board (http://www.mrexcel.com/forum/forumdisplay.php?f=18)