Indirect & offset formulae - Urgent Options

[Arlette]

I have my main worksheet which needs to take in the values from a
fixed column from every tab. So as i keep adding a tab every week,
the
data should flow into the appropriate cell on the main tab. How do i
do this using the indirect & offset formulae? Its pretty urgent and i
am stuck as i havent used these formulae before. Is there any other
method other than using the offset and indirect?

 

[Dennis Montgmery]

sorry I dont have an answer for your question, but perhaps you have one for
mine. How do you do a fixed column so that the rest of the spread sheet goes
underit, or to where you can still see it when moving to the right of the
spread sheet?

 

[Dave]

Hi Arlette,
Not sure what's happened to your thread, but it seems to have some rather
mixed responses.
Anyway, one of your problems is that you "keep adding a tab every week."
You can't refer to a tab that doesn't exist yet, so you can't prepare your
main worksheet in advance. You have to enter the formulas that refer to the
wanted cells each time you create a new sheet.
One way around this is to create a lot of new sheets in advance, filling
them in each week as normal.
Once the new sheets are created, you can enter your formulas into your main
sheet, even though their reference cells are currently empty.
Is this anything like what you want?
Regards - Dave.

 

[Ron Coderre]

Can you post the forumulas you've tried...even if they didn't work?

Meanwhile....If you need to sum the same cell on each sheet
eg. Sheet3!C2, Sheet4!C2, etc

Try something like this:
Assuming the SummarySheet is before all of the others...

Insert a blank sheet, named "LeftSheet" after the SummarySheet.
Insert a blank sheet, named "RightSheet" after the last Week sheet.
(Now the data sheets are between LeftSheet and RightSheet.)

Next, on the SummarySheet...
This formula sums cell C2 on all of the
sheets between LeftSheet and RightSheet:
C2: =SUM(LeftSheet:RightSheet!C2)

Copy that formula wherever you need that kind of formula.

To tidy up a bit, hide LeftSheet and RightSheet.

Is that something you can work with?
Post back if you have more questions.

Regards,

Ron
Microsoft MVP - Excel

 

[Dave]

Hey Dennis,
This is the second time I've noticed you've asked a totally unrelated
question within an existing thread.
How about starting a new thread? You're more likely to get your questions
answered.
Regards - Dave