Simplify formula

[Paul_Nepomuceno]

[FONT=&quot]Hi Guys,[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Is there anyway I can simplify this formula?[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]B26 =SUM(AMADEO!F4,CARMONA!F4,'CAVITE CITY'!F4,'DASMA A'!F4,'DASMA B'!F4,'GEN TRIAS'!F4,GMA!F4,IMUS!F4,MANGAHAN!F4,NAIC!F4,ROSARIO!F4,'SILANG I'!F4,'SILANG II'!F4,TANZA!F4,TERNATE!F4)[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]on B27 it should be like this =SUM(AMADEO!F8,CARMONA!F8,'CAVITE CITY'!F8,'DASMA A'!F8,'DASMA B'!F8,'GEN TRIAS'!F8,GMA!F8,IMUS!F8,MANGAHAN!F8,NAIC!F8,ROSARIO!F8,'SILANG I'!F8,'SILANG II'!F8,TANZA!F8,TERNATE!F8)[/FONT]
[FONT=&quot]And so on. [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Is there a way to make this easier[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Thank you guys.[/FONT]

 

[FormR]

Hi, welcome for the board, you can try this in B26 and copied down:

=SUMPRODUCT(SUM(INDIRECT("'"&{"AMADEO","CARMONA","CAVITE CITY","DASMA A","DASMA B","GEN TRIAS","GMA","IMUS","MANGAHAN","NAIC","ROSARIO","SILANG I","SILANG II","TERNATE"}&"'!F"&ROWS(B$26:B26)*4)))

 

[Paul_Nepomuceno]

Hi, welcome for the board, you can try this in B26 and copied down:

=SUMPRODUCT(SUM(INDIRECT("'"&{"AMADEO","CARMONA","CAVITE CITY","DASMA A","DASMA B","GEN TRIAS","GMA","IMUS","MANGAHAN","NAIC","ROSARIO","SILANG I","SILANG II","TERNATE"}&"'!F"&ROWS(B$26:B26)*4)))

Oh my God.. Thank you very much. This is a life saver.

 

[Paul_Nepomuceno]

Formr -

I also need these kind of formula for C to H

 

[FormR]

I also need these kind of formula for C to H

Hi, it doesn't seem too onerous to manually update the column references for those 6 variations.

EDIT - the col ref is here (scroll the code box right if needed):

=SUMPRODUCT(SUM(INDIRECT("'"&{"AMADEO","CARMONA","CAVITE CITY","DASMA A","DASMA B","GEN TRIAS","GMA","IMUS","MANGAHAN","NAIC","ROSARIO","SILANG I","SILANG II","TERNATE"}&"'!F"&ROWS(B$26:B26)*4)))

 

[Paul_Nepomuceno]

Here is the screenshot of what I am trying to do

 

[FormR]

Here is the screenshot of what I am trying to do

It doesn't help one bit - maybe you could try explaining in words?

 

[Paul_Nepomuceno]

Okay

Starting B26 I need to get F4 on C26 need to get F5 on D26 need to get F6.
Then B27 F8 on C27 F9 then D27 F10 and so on.

 

[FormR]

Starting B26 I need to get F4 on C26 need to get F5 on D26 need to get F6.
Then B27 F8 on C27 F9 then D27 F10 and so on.

Much easier to understand

You can try:

=SUMPRODUCT(SUM(INDIRECT("'"&{"AMADEO","CARMONA","CAVITE CITY","DASMA A","DASMA B","GEN TRIAS","GMA","IMUS","MANGAHAN","NAIC","ROSARIO","SILANG I","SILANG II","TERNATE"}&"'!F"&(ROWS(B$26:B26)*4)+COLUMNS($B26:B26)-1)))

 

[Paul_Nepomuceno]

Much easier to understand

You can try:

=SUMPRODUCT(SUM(INDIRECT("'"&{"AMADEO","CARMONA","CAVITE CITY","DASMA A","DASMA B","GEN TRIAS","GMA","IMUS","MANGAHAN","NAIC","ROSARIO","SILANG I","SILANG II","TERNATE"}&"'!F"&(ROWS(B$26:B26)*4)+COLUMNS($B26:B26)-1)))

When I copied the formula on C26 it is giving the same value on B26