Need help to update the table

[Sanjibghosh]

Hi All,

Please help me to fill up the table by formula when i put the date in green marked cell in Channel Tips sheet. Data will fetch from Master Data sheet. Please help me urgent basis.

[PFDave]

You'll need to use SUMIFs to complete this but on first view the date format in the master sheet and Channel Tips sheets is different - one is DD/MM/YYYY the other is MM/DD/YYYY

I'll have a play now and see what I can produce, but someone else may be able to do this quicker

[JohnTopley]

Please add expected results (Manual Calculation) to your file.

I assume only ("_BC_")means searching for this string in "Blast Name???

[Sanjibghosh]

Correct. JohnTopley

[JohnTopley]

Changed Dates in column B to DD/MM/YY (form mm/dd/yy) :you need to look at getting you dates formats standardised.

in B5

=SUMPRODUCT(('Master Data'!$D$2:$D$100)*(INT('Master Data'!$B$2:$B$100)='Channel Tips'!$B$3)*(ISNUMBER(SEARCH("NORAM",'Master Data'!$A$2:$A$100)))*(NOT(ISNUMBER(SEARCH("_BC_",'Master Data'!$A$2:$A$100)))))

in C5

=SUMPRODUCT(('Master Data'!$E$2:$E$100)*(INT('Master Data'!$B$2:$B$100)='Channel Tips'!$B$3)*(ISNUMBER(SEARCH("NORAM",'Master Data'!$A$2:$A$100)))*(NOT(ISNUMBER(SEARCH("_BC_",'Master Data'!$A$2:$A$100)))))

in B14

=SUMPRODUCT(('Master Data'!$D$2:$D$100)*(INT('Master Data'!$B$2:$B$100)='Channel Tips'!$B$12)*(ISNUMBER(SEARCH("NORAM",'Master Data'!$A$2:$A$100)))*(ISNUMBER(SEARCH("_BC_",'Master Data'!$A$2:$A$100))))

in C14

=SUMPRODUCT(('Master Data'!$E$2:$E$100)*(INT('Master Data'!$B$2:$B$100)='Channel Tips'!$B$12)*(ISNUMBER(SEARCH("NORAM",'Master Data'!$A$2:$A$100)))*(ISNUMBER(SEARCH("_BC_",'Master Data'!$A$2:$A$100))))

Replace "NORAM" with "APJ" & "EMEA" for other rows

[Sanjibghosh]

Hi All,

can it possible to update the subject line in cell no D3 in Channel Tips sheet fetching from (Column C) Master Data sheet based on Data (green mark cell) in Channel Tips sheet? please help me to update this by formula.

[JohnTopley]

Try

=INDEX('Master Data'!$C$2:$C$100,MATCH(B3,'Master Data'!$B$2:$B$100,0))

BUT sort out dates in column B of 'Master Data' to be same as B3 format AND date only (not Date and Time)

[Sanjibghosh]

Hi john,

please let me know how to sort out dates in column B of 'Master Data' to be same as B3 format AND date only by format cells options

[JohnTopley]

Format an empty column e.g O as "dd/mm/yyyy".

In O2

=INT(B2)

Drag down

COPY column O and PasteSpecial VALUES to column B.

How is the data input to column B initially? if it automated i.e. from an external source, then you will to repeat the above every time you get new data.

[Sanjibghosh]

Thanks John... if i need any kind of help further i will let you know.

[Sanjibghosh]

I have create a table in report deck sheet and manually put the data in the table as per the information from all tables in Channel Tips sheet. Now i want to know it can be automatically update the table in report deck sheet when i create a new tables below in Channel Tips sheet. Please help

[JohnTopley]

What is "Report Deck" sheet: it isn't in your original file.

Please post new file showing what is required.

[Sanjibghosh]

Sorry missed the attachment...

[JohnTopley]

Are you asking how to extract the 4QFY16 data from "Master Data"? If so, what defines 4QFY16 ? or what are the criteria for extraction?

And you did not format column O in "Master Data" as "dd/mm/yy"

[Sanjibghosh]

No.. Actually i want when i create new table under the existing table in Channel Tips sheet then in the "Report Deck" sheet automatically add/update those data below the table as per format.

[JohnTopley]

I note you blank dates in some tips cells: is that deliberate ? should they be omitted from the "Report Deck"?

[Sanjibghosh]

No... the blank dates cells is not that deliberate
yes...they are omitted from the "Report Deck"

[JohnTopley]

You might have to resort to VBA to get your "Report Deck" (or use "helper" columns).

[Sanjibghosh]

I have no idea how to use "helper" columns

[JohnTopley]

See the attached:

formulas generally are of this form:

=IFERROR(INDEX('Channel Tips'!$B$5:$B$1000,SMALL(IF(ISNUMBER('Channel Tips'!$B$5:$B$1000),ROW('Channel Tips'!$B$5:$B$1000)-ROW($B$5)+1,""),ROWS($B$5:B5))),"")

Entered with Ctrl+Shift+Enter

in column D (date retrieved from "Channel Data")

=IFERROR(INDEX('Master Data'!$C$2:$C$20000,MATCH($B14,'Master Data'!$O$2:$O$20000,0)),"")

% columns have been CUSTOM formatted as:

0.00%;;;a

which "hides" the zeros ( they are still there as data)

Column R is a "helper" column which I hid by changing the font to white

=IFERROR(INDEX('Channel Tips'!$A$5:$A$1000,SMALL(IF(ISNUMBER('Channel Tips'!$B$5:$B$1000),ROW('Channel Tips'!$B$5:$B$1000)-ROW($B$5)+1,""),ROWS($B$5:B5))),"")

Columns E & F use column R


in E


=IFERROR(LEFT(R14,FIND(" ",R14)-1),"")

in F

=IFERROR(RIGHT(R14,LEN(R14)-FIND(" ",R14)-2),"")

No formula for deriving column A (unless you supply criteria)