Creating a template that is manually updated from another shee
Okay, assuming your other tab is called Produce, and like you stated, your
data actually begins in column A, and row 1 (i.e.: no headers). Then on your
main tab, you need to select which row. If you are selecting by row number,
then use the following for each. I am using cell B2 as the reference on my
main tab for which row number I want to use, so your formulas would be as
follows.
Amount on Hand:
=IF(OR($B$2="",$B$2<COUNT(Produce!A:A)),INDIRECT(" Produce!A"&$B$2),"")
Amount Required:
=IF(OR($B$2="",$B$2<COUNT(Produce!A:A)),INDIRECT(" Produce!C"&$B$2),"")
Item:
=IF(OR($B$2="",$B$2<COUNT(Produce!A:A)),INDIRECT(" Produce!C"&$B$2),"")
Substitute Item:
=IF(OR($B$2="",$B$2<COUNT(Produce!A:A)),INDIRECT(" Produce!D"&$B$2),"")
Hope this helps.
--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.
" wrote:
This is a small fictional data set, but it should be able to clarify
what I am looking for. The problem is that I need to be able to choose
which row i want the formula to use, but i don't want to have to go to
each and every cell to update the formula to use the new row.
Example
:Column A, Column B, Column C, Column D,
Row 1: 25, 49, apple, orange
Row 2: 10, 100, pear, lemon
If I select (this is the problem that I am having) the first row the
output should be
Amount on hand = 25
Amount required = 49
Item = apple
Substitute Item = orange
If i select the second row the output would be
Amount on hand = 10
Amount required = 100
Item = pear
Substitute Item =lemon
|