Inserting Text from a Cell into Formula

  • Thread starter Thread starter martialtiger
  • Start date Start date
M

martialtiger

Hi All,

Here's a doosie. Is there anyway to take the text value of a cell an
insert that into a formula? But the twist is this, the text value i
the cell represents the name of the worksheet. I am using Excel 2002.

example
A2 is a list that contacts "1 Hour","2 Hours","3 Hours" etc all th
way up to "8 Hours". Then B2 contains the formula: ='A2'!A5. Th
formula is to assume that a value is selected for A2. Let's say th
value is "1 Hour", so the actual formula should be ='1Hour'!A5
Click to expand...

Hope I'm making sense :( .

Thanks Everyone!

Jona
 
=A2&A5 in cell B2 will give you whatever value is in cell A2 strung together
with whatever value is in cell A5.............if you want a space between
them then use
=A2&" "&A5

hth
Vaya con Dios,
Chuck, CABGx3
 
Thanks CLR,

It worked, but now I'm trying to actually take it a step further b
adding a more complex function.

=IF('1 Hour'!G22="","TBD",'1 Hour'!G22-'1 Hour'!G4)

Any ideas? A2 in this case is "1 Hour" and A5 is "G22". "G4" is jus
another value.
=A2&A5 in cell B2 will give you whatever value is in cell A
strung together
with whatever value is in cell A5.............if you want a spac
between
them then use
=A2&" "&A
Click to expand...
 
Sorry for the bump, but I am in desperate need of solving this problem
:confused
 
Sorry for not getting back sooner, but I had to go out for
awhile...........as far as your formula goes, ............I tried your
formula and when I just took the spaces out from between "1 Hour" and made
it "1Hour", and of course changed the Sheet name also, everything worked
just fine.............

=IF('1Hour'!G22="","TBD",'1Hour'!G22-'1Hour'!G4)


hth
Vaya con Dios,
Chuck, CABGx3




martialtiger > said:
Thanks CLR,

It worked, but now I'm trying to actually take it a step further by
adding a more complex function.

=IF('1 Hour'!G22="","TBD",'1 Hour'!G22-'1 Hour'!G4)

Any ideas? A2 in this case is "1 Hour" and A5 is "G22". "G4" is just
another value.
=A2&A5 in cell B2 will give you whatever value is in cell A2
strung together
with whatever value is in cell A5.............if you want a space
between
them then use
=A2&" "&A5
Click to expand...
Click to expand...
 
Hey Chuck,

I tried your suggestion and it's giving me #VALUE! errors on the cells
Any other suggestions
 
I dunno.........I copied and pasted your formula right out of your post and
used it as written, then only removed the spaces as I described and it
worked fine.........since you re-posted, I've re-written the formula and
renamed the sheet back and forth several times and sometimes it works either
with the space or without, and sometimes not.........but once it works, it
seems to copy from cell to cell normally.......when it doesn't work, I get a
#REF! error, not the #VALUE! as you did..........about all I can suggest is
that you re-type the formula, and check the formatting of the cells and
rename the sheet again and see if it all syncs...........

Maybe one of your cells contains TEXT and that is what is causing the
#VALUE! error.

Vaya con Dios,
Chuck, CABGx3
 
Another thought..........are you really trying to SUBTRACT in the latter
part of the formula, or do you really mean to concatenate the two cells, as
thus,..........

=IF('1 Hour'!G22="","tbd",'1 Hour'!G22&'1 Hour'!G4)

Excel don't do subtraction on TEXT cells too good...........

With Joe in G22 and Smith in G4, the formula would return JoeSmith.


=IF('1 Hour'!G22="","tbd",'1 Hour'!G22&" "&'1 Hour'!G4) would return Joe
Smith, with the space between the names.

Vaya con Dios,
Chuck, CABGx3
 
CLR,

I truly appreciate your help! The formula > =IF('1 Hour'!G22="","tbd",'1 Hour'!G22&'1 Hour'!G4)

Represents this. 1 Hour is the worksheet name and G22 is the locatio
of the data. What i want to do is have a list that contains the name
of the worksheets and have a drop down list. Then the formula i
suppose to take the value (worksheet name) in the cell and substitut
it into the formula. Hope this clears it up a little more
 
Assuming your Validation Drop-down box was in A1, and contained sheetnames,
like Sheet1, Sheet2, Sheet3, etc.......then try this..........(with Joe in
G22 and Smith in G4)

=IF(A1&G22="","tbd",A1&G22&A1&G4)

......returns Sheet2JoeSheet2Smith

Vaya con Dios,
Chuck, CABGx3
 
I'm sorry Chuck,

I'm not reallly clear on what I need. In cell A1, I am only using th
Sheet Names to be placed into a formula, so that whichever name i
selected in the dropdown will be the sheet from which the value of th
cells (G4 & G22) are taken from. Let's say the formula i
'SheetName'!G22-'SheetName'!G4 in cell B1. When someone selects th
sheet name in A1, this value is then substitued for "SheetName" so tha
the values are selected from the corresponding worksheet. I don't eve
know if it's possible, but I am really grateful for your help.
 
OK...........I think I understand now.............I can't seem to get the
variable to work into the formula just as you have described, but by
cheating a bit, and using a VLOOKUP table and formula and things I think I
can show you how to accomplish what I think you're looking for.......and
that is if your dropdown box in A1 says "sheet1", then you want to subtract
G4 from G22 both on sheet 1, and if A1 says "sheet2" then you want to
subtract G4 from G22 on Sheet2, etc etc...............

I've prepared a sample workbook of three sheets to demonstrate...........if
you'd like to send me your email addy, I'll send you back a
copy...........it would take a while to try to explain it in text.........

Vaya con Dios,
Chuck, CABGx3


martialtiger > said:
I'm sorry Chuck,

I'm not reallly clear on what I need. In cell A1, I am only using the
Sheet Names to be placed into a formula, so that whichever name is
selected in the dropdown will be the sheet from which the value of the
cells (G4 & G22) are taken from. Let's say the formula is
'SheetName'!G22-'SheetName'!G4 in cell B1. When someone selects the
sheet name in A1, this value is then substitued for "SheetName" so that
the values are selected from the corresponding worksheet. I don't even
know if it's possible, but I am really grateful for your help.
Assuming your Validation Drop-down box was in A1, and contained
sheetnames,
like Sheet1, Sheet2, Sheet3, etc.......then try this..........(with
Joe in
G22 and Smith in G4)

=IF(A1&G22="","tbd",A1&G22&A1&G4)

......returns Sheet2JoeSheet2SmithQUOTE]
Click to expand...
Click to expand...
 
That would be great! My email is > (e-mail address removed) I will be overseas for two weeks, so forgive me if I do not respond
right away. I should be able to get online once in a while there.

In the meantime, have a great one and take care!

Jonas :)
 
"It's in the mail"...........let me know if you don't get it for some
reason.

Hope you have a wonderful time on your trip.

Vaya con Dios,
Chuck, CABGx3



martialtiger > said:
That would be great! My email is > (e-mail address removed) I will be
Click to expand...
overseas for two weeks, so forgive me if I do not respond
 
Back
Top