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('total company'!N$1),FALSE)

--

HTH

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


"Brad" wrote in message
...
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" wrote in message
...
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?