please help save me some time!

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?

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)


"Brad" <[email protected]> wrote in message
news:[email protected]...
> 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?

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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Brad" <[email protected]> wrote in message
news:[email protected]...
> 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?