#1
|
|||
|
|||
auto cell populate
I have a question re auto populating sells on different sheets.
I've attached a rough example of what I'm trying to do and I guess some form of VBA/macro will be needed here is what I'm trying to do
e.g. Bert is in rm 3, cell D2 in the room layout sheet, if I change his room number on the front sheet to rm 9, his name will move to H4 on the room layout Is this possible? |
#2
|
|||
|
|||
Hi Steve
See attached solution. Basically I have used VLOOKUP functions to check what name is in Column C of the "Front" sheet and put that name in the room box on the next sheet. In addition I have put a small macro that recalculates the workbook when anything changes on the "Front Sheet". The code is: Private Sub Worksheet_Change(ByVal Target As Range) Application.Calculation = xlManual For Each sh In ActiveWorkbook.Sheets sh.Calculate Next sh End Sub Instead of changing the room numbers, leave them static and just type the name of the person using each room against the relevant room number. You can view this code by right clicking on the Front sheet tab and selecting view code. Hope this helps. If you need anything else then come back to the forum. Good luck. Tony |
#3
|
|||
|
|||
Hi Tony
You have helped me on a first incarnation of this project which has linked data and formulas. I've only included the first 2 columns for the test, the working document I want to add this to has additional data from cols D onwards. In reality to save having to move significant amounts of data from specific rows and other linked pages, it's the room numbers that need to change |
#4
|
|||
|
|||
Hi Steve
In that case the solution I gave you will still work. However, for example, if Mary is in Room 2 and you change James to Room 2 on row 10 it will only pick up the first occupant of Room 2 which may not be correct. hope this makes sense. If you need any further help I would be glad to help. Good luck. Tony |
#5
|
|||
|
|||
I think I've got the gist of this.
tomorrow I'll copy and post a blank of the original workbook to show how I want to integrate this. Once I had got my head around the original work you did for me it was easy to expand on it, this is a little beyond me for now, not too strong on macros/vba. I've found this site to be a valuable resource for this project |
#6
|
|||
|
|||
Here is the template of the document I'm using, as previously mentioned I want to link the names from the front sheet to the room numbers on the occupancy page. The room numbers can change from day to day, so I need changes made to room Nos to reflect in who is in what room on the occupancy page
I thought I'd got Tony's method fixed in my head but no, it went down the plughole when I tried to transpose his test.xlsm to this workbook |
#7
|
|||
|
|||
Hi Steve
I have just done Patient 1 only. Let me know if this is what you need. Please bear in mind that on Patient 1 you had a space after the "1" which would prevent VLookup from working. Also if you dont want anyone to change data in column I, suggest you protect that column so no changes can be made. Good luck. Tony Last edited by OTPM; 05-02-2013 at 03:30 AM. |
#8
|
|||
|
|||
It doesn't appear to have populated the Room Layout (room occupants tab) page,
When you say a space after the '1', not sure where you mean Col 1 is locked |
#9
|
|||
|
|||
Hi Steve
I am currently working on your solution and will post it once I have finished. Tony |
#10
|
|||
|
|||
Hi Steve
Please find herewith the update Workbook. I have taken the liberty of adding a small piece of code that checks if a room number is already in use on the FRONT SHEET and blank the cell if it is until you correct the error. If you need any further help then please let me know. Good luck. Tony |
#11
|
|||
|
|||
Hi
Had a look and still can't seem to get it to work. I guessed that if you clear a cell from the front page it will clear the corresponding occupancy cell, but it doesn't seem to want to do that, similarly, when you change room numbers it doesn't make any changes. Am I doing something wrong? |
#12
|
|||
|
|||
Hi Steve
Make sure you have automatic calculation turned on for the WHOLE workbook. I believe the Room Occupants Sheet is set to Manual calculation, that is why it is not updating when you change the FRONT SHEET. Tony Last edited by OTPM; 05-04-2013 at 04:09 AM. |
#13
|
|||
|
|||
Hi Tony, that fixed it
Now I have to try and work out how you did it in case one of the users manages to mess it up and I need to make repairs Many thanks Steve |
#14
|
|||
|
|||
Hi Tony
That works a treat. If at some point I need to change the Occupancy layout at a future date, would applying the formula =IF(ISERROR(VLOOKUP(C3,'FRONT SHEET'!$C$5:$D$25,2,FALSE)),"Empty",VLOOKUP(C3,'FR ONT SHEET'!$C$5:$D$25,2,FALSE)) and changing the cell details be sufficient. The reason I ask is to a) grasp what you actually did, to learn from you b) a colleague is most likely to ask me to set the same up for the ward next door which is of a different floor plan |
#15
|
|||
|
|||
Hi Steve
Check your private messages. Tony |
Tags |
auto, cell |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Populate Data to another cell | Mrkieth | Excel | 2 | 04-29-2013 07:30 AM |
how do I auto-populate a word template | Greenhorn | Word | 4 | 09-05-2012 04:16 PM |
Auto populate text | help i'm stuck! | Word | 1 | 08-13-2010 11:52 AM |
auto populate to a PDF file | jerrymbna22 | Excel | 0 | 07-12-2010 02:19 PM |
Auto-populate an MS Word table cell with text from a diff cell? | dreamrthts | Word Tables | 0 | 03-20-2009 01:49 PM |