Formula to put number in right place based on input

Hi,

In attached file I have a summary on the first sheet "Sammendrag". In Cell N-BF I want the sum from the changelog to be put in and/if the total on the right "Prodkode".
I am going to make 50 sheets that all must summary on the first sheet.

Today I have to check every "EM" and multiply all prodkode "699 - 791".

You can see what I mean by looking on cell "P9 -R9" in "sammendrag".

Think this can be done with and IF formula, but it need to check all prodkode and if there is more then one line they needs to be multiplied and put in the correct cell in sheet "sammendrag.

Hi there,

It's difficult to test this fully, but you could try inserting the following formula in Cell N9 and then copying it across and downwards as required:

Please Login or Register  to view this content.

Hope this helps.

Regards,

Greg M

The forum crashed on me when I posted this reply earlier, but fortunately I had saved it to the clipboard:

You can use this formula in cell N9 of the summary sheet:

=SUMIFS(INDIRECT("'"&$A9&"'!$N$50:$N$71"),INDIRECT("'"&$A9&"'!$G$50:$G$71"),N$8)

then copy across as required.

When you have added the other sheets you will be able to copy this down, as it picks up the sheet name from column A.

Note that you may need to use semicolons ( ; ) instead of commas ( , ) in the formula, depending on your regional settings.

Hope this helps.

Pete

Hi,

This was perfect! Excactly what I was looking for!

Thank you!

Hi,

Thank you for your answer! I tried the file but I do not get it to work. Would be really cool to learn macros. And to show this to my colleagues. Could it be because I am using excel version 2211 15831.20252 ?

Originally Posted by kvsrinivasamurthy

Indirect formula slow down excel. Worksheet event and a macro can be used to apply formulas.
Macro applies formulas for existing sheets only When any sheet is added or deleted Run Macro. To start with run Macro to apply all formulas in sheet Sammendrag. This is one time.
Work sheet event is used to to automatically apply formulas for the row when sheet name changed in Column A.
Macro code:

Please Login or Register  to view this content.

Worksheet event code:

Please Login or Register  to view this content.

How to use workheet event the code
Right click on Sheet tab --> view code
Visual Basic (VB) window opens.
Paste the code
Close the VB window.
Save the file as .xlsm

Hi,

Thank you for your answer! I tried the file but I do not get it to work. Would be really cool to learn macros. And to show this to my colleagues. Could it be because I am using excel version 2211 15831.20252 ?

Originally Posted by Pete_UK

The forum crashed on me when I posted this reply earlier, but fortunately I had saved it to the clipboard:

You can use this formula in cell N9 of the summary sheet:

=SUMIFS(INDIRECT("'"&$A9&"'!$N$50:$N$71"),INDIRECT("'"&$A9&"'!$G$50:$G$71"),N$8)

then copy across as required.

When you have added the other sheets you will be able to copy this down, as it picks up the sheet name from column A.

Note that you may need to use semicolons ( ; ) instead of commas ( , ) in the formula, depending on your regional settings.

Hope this helps.

Pete

Hi,

This was perfect! Excactly what I was looking for!

Thank you!

Glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete