[Solved] Military Passenger Manifest

[SgtIk3]

I built this spreadsheet to attempt to allow ease of building flight manifests. Inputting the individual's information plus their baggage weight on the input tab transfers over to the actual manifest. I used VLOOKUP to do this and had to reference specific cells, so each cell is specific. Unfortunately, this defeats the purpose because we are unable to delete personnel from the input tab without it causing a REF error on the manifest. I tried locking cells and referencing the table instead of individual cells, but when do that, it will only pull info from the input tab in ranges of 25 since that is the capacity of each page of the manifest.

Is there any way to update the formula on this manifest to where if you delete a row on the input tab, the info on the 2131 tab will simply move up and not give a REF error?

 

[shamimarita]

Hello SgtIk3,

To avoid REF errors when using this formula:

=VLOOKUP('Input Personnel Here'!A2,'Input Personnel Here'!A:K,1,FALSE)

You can switch to INDEX and MATCH will help create a dynamic solution that adjusts when rows are deleted. Here's how you can rewrite the formula:
=INDEX('Input Personnel Here'!A:A, MATCH('Input Personnel Here'!A2, 'Input Personnel Here'!A:A, 0))
This formula will search for the value in 'Input Personnel Here'!A2 and return the corresponding result from column A, dynamically adjusting even if rows are deleted.