3 sheets. Solution= VBA/Macros/something - I have a headache


Posted by George on August 29, 2001 2:59 PM

I have put together a workbook which was working fine. As I am self-taught in Excel it was never going to be fancy or complicated. So I thought.

The workbook calculates data for Scotland with various arrays, and IF statements all over the place. I now have to do the same for England and London. Each location has the same data-type, but the values differ for each location. I have made up 3 sheets for the 3 locations with the various data in the same corresponding cell.
ie B2=limit for each of the locations.
E14=2000/01 rate for each location.

1. I would like to be able to set one of these sheets as the default so that the data on that sheet can be used by the formulas. e.g If default = London, then the main sheet with the equations will use the London data.

I will probably have to alter the equations as well so that it reads from the default page. How?

BUT

2. As the main sheet with all the equations is set out as a report, I will need to sometimes change the default from one location to another.
I originally tried to make buttons so that the spreadsheet would use the stated default unless one of the buttons was pressed. I then got a headache. I know nothing of VBA or Macros, but I think these are the only options I have for achieving the above.

If you can suggest anything to help (I have asprin), I would be really really grateful; or any advice on how else I can go about tackling this.

Thanks in advance
George

Posted by Iain on August 30, 2001 1:13 PM

Just use 3 seperate workbooks

I am not sure if this is possible. The best solution is probably to have 3 workbooks and forget about buttons or checkboxes.



Posted by George on August 31, 2001 2:37 AM

IS THIS MY ONLY OPTION??

I have searched the archives and while it seems that using check boxes to alter which sheet is used to get the data for the formulas, I don't know how. 3 Seperate Reports is what I am currwntly using, but the layout is identical in each; the only difference is the data hed in Scotland, England & London Sheets.

Can anyone help or suggest alternatives?