Usually I am pretty good at using formulas, but this one has me
baffled, I am pretty sure it is possible.

I have two sheets; on the first page I have all my calculations and
variables, one row per item, but multiple columns. On the second
sheet I have how the items should be formatted. I am trying to figure
out a way script it so I can choose a row, and have it fill in the
cells on sheet 2, or choose another row from sheet 1 and have it
recalculate the cells on the sheet 2. In a nutshell sheet 2 is a
template that gets it information form sheet one. The trick is that I
need to be able to choose what rows from the first sheet.

Well, without knowing your data structure at all, and without knowing how you
are obtaining which specific row, you could do something like this:
Assuming A1 contains the row that you need.

B1: =INDIRECT("Sheet1!B"&A1)

If this doesn't get you on the right track, post back with more info, such
as data structure, formulas, and expected results.
--
** John C **


" wrote:

Usually I am pretty good at using formulas, but this one has me
baffled, I am pretty sure it is possible.

I have two sheets; on the first page I have all my calculations and
variables, one row per item, but multiple columns. On the second
sheet I have how the items should be formatted. I am trying to figure
out a way script it so I can choose a row, and have it fill in the
cells on sheet 2, or choose another row from sheet 1 and have it
recalculate the cells on the sheet 2. In a nutshell sheet 2 is a
template that gets it information form sheet one. The trick is that I
need to be able to choose what rows from the first sheet.

On Oct 22, 6:04*am, John C <johnc@stateofdenial wrote:
Well, without knowing your data structure at all, and without knowing how you
are obtaining which specific row, you could do something like this:
Assuming A1 contains the row that you need.

B1: =INDIRECT("Sheet1!B"&A1)

If this doesn't get you on the right track, post back with more info, such
as data structure, formulas, and expected results.
--
** John C **

" wrote:
Usually I am pretty good at using formulas, but this one has me
baffled, I am pretty sure it is possible.

I have two sheets; on the first page I have all my calculations and
variables, one row per item, but multiple columns. *On the second
sheet I have how the items should be formatted. *I am trying to figure
out a way script it so I can choose a row, and have it fill in the
cells on sheet 2, or choose another row from sheet 1 and have it
recalculate the cells on the sheet 2. *In a nutshell sheet 2 is a
template that gets it information form sheet one. *The trick is that I
need to be able to choose what rows from the first sheet.

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

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

Thats exactly what I was looking for. Thank you.

On Oct 22, 11:14*am, John C <johnc@stateofdenial wrote:
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

You are welcome, and thanks for the feedback. Please remember to check the
YES box below so that others can know this is done.
--
** John C **


" wrote:

Thats exactly what I was looking for. Thank you.

On Oct 22, 11:14 am, John C <johnc@stateofdenial wrote:
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