Linking tabs and formulas based on input into cells

**petelomax** · 03-17-2014, 08:34 AM

i need to link several tabs based on the input into one, which will give the output into the others

Profit Sheet tab, i will input the relevant information into here. From here, every job will follow through into Nigel's tab, which i have done, apart from the fact that there are cells with 0 in, which i think the formulas need updating. as you will be able to see, he receives 25% of the profit

within the profit tab i choose a certain "Fitter" from the dropdown menu, i then need the relevant details to follow through into that fitters tab and with them receiving 25% of the profit aswell

hope this has explained it

thanks

**ChemistB** · 03-17-2014, 11:00 AM

In the Profit sheet, column J (you can hide this column later)
In J2 copied down
=IF(ISNUMBER(H2),F2&COUNTIF($J$1:J1,F2)+1,"")
This creates unique identifiers for each row.
Note: I used the IF(ISNUMBER(...) on much of your formulas to remove those 0's. See attachment)

In each fitter's sheet, I put their name in H1 for convenience.
Then used the unique identifiers and INDEX/MATCH to bring over jobs related to that fitter. For example, In A2 of sheet Gary
=IFERROR(INDEX('Profit Sheet'!A$2:A$30,MATCH($H$1&ROWS($A$1:A1),'Profit Sheet'!$J$2:$J$30,0)),"")

Any questions?

**petelomax** · 03-18-2014, 06:36 AM

Hi

yes this works well just a couple of ommisions. when i put more jobs onto the profit sheet and input the fitter, the information is not following through onto their specific tab. i also need a formula where whichever fitter did the job (as allocated in profit sheet) they need to receive 25% of the profit.

hope this helps and appreciate your help

thanks

**ChemistB** · 03-18-2014, 08:12 AM

Oops, my mistake. On the Profit sheet. J2 copied down
=IF(ISNUMBER(H2),F2&COUNTIF($F$1:F2,F2),"")

and for the profit
=IFERROR(INDEX('Profit Sheet'!I$2:I$30,MATCH($H$1&ROWS($A$1:D1),'Profit Sheet'!$J$2:$J$30,0)*0.25),"")
Does that work for you?

**petelomax** · 03-18-2014, 08:56 AM

can you put it on the spreadsheet please so i can see it working

thanks

**ChemistB** · 03-18-2014, 09:21 AM

Here is an updated workbook.

EDIT: Sorry, misplaced parenthesis. Profit formula should be
=IFERROR(INDEX('Profit Sheet'!I$2:I$30,MATCH($H$1&ROWS($A$1:D1),'Profit Sheet'!$J$2:$J$30,0))*0.25,"")

I have corrected the attached workbook.

**petelomax** · 03-18-2014, 09:29 AM

the only thing that isnt working properly now is the profit for the fitters. whoever the fitter is, they should receive 25% of the profit but this isnt currently working. thanks

**petelomax** · 03-18-2014, 09:57 AM

sorry my mistake, it is working a treat, thank you!

Linking tabs and formulas based on input into cells

LinkBack

Thread Tools

Rate This Thread

Display

Linking tabs and formulas based on input into cells

Re: Linking tabs and formulas based on input into cells

Re: Linking tabs and formulas based on input into cells

Re: Linking tabs and formulas based on input into cells

Re: Linking tabs and formulas based on input into cells

Re: Linking tabs and formulas based on input into cells

Re: Linking tabs and formulas based on input into cells

Re: Linking tabs and formulas based on input into cells

Thread Information

Users Browsing this Thread

Similar Threads

Linking tabs and formulas based on input into cells

Linking input dates to quarterly reports in different tabs

Macro to insert one row into defined tabs based on user input

[SOLVED] Populate multiple tabs based on input in Master tab?

Linking tabs based upon changing data

Bookmarks

Bookmarks

Posting Permissions