please help save me some time!

[Guest]

Ok, I can't seem to figure this out. I have a large spreadsheet with 10
tabs. Tab 1 is for the entire company and tabs 2-10 are for each branch. I
have data from A to AD. On tabs 2 to 10 I have vlookup formulas that refer
back to tab 1 {=vlookup(A1,'totalcompany'!$1:!$6854,14,FALSE). My formulas
go as far as 26. My problem is that if I want to add a column on tab 1 in
column 4 (D) I then have to go to each column from D on for every other tab
and redo my formulas. Without rebuilding my spreadsheet is there a way
around this?

 

[Bob Phillips]

Brad,

Can't say I like this myself, but an idea for you.

Where you have a formula like

=VLOOKUP(A1,'totalcompany'!$1:!$6854,14,FALSE)

try replacing the offset by a sheet referencfe

=VLOOKUP(A1,'totalcompany'!$1:!$6854,COLUMN('totalcompany'!N$1),FALSE)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ken Wright]

On sheet 1 in an empty cell somewhere put the number 15 and name the cell
'colnum' (Without the quotes).

Now do Edit / Replace / Replace what = ',14,FALSE)' Replace with =
',colnum,FALSE)' (again without the quotes) - make sure you hit the options
button and change 'within sheet' to 'within workbook'

Now you can control which column you pull from on all your sheets by
changing that single cell.