How to make column constant / fix during cell reference on same sheet or from another sheet.
The user of MS Excel can use '$' before the column name or press 'f4' symbol after choosing the referenced cell on the same sheet or another sheet in order to make the column constant or fix.
Below example explain clearly the use of '$' sign before the column.
| TRAIN TIME TABLE | ||||||
| Colum Index---> | 1 | 2 | 3 | 4 | 5 | 6 |
| Serail No | Train No | Train Name | Departure Time | Destination Time | Source Station | Destination Station |
| 1 | 100025 | KOU_RAM | 10:30:00 | 01:30:00 | KHURDA | RAMPUR |
| 2 | 250035 | NAM_TAN | 11:45:00 | 01:35:00 | NIZAMUDIN | MUMBAI |
| 3 | 346895 | KSM_KUR | 02:36:00 | 02:36:00 | SAMSABAD | TITLAGARH |
| 4 | 282763 | BBS_NAT | 23:36:00 | 13:43:00 | BHUBANESWAR | DELHI |
| 5 | 366985 | VZM_OPR | 22:16:00 | 02:06:00 | VIZAYANAGARAM | ROURKELLA |
| 6 | 374215 | KOL_JTN | 09:48:00 | 19:32:00 | KOLKATTA | BHADRAK |
| 7 | 651846 | DRG_RAI | 01:45:00 | 06:45:35 | DURG | RAIPUR |
| 8 | 958485 | RAI:KOU | 08:45:00 | 01:02:00 | RAIPUR | KHOURDA |
| 9 | 354854 | CHN_HYD | 06:30:00 | 01:45:00 | CHENNAI | HYDRABAD |
| 10 | 678125 | HYD_ROU | 02:30:00 | 02:36:00 | HYDRABAD | ROURKELLA |
| 11 | 625789 | ROU_DAM | 12:30:00 | 04:25:00 | RAOURKELLA | DAMONJODI |
| 12 | 624856 | DAM_KOR | 02:15:00 | 04:25:00 | DAMONJODI | KORAPUT |
| 13 | 126986 | KOR_MAL | 03:16:00 | 12:30:00 | KORAPUT | MALKANGIRI |
| 14 | 365986 | MAL_JBL | 01:02:00 | 06:30:00 | MALKANGIRI | JABALPUR |
| 15 | 145254 | JBL_SAM | 00:25:00 | 23:36:00 | JABALPUR | SAMBALPUR |
| 16 | 236859 | SAM_CHA | 04:25:00 | 04:25:00 | SAMBALPUR | CHATTISGARH |
By using the formula the use can get the 1st column data from the reference cell value and if drag to below cell the user can column values from the next row value of the same column.
In the VLookup() function to fix the array we use the '$' sign. Its use is frequent and the users of excel always used to lock the cell array from where the user want to search for the value.
In the below example shows the use of VLookup() function to get the destination time of the train where the train search column is fixed and row is variable mean when we drag the formula to below cells the column value remain fix or constant its will not change only the row value changes from next below rows. And here we are referencing the search in the "sheet2(2)' whose data are in the range from B4 to G19 and to make this fixed for the below rows and need not to change while dragging the formula we are using the $ symbol before the column B and 4th row which is '$B$4'.Similarly we use before the column name 'B' and row '19' which looks like this '$B$19'.The reference of array from '$B$4' to '$B$19' sheet named 'Sheet2(2)' looks like this in the formula as 'Sheet2 (2)'!$B$4' to '$B$19.The Destination time of train no is on third column i.e. '3' so the formula becomes =+VLOOKUP($C23,'Sheet2 (2)'!$B$4:$G$19,3,0) or =VLOOKUP($C23,'Sheet2 (2)'!$B$4:$G$19,3,0) .In the below example we have given example of both use of VLookup() and the cell reference for users referance and study and to make my blog useful for excel user.
Comments