lord_seagrave Posted February 16, 2015 Report Share Posted February 16, 2015 I'm so sorry to be trying to mooch yet more free advice, I promise I'll post something helpful one day. Anyway, I know what I want to do but I'm struggling to put it into words that Google understands... I've got a list on one sheet, and, on another sheet, cells that reference rows in that list. So on sheet "seats", seat 1 = the name in row 2 (I've got headings in row 1) on sheet "guests" OK? My problem is when that I start to move my data around on the "guests" sheet - the formula on the "seats" sheet follows the data around! So, if I drag the data on the "guests" sheet from row 2 to row 70, seat 1 now returns the data in row 70, not the new information I type in row 2. The formula for seat 1 has changed to reference row 70. I always want seat 1 to display the information in guest row 2. How do I fix this? LS Quote Link to comment Share on other sites More sharing options...
Spaniel Posted February 16, 2015 Report Share Posted February 16, 2015 Search the excel help file for consolidating data you should be able to create a master worksheet from separate sheets Quote Link to comment Share on other sites More sharing options...
spanj Posted February 16, 2015 Report Share Posted February 16, 2015 (edited) I'm so sorry to be trying to mooch yet more free advice, I promise I'll post something helpful one day. Anyway, I know what I want to do but I'm struggling to put it into words that Google understands... I've got a list on one sheet, and, on another sheet, cells that reference rows in that list. So on sheet "seats", seat 1 = the name in row 2 (I've got headings in row 1) on sheet "guests" OK? My problem is when that I start to move my data around on the "guests" sheet - the formula on the "seats" sheet follows the data around! So, if I drag the data on the "guests" sheet from row 2 to row 70, seat 1 now returns the data in row 70, not the new information I type in row 2. The formula for seat 1 has changed to reference row 70. I always want seat 1 to display the information in guest row 2. How do I fix this? LS put a $ in front of the row and / or column letter and no . So $A1 if you want to fix the data from row A but be aware that the column will advance if you want to fix the row and column then its $A$1 Edited February 16, 2015 by spanj Quote Link to comment Share on other sites More sharing options...
hollidca Posted February 16, 2015 Report Share Posted February 16, 2015 Instead of reference the cell in "seats", I would use a vlookup. So cell on the main sheet (where you want guest name to appear) =vlookup("range on seats sheet & use dollar signs",2,false) Quote Link to comment Share on other sites More sharing options...
casts_by_fly Posted February 16, 2015 Report Share Posted February 16, 2015 If i am reading you right, you are creating a relative reference but want an absolute reference. As spanj says, a dollar sign will fix it, depending if you want to lock the row or column or both. Some reading: https://support.office.microsoft.com/en-gb/article/Switch-between-relative-absolute-and-mixed-references-538396b3-990e-4b44-9d9c-28b4151d7d21?CorrelationId=82a68bba-1291-4bbe-9529-514b59546b6c&ui=en-US&rs=en-GB&ad=GB Rick Quote Link to comment Share on other sites More sharing options...
lord_seagrave Posted February 16, 2015 Author Report Share Posted February 16, 2015 That's the stuff - a vlookup here and a dollar sign there and it's starting to look like it's working! Thanks guys! LS Quote Link to comment Share on other sites More sharing options...
amateur Posted February 17, 2015 Report Share Posted February 17, 2015 You can also introduce the $ signs in one fell swoop by clicking on the F4 button when you are editing the cell. As you press F4 successively, this will then toggle between complete cell reference, row reference, column reference and no reference. eg $C$1, C$1, $C1 and C1 Quote Link to comment Share on other sites More sharing options...
lord_seagrave Posted February 17, 2015 Author Report Share Posted February 17, 2015 Amazing! Thanks fella Quote Link to comment Share on other sites More sharing options...
amateur Posted February 17, 2015 Report Share Posted February 17, 2015 (edited) Blimey Duncan, Is the little master keeping you up? 1.45am indeed - I thought it was only me who is the insomniac Edited February 17, 2015 by amateur Quote Link to comment Share on other sites More sharing options...
lord_seagrave Posted February 18, 2015 Author Report Share Posted February 18, 2015 Hah! Wife and child are presently in sunnier climes, the only thing keeping me awake in the middle of the night is Excel! Er, and my refusal to pay £50 for table-planning software. :( LS Quote Link to comment Share on other sites More sharing options...
keg Posted February 19, 2015 Report Share Posted February 19, 2015 You can also introduce the $ signs in one fell swoop by clicking on the F4 button when you are editing the cell. As you press F4 successively, this will then toggle between complete cell reference, row reference, column reference and no reference. eg $C$1, C$1, $C1 and C1 Thanks for the tip, been using it for years and didn't know that. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.