Using Variable sheet names with array in formula for worksheet

[UnsolvedCoding]

Greetings,

I am stuck on this one.

The problem is as follows:
The user enters a number range which gets added to some other information to create a series of consecutive sheet names. On the main worksheet called Cover, I need to create formulas that work like sumif formulas in the cells. However I can't figure out the syntax for doing this.

Example on the worksheet would be =Sumif('jb214'!D855,""P37"")

However to put that into code is where I get stuck because the sheet name (jb214) changes each time and the item to check for (P37) is pulled from an array list.

I have tried several different things and so far have failed. The macro recorder recorded this:
Sheets("Cover").Range("O14").Value = "=SUMIF('jb214'!D855,""P37"")"

But I need it to be coded something like this

Sheets("Cover").Range("O14").Value = "=SUMIF('jb' & jbNbr !D855,Input_Array(0))"

Any ideas?

 

[SkipVought]

Hi,

Why do sheets get added while the workbook is being used as an application???

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[UnsolvedCoding]

The macro that runs in the workbook creates not only the pages to hold the lists of information that will be entered by data entry, but also some pages need to be added after the data entry to create the proper diagram. It verifies the information used to set up the Cover sheet and then creates and inserts the new pages. Its the formulas on the Cover tab that are giving me trouble.

 

[SkipVought]

Creating sheets on the fly is a horrible design IMNSHO.

"=SUMIF('jb" & jbNbr & "'!D8:D55,Input_Array(0))"

Furthermore, multiple sheets for jod numbers is also a bad design, not up,to best and accepted practice.: a typical mistake committed by novice spreadsheet users. You'd be miles ahead having ALL your job data in ONE SINGLE TABLE!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[UnsolvedCoding]

I know. Creating sheets like that is always messy, however in this case I am being handed a very old worksheet that desperately needs updating. If you have a way to add and name a changing number of sheets every time I am all ears.

PS - has to be on different tabs because of the way the information breaks out.

 

[SkipVought]

As you have been experiencing, aggregating over multiple sheets is cumbersome, while aggregating from a single table is a snap, as that's how Excel has been designed to function. I'd change the workbook design to combine the job number sheets into one table and then use SUMIFS().

But you seemed not to notice the change I posted to your formula.

FYI, different formulas in the same column, in your case column O, is also a nightmare! You do have a real mess on your hands!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

...and I think that your formula might need this...

="SUMIF('jb" & jbNbr & "'!D8:D55," & Input_Array(0) & ")"

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

...and the statement should be...

Sheets("Cover").Range("O14").[b]Formula[/b] = "=SUMIF('jb" & jbNbr & "'!D8:D55," & Input_Array(0) & ")"

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[UnsolvedCoding]

I was given two weeks for this project and wonder if it will be enough. Sadly because of downstream impacts I can change coding but not the end layout.

 

[zelgar]

I'm wondering if it would be easier to code in VB what the SUMIF function did, versus trying to figure out a way of coding to put in the SUMIF function in the cell.

 

[SkipVought]

I'm just wondering if all the job sheets have the same sum range?

If so then maybe one formula might sum ALL sheets, assuming that the Cover (summary) sheet is the first sheet in the workbook and all others are job sheets...

    Sheets("Cover").Range("O14").Formula = "=SUM('" & Sheets(2).Name & ":" & Sheets(Sheets.Count).Name & "'!D8:D55)"

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[UnsolvedCoding]

I would think so. It would have to update after each user enters the information.

To over simplify - the user enters various products and the amp usage totals on the left side. The right side of the sheet is similar information plus which ones must be turned on all the time to make things run.
So I have to collect how much total amperage from the left side of the sheet along with will use it and put it on the table on the Cover sheet; then collect the same information on the right side (item, amount and possible use) plus the indicator that a item will always be on or not (which is a 1 if its on all the time).

What you have seen posted is the way to isolate each sheet as it is created and put the needed sumif statments into the Cover sheet under the correct items to show the amps.

... Cumbersome I think is a polite word.