one cell in a row contains a number
 

I have a fixed row of 7 cells, any one of which might contain a number. the
cell with the number is the relevant one and it will determine my formula; i
will want to multiply the cell value by a number in another cel which is
chosen according to the number of the cell in the original row.

eg A1:A7- A3 contains 21
formula in A50 will wish to determine 21 x value of cell in column 3 of a
range of columns because the number 21 is in cell 3 in the row.

I was planning to use V or HLookup I think but cant fathom how to get the
cell number form the original row.

TIA Chris

one cell in a row contains a number
 

=SUMPRODUCT(ROW(A1:A7),A1:A7)

I notice that you are confused between rows and columns, Chris. A is a
column, not a row, and A3 is the 3rd row in that column.
--
David Biddulph

Chris wrote:
I have a fixed row of 7 cells, any one of which might contain a
number. the cell with the number is the relevant one and it will
determine my formula; i will want to multiply the cell value by a
number in another cel which is chosen according to the number of the
cell in the original row.
eg A1:A7- A3 contains 21
formula in A50 will wish to determine 21 x value of cell in column 3
of a range of columns because the number 21 is in cell 3 in the row.

I was planning to use V or HLookup I think but cant fathom how to get
the cell number form the original row.

TIA Chris

one cell in a row contains a number
 

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Chris" wrote in message
...
I have a fixed row of 7 cells, any one of which might contain a number. the
cell with the number is the relevant one and it will determine my formula;
i will want to multiply the cell value by a number in another cel which is
chosen according to the number of the cell in the original row.

eg A1:A7- A3 contains 21
formula in A50 will wish to determine 21 x value of cell in column 3 of a
range of columns because the number 21 is in cell 3 in the row.

I was planning to use V or HLookup I think but cant fathom how to get the
cell number form the original row.

TIA Chris

one cell in a row contains a number
 

Sorry, but I am a little confused by your question. You start off by saying
you have a **row** seven cells, but then your example shows a **column** of
cells (A1:A7). Also, I am unclear if you just want the number that is in the
cell or if you want its column number or, perhaps, both?

--
Rick (MVP - Excel)


"Chris" wrote in message
...
I have a fixed row of 7 cells, any one of which might contain a number. the
cell with the number is the relevant one and it will determine my formula;
i will want to multiply the cell value by a number in another cel which is
chosen according to the number of the cell in the original row.

eg A1:A7- A3 contains 21
formula in A50 will wish to determine 21 x value of cell in column 3 of a
range of columns because the number 21 is in cell 3 in the row.

I was planning to use V or HLookup I think but cant fathom how to get the
cell number form the original row.

TIA Chris

one cell in a row contains a number
 

Apologies to you all,

I mean that I have a row of 7 cells say N7:T7 for each use of the solution
it will progress down the sheet, N8:T8, etc.
I need the value in whichever cell has the number so that I can take it to
another formula and use it to find a value. like this
21
2
6
3
11 3
5
2
2
49 30

elsewhere in the workbook column N has a value, column M has a different
value and so on. My thinking was that if I could identify the column the
figure was in, i would be able to then tell my formula which value it had to
use to multiply with the value here and produce my result. i.e. 21 in cell
P7 means 21*2.2 = 46.2

I then have the added issue of if a row has more than one number but I was
trying to have some help and also to try and work things out for myself
rather than be lazy and simply ask for the whole answer. Sorry if I've done
wrong by that.

Chris



"Chris" wrote in message
...
I have a fixed row of 7 cells, any one of which might contain a number. the
cell with the number is the relevant one and it will determine my formula;
i will want to multiply the cell value by a number in another cel which is
chosen according to the number of the cell in the original row.

eg A1:A7- A3 contains 21
formula in A50 will wish to determine 21 x value of cell in column 3 of a
range of columns because the number 21 is in cell 3 in the row.

I was planning to use V or HLookup I think but cant fathom how to get the
cell number form the original row.

TIA Chris

one cell in a row contains a number
 

I'm still a little unclear about your setup, but it appears that all you
need from any one row is the number that is in that row (as I understand it,
that in the seven cells within a single row, only one of those cells will
have a value and the rest will be empty). If that is the case, you can get
that value without knowing the row it is in. This formula will return the
value you are seeking (note that there are two minus signs in front of the
left parenthesis... they and the parentheses are needed to make this method
work).

=--(N7&O7&P7&Q7&R7&S7&T7)

You can either put this formula in a cell by itself and have your formula
use that cell for the value or you can use the expression (minus the equal
sign) directly within your formula and save yourself from having to use
another column, your choice.

--
Rick (MVP - Excel)


"Chris" wrote in message
...
Apologies to you all,

I mean that I have a row of 7 cells say N7:T7 for each use of the solution
it will progress down the sheet, N8:T8, etc.
I need the value in whichever cell has the number so that I can take it to
another formula and use it to find a value. like this
21
2
6
3
11 3
5
2
2
49 30

elsewhere in the workbook column N has a value, column M has a different
value and so on. My thinking was that if I could identify the column the
figure was in, i would be able to then tell my formula which value it had
to use to multiply with the value here and produce my result. i.e. 21 in
cell P7 means 21*2.2 = 46.2

I then have the added issue of if a row has more than one number but I was
trying to have some help and also to try and work things out for myself
rather than be lazy and simply ask for the whole answer. Sorry if I've
done wrong by that.

Chris



"Chris" wrote in message
...
I have a fixed row of 7 cells, any one of which might contain a number.
the cell with the number is the relevant one and it will determine my
formula; i will want to multiply the cell value by a number in another cel
which is chosen according to the number of the cell in the original row.

eg A1:A7- A3 contains 21
formula in A50 will wish to determine 21 x value of cell in column 3 of a
range of columns because the number 21 is in cell 3 in the row.

I was planning to use V or HLookup I think but cant fathom how to get the
cell number form the original row.

TIA Chris

one cell in a row contains a number
 

I've read this thread about 10x and I'm still confused!

only one of those cells will have a value and the rest will be empty
=--(N7&O7&P7&Q7&R7&S7&T7)

If there is just one number in the range:

=SUM(N7:T7)
=MIN(N7:T7)
=MAX(N7:T7)

Seems they want to use the relative location of the number as the "index
number" for a lookup.

But, they're not telling us where to look for that index number.

.........N...O...P...Q...R...S...T
7........................1................

Ok, so the number 1 is found in Q7 and Q7 is the 4th cell relative to the
range.

Now what? How do we use 4 to look for the result you want?

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
I'm still a little unclear about your setup, but it appears that all you
need from any one row is the number that is in that row (as I understand
it, that in the seven cells within a single row, only one of those cells
will have a value and the rest will be empty). If that is the case, you
can get that value without knowing the row it is in. This formula will
return the value you are seeking (note that there are two minus signs in
front of the left parenthesis... they and the parentheses are needed to
make this method work).

=--(N7&O7&P7&Q7&R7&S7&T7)

You can either put this formula in a cell by itself and have your formula
use that cell for the value or you can use the expression (minus the equal
sign) directly within your formula and save yourself from having to use
another column, your choice.

--
Rick (MVP - Excel)


"Chris" wrote in message
...
Apologies to you all,

I mean that I have a row of 7 cells say N7:T7 for each use of the
solution it will progress down the sheet, N8:T8, etc.
I need the value in whichever cell has the number so that I can take it
to another formula and use it to find a value. like this
21
2
6
3
11 3
5
2
2
49 30

elsewhere in the workbook column N has a value, column M has a different
value and so on. My thinking was that if I could identify the column the
figure was in, i would be able to then tell my formula which value it had
to use to multiply with the value here and produce my result. i.e. 21 in
cell P7 means 21*2.2 = 46.2

I then have the added issue of if a row has more than one number but I
was trying to have some help and also to try and work things out for
myself rather than be lazy and simply ask for the whole answer. Sorry if
I've done wrong by that.

Chris



"Chris" wrote in message
...
I have a fixed row of 7 cells, any one of which might contain a number.
the cell with the number is the relevant one and it will determine my
formula; i will want to multiply the cell value by a number in another
cel which is chosen according to the number of the cell in the original
row.

eg A1:A7- A3 contains 21
formula in A50 will wish to determine 21 x value of cell in column 3 of
a range of columns because the number 21 is in cell 3 in the row.

I was planning to use V or HLookup I think but cant fathom how to get
the cell number form the original row.

TIA Chris

one cell in a row contains a number
 

I'm not sure why I went with the concatenation method (after New Years
confusion maybe<g); of course your three posted methods would work as well.
As for what the OP is trying to do... and how... your guess would be as good
as mine. As I said... "I'm still a little unclear about..."

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
I've read this thread about 10x and I'm still confused!

only one of those cells will have a value and the rest will be empty
=--(N7&O7&P7&Q7&R7&S7&T7)

If there is just one number in the range:

=SUM(N7:T7)
=MIN(N7:T7)
=MAX(N7:T7)

Seems they want to use the relative location of the number as the "index
number" for a lookup.

But, they're not telling us where to look for that index number.

........N...O...P...Q...R...S...T
7........................1................

Ok, so the number 1 is found in Q7 and Q7 is the 4th cell relative to the
range.

Now what? How do we use 4 to look for the result you want?

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
I'm still a little unclear about your setup, but it appears that all you
need from any one row is the number that is in that row (as I understand
it, that in the seven cells within a single row, only one of those cells
will have a value and the rest will be empty). If that is the case, you
can get that value without knowing the row it is in. This formula will
return the value you are seeking (note that there are two minus signs in
front of the left parenthesis... they and the parentheses are needed to
make this method work).

=--(N7&O7&P7&Q7&R7&S7&T7)

You can either put this formula in a cell by itself and have your formula
use that cell for the value or you can use the expression (minus the
equal sign) directly within your formula and save yourself from having to
use another column, your choice.

--
Rick (MVP - Excel)


"Chris" wrote in message
...
Apologies to you all,

I mean that I have a row of 7 cells say N7:T7 for each use of the
solution it will progress down the sheet, N8:T8, etc.
I need the value in whichever cell has the number so that I can take it
to another formula and use it to find a value. like this
21
2
6
3
11 3
5
2
2
49 30

elsewhere in the workbook column N has a value, column M has a different
value and so on. My thinking was that if I could identify the column the
figure was in, i would be able to then tell my formula which value it
had to use to multiply with the value here and produce my result. i.e.
21 in cell P7 means 21*2.2 = 46.2

I then have the added issue of if a row has more than one number but I
was trying to have some help and also to try and work things out for
myself rather than be lazy and simply ask for the whole answer. Sorry if
I've done wrong by that.

Chris



"Chris" wrote in message
...
I have a fixed row of 7 cells, any one of which might contain a number.
the cell with the number is the relevant one and it will determine my
formula; i will want to multiply the cell value by a number in another
cel which is chosen according to the number of the cell in the original
row.

eg A1:A7- A3 contains 21
formula in A50 will wish to determine 21 x value of cell in column 3 of
a range of columns because the number 21 is in cell 3 in the row.

I was planning to use V or HLookup I think but cant fathom how to get
the cell number form the original row.

TIA Chris

one cell in a row contains a number
 

That's why I made my offer as I did. Don't like to guess...

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein" wrote in message
...
I'm not sure why I went with the concatenation method (after New Years
confusion maybe<g); of course your three posted methods would work as
well. As for what the OP is trying to do... and how... your guess would be
as good as mine. As I said... "I'm still a little unclear about..."

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
I've read this thread about 10x and I'm still confused!

only one of those cells will have a value and the rest will be empty
=--(N7&O7&P7&Q7&R7&S7&T7)

If there is just one number in the range:

=SUM(N7:T7)
=MIN(N7:T7)
=MAX(N7:T7)

Seems they want to use the relative location of the number as the "index
number" for a lookup.

But, they're not telling us where to look for that index number.

........N...O...P...Q...R...S...T
7........................1................

Ok, so the number 1 is found in Q7 and Q7 is the 4th cell relative to the
range.

Now what? How do we use 4 to look for the result you want?

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
I'm still a little unclear about your setup, but it appears that all you
need from any one row is the number that is in that row (as I understand
it, that in the seven cells within a single row, only one of those cells
will have a value and the rest will be empty). If that is the case, you
can get that value without knowing the row it is in. This formula will
return the value you are seeking (note that there are two minus signs in
front of the left parenthesis... they and the parentheses are needed to
make this method work).

=--(N7&O7&P7&Q7&R7&S7&T7)

You can either put this formula in a cell by itself and have your
formula use that cell for the value or you can use the expression (minus
the equal sign) directly within your formula and save yourself from
having to use another column, your choice.

--
Rick (MVP - Excel)


"Chris" wrote in message
...
Apologies to you all,

I mean that I have a row of 7 cells say N7:T7 for each use of the
solution it will progress down the sheet, N8:T8, etc.
I need the value in whichever cell has the number so that I can take it
to another formula and use it to find a value. like this
21
2
6
3
11 3
5
2
2
49 30

elsewhere in the workbook column N has a value, column M has a
different value and so on. My thinking was that if I could identify the
column the figure was in, i would be able to then tell my formula which
value it had to use to multiply with the value here and produce my
result. i.e. 21 in cell P7 means 21*2.2 = 46.2

I then have the added issue of if a row has more than one number but I
was trying to have some help and also to try and work things out for
myself rather than be lazy and simply ask for the whole answer. Sorry
if I've done wrong by that.

Chris



"Chris" wrote in message
...
I have a fixed row of 7 cells, any one of which might contain a number.
the cell with the number is the relevant one and it will determine my
formula; i will want to multiply the cell value by a number in another
cel which is chosen according to the number of the cell in the original
row.

eg A1:A7- A3 contains 21
formula in A50 will wish to determine 21 x value of cell in column 3
of a range of columns because the number 21 is in cell 3 in the row.

I was planning to use V or HLookup I think but cant fathom how to get
the cell number form the original row.

TIA Chris