Cell reference to previous sheet.

[Draughtsman]

Dunno if that made sense the title.

We have a sizeable Excel workbook that contains many worksheets for various things, everything works smoothly except for one minor niggle.

I am looking for a formula that references to a cell on a previous worksheet, the worksheet could be named anything (eg A1, B, 2, 3.4, etc). Then to add 1, ideally the first worksheet inserted must equal, say, 0 BUT can reference the sheet previous as this is a constant; so something along the lines of...

Cell A1 =magic formula
Cell A2 =A1+1

This is to get a vlookup to work consistently.

OR another option is, for the worksheet to reference an already populated worksheet and read down a table by adding one to reference the cells. Eg first sheet reads ='schedule'!A13, the next sheet added would read A14 then A15 etc...

OR a worksheet (like page numbers) count into a cell onto each worksheet inserted, eg on worksheet 6 in cell A1 the number 6 would be inserted then worksheet 7 would have 7 inserted into A1 etc..

I hope this makes some sense, we have quite a complex workbook and this is the final hurdle to overcome.

[NBVC]

I am not sure I follow, but I think you might benefit from the INDIRECT function

Have a read through these articles to see if it might suit your needs:

http://www.contextures.com/xlFunctions05.html

http://www.cpearson.com/excel/indirect.htm

[Draughtsman]

I am not sure I follow, but I think you might benefit from the INDIRECT function

Have a read through these articles to see if it might suit your needs:

http://www.contextures.com/xlFunctions05.html

http://www.cpearson.com/excel/indirect.htm

Cheers for the advice, I am trying to get my head around the INDIRECT function as we already have this within our document.

And I will try explaining again...

As a new worksheet is added (via a macro function) a cell needs to equal the value on a table on a separate worksheet, currently this is done via a VLOOKUP but due to the reference being so random (eg A, 1, A.1 2.A etc) the VLOOKUP can't handle this variation.

So I am looking for a formula (rather than another macro) that can create a cell reference by looking back at the previous sheet inserted, so it will go down a table. EG first sheet will reference cell A13 then the next sheet inserted looks back to the previous sheet and creates the reference for A14 and so on.

[NBVC]

Still not totally grasping it...

Perhaps have a read through this article... and see if it might help. It gives you a way to refer to previous sheets (without directly using VBA).....

http://www.d i c k s-blog.com/archives/2004/12/02/referring-to-previous-sheet/ (replaces the spaces in the word after the www.)

[Draughtsman]

Sorry its really hard to describe what I am after.

I used the INDIRECT formula on the Template sheet (the sheet to be inserted), roughly speaking this what I have come up with...

=INDIRECT("'" & K1 & "'!" & K2)

Where K1 references the 'Schedule' sheet (this sheet contains the data from which the new sheets reference to and vice versa) and K2 references the cell that needs the data extracting from. So that bit works, the next challenge i;s as new sheets are inserted this cell reference needs to increase eg...

if K1=schedule and K2=B13 how do I get K2 to increase by 1 every time a new sheet is added therefore when a new sheet is added this cell (K2) on the new sheet will equal B14 and so on.

Cheers for the help so far!

[NBVC]

Without VBA, Excel doesn't really "know" when you insert a new sheet, so probably the easiest thing would be to have a list of sheets somewhere in the main sheet, and then use something like:


=INDIRECT("'" & K1 & "'!K" & COUNTA(X:X)+1)

where X:X is column X and is has the list of sheets to count to determine at what cell in column K to extract.

so if you have one sheetname (Service) in X1, then the result of the formula is


=INDIRECT("'" & K1 & "'!K" & 2)

if you a sheetname to column X, then you get



=INDIRECT("'" & K1 & "'!K" & 3)

etc.

Does that work?

[Draughtsman]

Hi been awhile, not had a chance recently to get on.

Right then this is the VBA we currently use to insert a new worksheet using a template within the workbook.

Please Login or Register  to view this content.

If something can be added to this along the lines of, say, cell K1 on each new worksheet inserted is plus 1 of the previous.

Eg worksheet A cell K1=1 then worksheet B cell K1="worksheet 'whatever the name might be' K1"+1 therefore we would have 2 shown in K11 then worksheet C would be 3 etc etc.

The worksheets are named via a table on the Schedule sheet.

I hope this makes some more sense.

[Draughtsman]

Can anyone help or shall I give up hope?

[snb]

Don't despair.
I think this macro does the same job as yours.

Please Login or Register  to view this content.

It would be nice to have an example sheet of one of your 'templates' here.

[Mayweed]

Do you need the incremented number to stay the same if the sheet is moved in position, or don't you ever move the sheets?

[Marcol]

Not sure if this will help, but this thread, from Post #12 on might give you some ideas
http://www.excelforum.com/excel-gene...-workbook.html

It produces a table in sheet "LargeTable" sorted by the position of the sheet position in the workbook, using a combination of native formulae and VBa. Drag a sheet to a new position in the workbook to see the changes.

The last file posted is a bit over ambitious but perhaps the earlier posts might help.

[Draughtsman]

Cheers for the replies, I would have to edit the document to make it suitable to publish online.

Basically the workbook contains 'static' worksheets that don't move of change. We then have a schedule worksheet that is a costing sheet that totals up the inserted worksheets. The inserted worksheets take reference points from the schedule when they're inserted, ie plots and types. The type references are using that formula that looks at the worksheet tab name, then the plots use a VLOOKUP using the type as it's reference to lookup the plots BUT this where the problem lies, if the type is not a simple letter or number we get a #VALUE error.

The way I see it is in the VBA I posted (which works for inserting our new worksheets) needs to contain a way of referencing back to the previous worksheet to add 1 (for example) so the VLOOKUP has something to reference to if we added an extra column to our schedule worksheet that is numbered 1, 2, 3 etc..

Is this better explained or just worse?

[Mayweed]

Do I understand this correctly?
Your current sheet has a value in cell K1 (let's say 19)
Run your current macro to create a new sheet and K1 in the new sheet is now 20
The value of 20 is calculated by the macro referring to cell K1 of the previous sheet and adding 1 to it.
This happens every time you run the macro; so each new sheet has the previous sheets K1 value incremented by 1.

[Draughtsman]

Do I understand this correctly?
Your current sheet has a value in cell K1 (let's say 19)
Run your current macro to create a new sheet and K1 in the new sheet is now 20
The value of 20 is calculated by the macro referring to cell K1 of the previous sheet and adding 1 to it.
This happens every time you run the macro; so each new sheet has the previous sheets K1 value incremented by 1.

Basically, yes thats it.

We can have up to 25 new worksheets inserted at once, hence the need for this plot number reference to work.

So in theory when the first new sheet is inserted the marco inserts a number into cell K1, but if its simpler K1 could be filled in on a sheet previous to the first new sheet as it's always there if that makes sense).

The way I see it is something along the lines of... =IF('previous sheet'K1="", 1,k1+1)

But I don't know the code to make this work if it doesn't know what the previous sheet is.

And attached should be the document we use (note it is stripped down), hopefully that will give a better understanding of what we are trying to achieve. as a stop gap we have introduced a system that works but we would prefer the types as the worksheet names.

Cheers

[Draughtsman]

Hmm lets see if this works

[Mayweed]

Try the attached sheet.
Do you need a message box asking for a number?

[Mayweed]

Try the attached sheet, do you need a message box?
Sorry about the double post, the forum was down for a few minutes and on return the first one was not showing up.

[Draughtsman]

Try the attached sheet, do you need a message box?
Sorry about the double post, the forum was down for a few minutes and on return the first one was not showing up.

Cheers for that!!

Next challenge, I have failed at this, is it possible to integrate that with the orignal VBA so that as a new sheet is inserted it runs your macro OR it runs after all the new sheets are inserted.

Cheers again!

[cobaltness]

So whats the formula? I understand what he's needing, I need the same thing. Did I miss something because I still don't have the solution.

[Marcol]

@ cobaltness
This thread is 2years old and the OP never really gave a conclusive answer.
I suggest you start a new thread as per the Forum Rules See Rule #2

[rtysdadg]

I need the formula as well??



Im new to coding but this is what a want to achive

Please Login or Register  to view this content.

Now say im in 30 April this code will work but as soon as this code kicks in on the 1st of May the link will break.

Please Login or Register  to view this content.

so how to a make this code,

Please Login or Register  to view this content.

link to the previous page??? say now April!



Thanks guys

[arlu1201]

rytsdadg,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.