Getting Data from same cells of various sheets

[ali92]

Hi All,

I have various sheets in a workbook (more sheets are added weekly) and I have a cover or detail sheet having just a table showing 4 columns i.e. Product name, price charged, revenue, G.P .... all sheets have this information in same cells e.g. product name is cell B1, product type in B2, price charged in cell B3, G.p in B4 and so on ...... (have uploaded a sample file).

Is there a way that whenever a new sheet is added .... this information automatically flows to cover sheet instead of having typed in?

using excel 2013.

Thanks.

 

[bosco_yip]

Hi,

D2, copy across and down :

=VLOOKUP(D$1,INDIRECT("'"&$C2&" - "&$B2&"'!A1:B5"),2,0)

p.s. I changed Cover Sheet D1 heading, so that Cover Sheet heading same as other worksheet heading

Regards
Bosco

 

[ali92]

Hi Bosco,

Thank you very much for your kind reply (although i need to understand and learn whats going on in this formula .... its wayyy above my skills), anyhow, my concern is I would still need to input product name and product category for the rest of fields to be populated ..... would it be possible as I add a new sheet, on cover page all the details are added including product name and category (to minimise omission errors).

Kinda Regards.

 

[bosco_yip]

Hi Bosco,

Thank you very much for your kind reply (although i need to understand and learn whats going on in this formula .... its wayyy above my skills), anyhow, my concern is I would still need to input product name and product category for the rest of fields to be populated ..... would it be possible as I add a new sheet, on cover page all the details are added including product name and category (to minimise omission errors).

Kinda Regards.

Give further information together upload with a sample file, and stated that what are the expected results ?

Regards

 

[ali92]

Hi

maybe i was not clear in my reply, let me try again

File you uploaded is beautiful, my concern is, we have to put/type product name and product category in column B and C and then formula works for column D, E, F. Since I have so many sheets there is a chance that i might forget to update cover sheet manually with product name and category ..... is it possible that when i insert a new sheet e.g. Omega - T5 ...... and it automatically update row 6 on cover sheet tab with required information rather then I type in cell B6 and C6?

I hope this make sense.

Regards.

 

[bosco_yip]

Hi

maybe i was not clear in my reply, let me try again

File you uploaded is beautiful, my concern is, we have to put/type product name and product category in column B and C and then formula works for column D, E, F. Since I have so many sheets there is a chance that i might forget to update cover sheet manually with product name and category ..... is it possible that when i insert a new sheet e.g. Omega - T5 ...... and it automatically update row 6 on cover sheet tab with required information rather then I type in cell B6 and C6?

I hope this make sense.

Regards.

Try.......

1] Add a column B "Sheet name"

2] Define name >>

Name : Shts

Refer to : =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),)&T(NOW())

3] "Sheet name" B2, formula copy down :

=IFERROR(INDEX(Shts,A2+1),"")

4] "Product name" C2, formula copy down :

=IF(B2="","",MID(B2,FIND("-",B2)+2,99))

5] "Product Category" D2, formula copy down :

=IF(B2="","",LEFT(B2,FIND("-",B2)-2))

6] "Price" E2, formula copy across to G2 and all copy down :

=IF($B2="","",VLOOKUP(E$1,INDIRECT("'"&$B2&"'!A1:B100"),2,0))

Remark :

1] Try to test by adding 1 new sheet with data, the "Cover Sheet" will auto add the new sheet name and data in accordindly.

2] Since the define name formula used a macro-4 function, the file will saved in a macro-enable worksheet.xlsm

Regards
Bosco

 

[ali92]

Hi Bosco,

This is amazing .... adding and deleting sheets just automatically updates the cover sheet.

Thank you so much for your help and explaining .... it would help me learn these formulas and logic behind it.

Regards,
Ali