Copying complex formula, with sheet references...

[tadd9]

hi everyone, sorry to come here and demand answers to my excel issues but this one is a bit of a head scratcher...

i've created a bit of a monster of a workbook (only 2 sheets but doing some very funky complex things)

i am basically using a formula to read a box on another sheet and give me the answer i seek...

these sheets have not been made yet so i have had to loop some things and do some mad nesting and IFIFIF type things... i have it super balanced at the minute and doing exactly what i seek...

my issue is that in H5 i have a complex formula...

=IF(B5="","",IF(B5="b",'1'!I1,IF('1'!I1="","",IF(B5="","",'1'!I1))))

i need this formula to copy down to row 100004 of column H, BUT... i need it to do this on the way down...

H6:
=IF(B6="","",IF(B6="b",'2'!I1,IF('2'!I1="","",IF(B6="","",'2'!I1))))

H7:
=IF(B7="","",IF(B7="b",'3'!I1,IF('3'!I1="","",IF(B7="","",'3'!I1))))

etc...

i also have the issue in K5 with a different complex formula...

=IF(AND(H5="",G5=""),"",IF(H5="",-G5,IF('1'!I1="",H5-G5-I5-J5,VALUE(H5))))

again i need it to copy down to row 100004 but only change certain things...

K6:
=IF(AND(H6="",G6=""),"",IF(H5="",-G6,IF('2'!I1="",H6-G6-I6-J6,VALUE(H6))))

K7:
=IF(AND(H7="",G7=""),"",IF(H7="",-G7,IF('3'!I1="",H7-G7-I7-J7,VALUE(H7))))

etc...


ok so that is all pretty straight forward, now the head scratcher...

of all the multiple ways to copy a formula down i am yet to find one that will work for me... i think maybe it will take some VBA but i was hoping not...

my issue is i need these formulas to physically (haha if there is a physically in software) be in these boxes...

as you can see my complex formula is quite balanced and the old =INDIRECT trick wont work as i said this formula needs to be in the box to work...

so my question is... how can i physically copy these down without manually changing each '1'!, '2'!, '3'! all the way to '100004'!


possible solution i tried was...

i can obviously just ctrl+D to change the H5, H6, H7 stuff as excel will recognise and change that...

my issue there is that i believe it changes the !I1 part of the formula also but on each sheet i only want I1, NOT I1, I2, I3...

and obviously this method doesnt change the '1', '2', '3' part...

so my question is...

how can i copy these down, change the formula accordingly, but also actually have the formula in each corresponding cell??

sorry if i am not being clear or left out important points i am new to this excel stuff...

literally days old at it :p

i should probably mention, these sheets in the formula dont exist until i trigger them... this is the reason for the overly complex formula and the reason it needs to actually be in each box.

thank you for reading and if you have a possible solution, i owe you my life :D

[arlu1201]

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request.

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[LJMetzger]

Hi tadd9,

Please post a sample workbook that contains the formulas (3 rows is all that is needed). This is needed to verify that the formulas are replicated correctly, if my efforts seem to be successful.

Lewis

[peterrc]

Hi tadd9,

Change I1 to $I$1 (absolute cell reference)

Regards

peterrc

[tadd9]

hi Lewis, thank you for taking the time to attempt a fix...

my issue is... the workbook contains sensitive information i would not want to post on a forum and i fear a sample would not work correctly as in the work book is a hidden sheet that is triggered to open, then information from that page is updated via VLOOKUPs...

if you could offer your opinion on how you were thinking of solving it i would be happy to guide you through what is happening in my workbook... the figures and sums are sensitive information though...

i can attempt to build a sample if absolutely needed, it would however need to be heavily modified...

let me know if you need this sample and i will get to work on it.

again thank you for taking the time

and @peterrc... that is a very good idea my friend, thank you...

the change in sheet number is still an issue with it though... any thoughts?

[peterrc]

Hi todd9,

If B5 is in Sheet1 use:-

=IF(Sheet1!B5="","",IF(Sheet1!B5="b",'1'!$I$1,IF('1'!$I&1="","",IF(Sheet1!B5="","",'1'!$I$1))))

If B5 is in Sheet2 use:-

=IF(Sheet2!B5="","",IF(Sheet2!B5="b",'1'!$I$1,IF('1'!$I&1="","",IF(Sheet2!B5="","",'1'!$I$1))))

Regards

peterrc

[tadd9]

hi peterrc, thank you for a speedy response...

my issue is that i need the '1' from '1'!$I$1 to change to '2' and then '3' and so on... all the way to '100004' as it goes down the column...

i have a master sheet and a hidden sheet that is copied everytime i enter "b" into column B... it copies the hidden sheet and names it what is in column A (which is numbers 1 to 100000)... so the new sheets are named '1' '2' '3'... i then need the formulas in column H and K to change the sheet name as they are copied down...

i cannot just simply use the =INDIRECT trick either as i need these formulas in the cells (not an INDIRECT formula)

the formula basically says, if "b" is not in column B then BLANK... and also says (and the tricky bit that needs to change) that if its corresponding sheet number ('1', '2', '3' etc...) and cell I9 of that sheet is BLANK then BLANK the cell this formula is in.

i hope you can follow.

thank you

[martindwilson]

so you are going to have a workbook with 100004 worksheets?

[tadd9]

haha no...

not every row will have "b" in it and trigger a new sheet... i may even shorten the list to 10000...

still far too many to manually fix myself :p

thank you for your reply

edit: oh and it would only be 100000... just my table row 1 is on row5 of excel... therefore row 100000 in my table is row100004 in excel

[peterrc]

Hi tadd9,

Even if you reduce it to 10,000 and only have a "b" in every fourth row you will still need 2,500 Worksheets?

What exactly are you trying to achieve?

Regards

peterrc

[tadd9]

its a set of accounts and a certain system that has been devised...

if i can make it solve world peace as well i will

and depending on how many entries i have per month or week i may even make it 1000...

is there an issue with having too many sheets then??

either way, my issue still stands...

how to actually have my formula put into a cell, down a column and make the change to the sheet number...

could a one time VBA to add and alter the formula down work?

i wouldnt have any clue how to even write the VBA for it, sorry

[peterrc]

Hi tadd9,

In your original post you stated:-

"I am basically using a formula to read a box (cell?) on another sheet and give me the answer I seek..."

If the answer you seek in already on another sheet (I assume the hidden one) couldn't you use INDEX and MATCH?

Regards

peterrc

[tadd9]

yes cell, sorry...

and ummm... im not sure i follow...

either way i will end up needing to copy a formula down a column that contains sheet info would i not?

sorry, this has already got extremely complex for me...

its not the referencing a sheets information i am having the trouble with...

it is the IF the cell on another sheet is blank then blank this cell... i need that to copy down a column.

edit: is it possible to index/match the hidden sheets cell I1 and index/match with this formula and all the IFs?? that is one formula i do not even want to attempt haha

sorry for my inexperience

[tadd9]

could anyone give me some VBA to copy the formula down column H and K (from row5)

then change the value of '1' to +1 every row it goes down... run this VBA once to achieve my goal and then delete it once the cells are filled with the right sheet number...

sorry if this is a big ask.

[LJMetzger]

Hi again,

I think I may have a solution for you. Try the attached sample file and the code that follows in this post (both the same) tested using Excel 2003. If the sample file works, then you can import the Macro into your Excel 2013 file.

I had two small problems.
1. I am using Excel 2003 and either I am doing something wrong or the syntax '1'!I1 in a cell doesn't work in Excel 2003. I was able to workaround this problem in two ways:
a. By converting the Sheet index numbers to Sheet names.
b. By using CONDITIONAL COMPILATION to use your original syntax ''1'!I1
2. Since you reference sheets, my sample file only works on 4 sheets.

By editing the constants at the top of my macro you can customize:
a. the last row (number of sheets) you want, and
b. the syntax used in the formulas '1'!I1 or 'Sheet 1'!I1.

Please Login or Register  to view this content.

If you have problems running macros and/or copying the VBA code see one or more of the following:
To enable Macros and to Run Macros see the following:
http://office.microsoft.com/en-us/ex...010031071.aspx
http://office.microsoft.com/en-us/ex...010014113.aspx
If help is still needed do a google search for 'youtube excel enable macro' and/or 'youtube excel run macro'.

To access Visual Basic (VBA) see:
http://www.ablebits.com/office-addin...a-macro-excel/

Lewis

Please Login or Register  to view this content.

[tadd9]

thank you my friend, i am testing now...

Edit: ok... an issue... i know how to open VBA and copy paste a macro... i have also set the settings you mentioned...

but i dont fully get the true or false bit...

and after i put the code in nothing happened haha sorry am i meant to press something to get it to run?

edit2: also... if i am understanding this right, you are counting the already created sheets and naming down the list that way?

if thats the case i have not created sheet '1' or '2' or '3' etc... yet.

[LJMetzger]

edit2: also... if i am understanding this right, you are counting the already created sheets and naming down the list that way?

That is correct, which I thought was per your instructions.

The code tends to rebel if the sheet does not exist. If this is the case, each new line may have to be created (automatically) when a new sheet is added. It could get nasty again if a sheet is deleted.

Let's walk first before we run.

Question 1 - Does the code work if the sheets exist?

Question 2 - If the code works, how do you create new sheets, and how do you want a line of new formulas added?

Lewis

[tadd9]

ok let me break this down...

i either need...

code to copy a formula down to line 100004 but change the '1' to 2 then 3 and so on every row...

or

everytime i enter "b" into column B... put the formulas into columns H and K and change the '1' in the formula to whatever is in column A of the same row i added "b" and that the formula are adding to...

are either of those possible?

i can give specifics... the 2 formulas i want to copy down are in H and K and they start on row 5 which is row 1 in my table... so A5 has a 1 in it, A6 has 2, A7 has 3...

if that helps...

i then need the formula to have sheet NAME 1 or 2 or 3 depending on what is in column A...

OR +1 from the formula above... also needs to change the formula cell references as it goes down... B5 to B6 to B7 etc.

thank you so much for taking the time to attempt a solution

edit:
or as it stands now, i have the formula in all cells i need at the minute... so the cell references are changed and using the trick of $I$1 that is the same all the way down...

now the only issue is all my formulas need to have the '1' changed to what ever is in column A... i.e 1 2 3 4 5 6 7... 99999 100000

edit2: is there a possible way to have a VBA change the values of the formulas to reflect the sheet numbers as the numbers is in column A of the same row?

sorry man, just throwing all my ideas around, not sure which would be easiest to achieve?

thanks man

edit3: sorryyy forgot to mention, the code does not work on sheets that arent created

[LJMetzger]

What you're asking can be done. The hardest thing is defining the problem, which at the present time seems more dynamic than static.

The sheet numbers in column A make things a lot easier.

Tell us what do you do when you add a new sheet. Go through it step by step, so we can duplicate what you do in VBA code as much as possible.

Lewis

[tadd9]

let me just clear things up...

i need a quick way to copy this formula...

=IF(B5="","",IF(B5="b",'1'!$I$1,IF('1'!$I$1="","",IF(B5="","",'1'!$I$1))))

down column H

i need the formula in the cells to change the B5 to B(whatever row i added on)

and need the 1 from '1'! to change to the value of column A (of the same row)(i.e column A=55556 then the formula ='55556'!$I$1 ... but also =B[and the row])

i also need to do this with column K and this formula...

=IF(AND(H5="",G5=""),"",IF(H5="",-G5,IF('1'!$I$1="",H5-G5-I5-J5,VALUE(H5))))


now...

Please Login or Register  to view this content.

this is what triggers the opening of a new sheet... i enter "b" into column B and it triggers a new sheet with the name of 1 2 3... depending on what is in column A of the same row i entered "b"

however...

the formulas must be in the cells... i have realised that i need them there at all times, not only if i have entered a "b" in column B...

the formulas do 2 things 1 if "b" is present and one if "b" is not...

i know that makes things harder as they cant just be triggered in after i enter "b" ... sorry

i am literally gonna need something to copy the formula, change the row and change the '1' number to column A of that row (or just copy to next cell down and +1 to both values)

is that possible??

edit: also just realised i cant use the I$1$ trick as it gives me a #ref error in the boxes so the formulas would be '1'!I1 again like my original post, said

[LJMetzger]

I am assuming Target is the row on the main sheet. Try the following

Lewis

To Worsheet_Change() add at the end (after the activate):

Please Login or Register  to view this content.

Add the following code (and delete the code from my previous post):

Please Login or Register  to view this content.

[tadd9]

i get a compile error...

sorry am i adding the 2nd part to a separate window in VBA or same window just below the end sub?

oh and ive realised if i can get that enter "b" in column B trigger to (at the same time) put the formulas into the columns H and K, then i can just copy down "b" and have them all auto fill... the down side to doing it this way is it would create 100000 sheets, which i would then have to delete... if excel would let me with 100000 sheets open :p could always do 1000 at a time or whatever my ram would allow me :D

if you could tell me what i have done wrong with the code it may fix the issue but it doesnt seem to want to work for me...

sorry very new to this, surprised myself by getting this far haha

thank you again for helping

edit: ok would it be possible to...

when b is entered into column B and my existing code creates this new sheet...

add a formula to same row i entered "b" but in column H (with the 1st formula) and column K (with the 2nd formula) (at the same time, change the cell reference to the row its on and change the '1' to '2' or '3' etc (the value of column A same row and name of new sheet just triggered)

if you can code that i will semi manually enter "b" and then delete all the created sheets (very odd way to do it, but seems simpler)

and also, i notice you give the option in the code to reference the '1' or the sheet1 if true or false...

well i always will be using the '1' option so if that helps cut down on coding that is what i am aiming for

thank you brother brother, kifflom

[tadd9]

sorry for double...

i am still convinced a copy cell H5 and K6 down adding +1 to the places i need in the formula would be the easiest option...

is there a run once sort of VBA that will do the job and then i save the sheet and remove the code??

or would the code always need to be present to do the task?? i guess i mean is there a way to make a permanent change then remove the VBA code??

sorry for all the questions, im just trying to solve the issue myself at the same time so i dont burden you and others with it

[LJMetzger]

I can't help you without additional information. What does the compile error say and what line does it point to.

Please Login or Register  to view this content.

[tadd9]

sorry it was this part...

Option Explicit

'Set the following value to 'True' to use absolute Sheet Number references (.e.g. '1'!I1)
'Set the following value to 'False' to use Sheet Name references for the Sheet Index number
'(.e.g. 'Sheet 2'!I1) where 'Sheet 2' would be the name of the sheet corresponding to 'Index 1'
#Const USE_ABSOLUTE_SHEET_NUMBERS = True

very first line option explicit was above the blank line and was highlighted in yellow.

edit: says only comments may appear after end sub, end function, or end property...

this is how i wrote it...

Please Login or Register  to view this content.

and this part is highlighted with an error...

Private Sub Worksheet_Change(ByVal Target As Range)

[tadd9]

Ok i am getting a bit frustrated now as my IFIFIFIFs have become unbalance with all my altering and saving i have messed them up somewhere and they took me days to balance...

Is there some simple VBA to show the value of cell I1 on different sheets in column H of my master sheet? Relative to name of the sheet and what is in column A?

So column A has 1 to 100000 (starting at A5)

If i enter a "b" into column B row 5 it will show the value of I1 on sheet '1'! (the number in A5) would show in H5 (of master)...

Then row 6 if a "b" was entered would show the cell I9 on sheet '2' (the number in cell A6) would show in cell H6 (of master) etc...

BUT if the cell I1 is blank then blank the cell in column H showing #ref (as #ref is what i get if I1 is blank)

This was the purpose of this mad complex formula

[newdoverman]

This might help you. On the right, you will see column T with numbers. I have used this as a helper column for using the number names of your worksheets. As you add worksheets, add the number of the worksheet to column T. This will not create worksheets for you but should retrieve the values that you are looking for. This was done much earlier in the day and didn't have a chance to upload it so it might be out of date.

[tadd9]

Hey newdover... thank you for the reply... each new worksheet that opens is named 1 2 3 4 5 etc....

In that worksheet is a box in A2 that has the same number as its name and same number as column A in the mastersheet... could you tell me what you are attempting to achieve with this?

Thanks for the help

[peterrc]

Hi tadd9,

In your post #26 you state:-

"If i enter a "b" into column B row 5 it will show the value of I1 on sheet '1'! (the number in A5) would show in H5 (of master)...
Then row 6 if a "b" was entered would show the cell I9 on sheet '2' (the number in cell A6) would show in cell H6 (of master) etc..."

Does this mean that row 7 would show I17 on sheet 3?

Also,

If you do get this to work and you were to look at say 100 sheets created what would be the differences between each of the sheets?

Regards

peterrc

[tadd9]

Ahhh i meant to put I1... sorry, every cell down the column H of my master sheet needs to show the value of I1 (on its corresponding sheet)

So... row 5 is item number 1...

There is a 1 in A5 (item 1)

When i enter "b" into B5 it opens a sheet called 1

Now i need H5 to show I1 on sheetname 1 (so '1'!I1 )

Then row 6 does the same but for sheetname 2 ( this would be '1'!I1 )

And so on down the list... if there is a "b" on that row, show I1 on that rows sheet...

But if its blank on the other sheet then blank the H cell that is showing the #ref error...

Also do nothing if i dont enter "b" into column B of that row...

Sorry for maybe making this way more complicated than it needs to be lol

Thank you for replying

Also, the only thing to change sheet to sheet (that i need the value of (VALUE) not formula... is, cell i1...

The rest of the info on each sheet i do not need...

I just need the H cell of master sheet (same row as "b") to blank if cell i1 on that sheet of that row is blank

And also do nothing if i dont enter a "b" (just blank cell H of master sheet

[peterrc]

Hi tadd9,

What about my second point - differences between the sheets created?

Regards

peterrc

[tadd9]

Hey mate, just edited my post on previous page about it at the bottom

[peterrc]

Hi tadd9,

So you will end up with a workbook with 10,000 plus worksheets and the only information you want from each sheet is the value of I1 (one cell)?

(a simple yes or no answer would suffice).

Incidentally, as selecting "b" creates the new (copied) sheet how is the value of I1 created?

Regards

peterrc

[tadd9]

I1 is calculated on the sheets themselves...

Basically i buy items... some are single items, some are bundled...

I count a bundle as 1 item (but need a sheet to calculate the individual items) all i need though on the master sheet is I1 of these sheets (depending on which item number is b)

And to your first question... yes... just that one cell i1

[newdoverman]

@ Tadd9 message#28

Glad to help. Regarding A2, there is nothing in A2 of any of the worksheets in the workbook that I uploaded.....just checked to be sure.

[tadd9]

hi newdover, i was referring to my sheets in my workbook, sorry for not being clear...

the reason i am doing all of this is to avoid #ref errors

[newdoverman]

When you are working on a project, complicated or not, make constant backups so that you can fall back to a "known" position when necessary. This also holds true when entering data and using your workbook....backup, backup, backup.

I say this because of the sheer number of things that you are trying which can become very confusing very quickly.

Good luck with the project.

[tadd9]

thanks man, i have learned a lesson

ok so i solved my issues...

in column H row 5 i put:

=IFERROR('1'!$I$1,"")

then in H6 i put:

=IFERROR(INDIRECT("'"&ROW()-4&"'!$I$1"),"")

then copied the indirect down :D

simple but effective!!

thank you to all who helped with this, been a real mind boggler

but also learned a lot so thank you all for your input

regards

[newdoverman]

Thanks for letting us know what solution you are using.

Good luck.

[tadd9]

Thank you, no problems, thank you all for being so helpful...

If ever anyone has similar issues of sheet to sheet references and issues with errors because sheets wernt created or such, then i am subscribed to this topic and am willing to explain my work arounds and wall that i hit and what i did to fix them...

For future reference...

If you want master sheet cell info on your sheets then i used VLOOKUPS and IF and AND formulas...

If you need sheet info on a master sheet then the =INDIRECT approach seemed the only way (still not seen anyone successfully VBA it or index/match and things from master sheet to multiple other sheets...

If it is just one cell then no issues ='sheetname'!cellname... if you want to copy down or across for lots of cells though and need changes in the formula, =INDIRECT is the way...

Also i notice cross sheet cell information is prone to errors (#ref if there is no sheet by that name yet and i got some #value and even #N/A when messing with index formulas across sheets)

My solution to that was the =IFERROR(...,"") fix...

This meant if my cell had an error value or ref or anything it would just blank the cell... i would have made the cells value of 0 but i need the cell blank instead... you could also do something like...

=IFERROR(...,"ERROR")

and your cell that miscalculated or gave the error will just return the phrase ERROR... you can then go and realise why you have an error... did you delete a sheet that was being referenced? Did you forget to fill out that cell you are referencing or did the calculation go wrong with it? Etc...

I found this route with things worked best for me and allowed me to customise the formulas to my needs exactly (what to do if error and which sheet to reference etc...)

Again thank you all for your help and god save the google

maybe i will be back when excel throws me another curve ball haha

Take care guys

Regards