S
Sherr
Gurus:
I'd like to put an info line on a form for the short-term
memory challenged (like me)
The problem: How do I nest my bits and pieces into
one formula to get the same result?
Results are:
Model:ABC1234 Qty:10 Sale:Y Return: Loc:W RefNo:12345
I have an input form/page where the results will display
as the memory jogger and a register/page where the source
info is. As the entries are made each may be recorded on
the register then the curser in the register moves to the
next row...ready for the next RecordThisEntryClick. A
Pivot Table is updated at the end of the session
displaying all the info in the Register.
My Bits & Pieces:
The register is on a separate hidden page and this
concatenation works with a specific address,therefore, on
the register,I need to OFFSET(-1,0) up 1 row to where the
info is in relation to the curser OR I need to identify
the row the info is on.
1)'Concatenation which works with the specific
address "Register!B756" (This address needs to vary as the
register grows)
=CONCATENATE("Model:",Register!B756," ","Qty:",Register!
E756," ","Sale:",Register!F756," ","Return:",Register!
G756," ","Loc:",Register!H756," ","RefNo:",Register!I756)
Results are:
Model:ABC1234 Qty:1 Sale:Y Return: Loc:W RefNo:12345
2)'Worksheet name is "Register" where info is
3)'Get the row number to read
=COUNTA(A6:A500)+5 which gives me the row number to read
Results are:"756"
4)'Columns to read B,E,F,G,H,I on row 756 or whatever
COUNTA result is
5)'I tried using the COUNTA result as the row reference
with the column reference. A23 is COUNTA result.
=CONCATENATE("B",A23) = B756 etc to add to Register! but
that didn't work either.
Recap: Existing Concatenate result is desired
"Register!" is OK for sheet
Replace row indicator with COUNTA result
Same Column references
Any assistance is greatly appreciated...feel free to toss
any or all of these ideas...there is, no doubt, a straight
forward approach that I've overlooked completely.
I'd like to put an info line on a form for the short-term
memory challenged (like me)
The problem: How do I nest my bits and pieces into
one formula to get the same result?
Results are:
Model:ABC1234 Qty:10 Sale:Y Return: Loc:W RefNo:12345
I have an input form/page where the results will display
as the memory jogger and a register/page where the source
info is. As the entries are made each may be recorded on
the register then the curser in the register moves to the
next row...ready for the next RecordThisEntryClick. A
Pivot Table is updated at the end of the session
displaying all the info in the Register.
My Bits & Pieces:
The register is on a separate hidden page and this
concatenation works with a specific address,therefore, on
the register,I need to OFFSET(-1,0) up 1 row to where the
info is in relation to the curser OR I need to identify
the row the info is on.
1)'Concatenation which works with the specific
address "Register!B756" (This address needs to vary as the
register grows)
=CONCATENATE("Model:",Register!B756," ","Qty:",Register!
E756," ","Sale:",Register!F756," ","Return:",Register!
G756," ","Loc:",Register!H756," ","RefNo:",Register!I756)
Results are:
Model:ABC1234 Qty:1 Sale:Y Return: Loc:W RefNo:12345
2)'Worksheet name is "Register" where info is
3)'Get the row number to read
=COUNTA(A6:A500)+5 which gives me the row number to read
Results are:"756"
4)'Columns to read B,E,F,G,H,I on row 756 or whatever
COUNTA result is
5)'I tried using the COUNTA result as the row reference
with the column reference. A23 is COUNTA result.
=CONCATENATE("B",A23) = B756 etc to add to Register! but
that didn't work either.
Recap: Existing Concatenate result is desired
"Register!" is OK for sheet
Replace row indicator with COUNTA result
Same Column references
Any assistance is greatly appreciated...feel free to toss
any or all of these ideas...there is, no doubt, a straight
forward approach that I've overlooked completely.