Help in Combining Formula

Hi guys... need all the help i can get to combine these 2 formula together, coz i need both of the function to work in the single cell..

=OFFSET('Full List'!$A$6,ROW(F1)-1,COLUMN(F1)-1,1,1)

=IF('Full List'!H6>0,'Full List'!H6,'Full List'!F6)

really appreciate it.. thanks in advance

cheers...
shuriyan

Hi glenn.. thax for the fast respond.. i have already tried that equation before.. but i did not give me the result i was looking for..

okay let me explain the output i am looking for..


the below picture is the main Sheet named 'Full List'.. as you can see below i have alot of sub sheets name as AMOS,ANCHOR etc..

the output i desire is that when i key in in full list the data will be transfer to those sub sheets. i manage to complete that by using
=OFFSET('Full List'!$A$6,ROW(F1)-1,COLUMN(F1)-1,1,1)..

sample 1.jpg

here is the tricky part, as u can see the picture below is the AMOS sub sheets.. the cell i want apply those function combinations are the F6 as i want he price which is stated in the FULL LIST as in Cell H6.. therefore comes the usage of this =IF('Full List'!H6>0,'Full List'!H6,'Full List'!F6) which really did the job by choosing the value which i desire.

The problem is now, whenever i added a new row in the FULL LIST as my company keep adding new item to the warehouse. The row will be added in AMOS sheet but only the price column in AMOS not affected by the added row. the picture attached shows the result.

*** BEFORE ADDING ROW
before.png

*** AFTER ADDING ROW
AFTER.png

welll i think that a long story of complicated problems....hahahaha.... thanks

hi glenn, as you requested.. here i attached my sample worksheet..

Price List - Copy.xlsx

cheers!!

Originally Posted by Glenn Kennedy

Not sure if I know what to do... but I'll give it a go!!

no prob bro.. the effort is much apreciated

Originally Posted by Glenn Kennedy

Hope you're still out there...

To summarise. On the AMOS sheet, if AMOS have a price listed in the MAster sheet, transfer it to the AMOS sheet, otherwise, put the full price on the AMOS sheet. Is that it in a single sentence?

yupp still here...it is exactly as you put it.... if in FULL PRICE sheet does not have any speacial price on AMOS it will us the normal price as in the mastersheet NORMAL PRICE column..
as you can see the equation you given i have already use it in the cells... it work perfectly..

but the only thing is, when i added new row between the item 1 and 2 in master sheet, in amos sheet the price stuck in the blank row.. i need to be able to add new row to update new items regularly according with the code numbers.

Originally Posted by Glenn Kennedy

Try this out, then..

So. I'd be tempted to abandon OFFSET entirely and go for INDEX-MATCH. The formula looks like a bit of a monster. Basically it's using the part number to find an Amos price from the master list. If it doesnt find anything, it returns the normal price. It's also an Array formula, ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

I had to change AMOS Price in the yello cell to AMOS so that I could match it against the supplier row in the full list.

Clear as mud??

hahahaha... i can see that it works perfectly.. juz that i dont quite understand the formula yet... hey bro 1 more thing.. how should i be able to change it to other supplier name.. coz i got at least 20 supplier sheet i need to link to the master sheet as u can see in the names in the FULL List...

Damn... i would never understand the formula...hahahaha.... but as long it gets what i intend it to be good enough for me... well bro im staying in JB actualy, neighbor to singapore... n ya, we onlyhave 2 season here.. hot n wet... hahahaha