Keeping cell range locked during Vlookup? Indirect function?

[iracknback]

I have the following formula in a workbook. it's used to determine if any lines/charges were added/removed from the previously ran report. my problem is that the macro i run deletes 6-8 lines in the report that the Vlookup uses and therefore changes the end range for the array i need. In reality the report only goes to cell AG but I made it $B:$ZZ so it could delete enough lines to not make me worry about it for awhile.

i know there is an easier way. i thought it was with the indirect function but i can't make it work. probably easy but looking for help. Thank you.


=IF(ROUND(A2,1)=(ROUND(VLOOKUP("TOT Direct Labor Shipwork",'WARR Actuals'!$B:$YH,12,FALSE),1)),"Yes","No")

[ben_hensel]

Back up, like, 30 steps.

Are you deleting rows or columns when you delete "lines"? Why are you doing that?

When you are "running a report", what does that mean? It almost sounds like "running a report" means "running a macro embedded in the spreadsheet".

[iracknback]

Ok, sorry. i'll clarify a bit. as much as i can at least. OPSEC here is pretty strict.

This is a tool i use to verify if a snapshot of our workplace in terms of what is being charged where is accurate and up to date. this "report card" we call it holds 97 "lines" or vessels and there is a vlookup on each one which pulls from the report i copy/paste into this workbook.

The "lines" are names of vessels my employer repairs. they are in rows. vessels come in and out fairly regularly so this is designed to check which area is missing a vlookup to add into the mix. There is only 1 column i really need that shows the actual "charges" if you will and it shifts each month as the calendar does. The report is ran independent of this workbook from a database. I copy/paste it into the workbook and run the macro which shifts the 4th column to the 1st (so i can use the vlookup - yes i know Index/match, this is easier), and deletes the extra blank columns.

Technically i don't have to delete the blank columns and would just have to change the col num on the vlookup to 17 or 18 instead of 12 but i know this is possible to do what i need.

Hope this helps. sadly i can't post a workbook without sanitizing so much it wouldn't be useful anyway. i'll look into trying though. Thanks.

[Jonmo1]

- yes i know Index/match, this is easier
...
...
Technically i don't have to delete the blank columns and would just have to change the col num on the vlookup to 17 or 18 instead of 12

If you use Index/Match you wouldn't have to do that. It would adjust automatically as columns are deleted/inserted/moved etc..

[protonLeah]

What do the column headers look like? What determines which column to look at?