 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / March 2008Variable within a formula
Hello, I would like to set a formala in a cell that would vary based on the value of an another cell. The end result should look like: ='040208'!A1+C4 with 040208 being the value of the cell in question. How can I do that? Thank you. Eric Put 040208 in A1 and then: =INDIRECT(A1 & "!C4") will give you the same as putting: ='040208'!C4 in the same cell  SignatureGary''s Student - gsnu2007g > Hello, > [quoted text clipped - 8 lines] > > Eric Gary, almost there... I was looking for ='040208'!A1+C4 and not just '040208'!A1 I tried =INDIRECT(A1 & "!A1+C4") but it didn't work (A1 containing the value '040208'). Thank you. Eric > Put 040208 in A1 and then: > =INDIRECT(A1 & "!C4") [quoted text clipped - 14 lines] > > > > Eric G'day Eric I tried this =IF(A1="040208",(A1 & "!A1+C4"),0) It returned 040208!A1+C4 I had to change A1 to a Text for it to work though, as I got a Ref# error when it was General HTH Mark. Hi Mark, I am trying to end up with the formula =040208!A1+C4 not just with the string 040208!A1+C4 hence the use of INDIRECT. Thank you Eric > G'day Eric > [quoted text clipped - 11 lines] > HTH > Mark. Maybe: =INDIRECT("'" & A1 & "'!A1+C4") or =INDIRECT("'" & text(A1,"000000") & "'!A1+C4") or =INDIRECT("'" & text(A1,"mmddyy") & "'!A1+C4") The apostrophes are required for lots of worksheet names. And depending on what's in A1 (a number formatted to show leading 0's) or a date formatted nicely), you may need to do that in your formula, too. > Gary, almost there... I was looking for ='040208'!A1+C4 and not just > '040208'!A1 [quoted text clipped - 28 lines] > > > > > > Eric SignatureDave Peterson Nope, thank you for trying. A1 was already formatted as text and it still does not work. All your solutions return the same #REF! as mine. Thank you. Eric > Maybe: > =INDIRECT("'" & A1 & "'!A1+C4") [quoted text clipped - 40 lines] > > > > > > > > Eric What formula did you use? My real bet is that you don't have a worksheet by that name. Maybe there's something wrong with the value in the cell--or maybe there's something different with the worksheet tab. I'd look for leading/trailing spaces in both spots to start. > Nope, thank you for trying. A1 was already formatted as text and it still > does not work. [quoted text clipped - 58 lines] > > > > Dave Peterson SignatureDave Peterson Dave, I tried all the solutions and everytime I changed the type of the cell A1 (to match the type expected) and I do have a sheet 040208. I found something that worked. Instead of trying to incorporate the +C4 within the INDIRECT, I just took it outside... =INDIRECT("'" & A1 & "'!A1)+C4 instead of =INDIRECT("'" & A1 & "'!A1+C4") I wish I knew why the other one does not work because it does make sense... Thank you. Eric > What formula did you use? > [quoted text clipped - 66 lines] > > > > > > Dave Peterson I'm sorry. I copied and pasted and didn't notice the mistake in the original formula. I was too busy concentrating on the lack of apostrophes! The stuff after the exclamation point has to look like an address. And I can't think of anything that you could put into A1 that would make A1+C1 look like an address. > Dave, > [quoted text clipped - 92 lines] > > > > Dave Peterson SignatureDave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.