+ Reply to Thread
Results 1 to 20 of 20

Help with Indirect reference

  1. #1
    Registered User
    Join Date
    11-28-2012
    Location
    Dayton, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    15

    Question Help with Indirect reference

    Trying to wrap my head around the indirect cell reference but not getting anywhere.

    In the attached workbook I am trying to use a value in Sheet 1 Row C to reference a cell in Sheet 2 and assign that value to Sheet 1 Row D

    ie Sheet 1 column C8 is "X2" the get cell value from sheet 2 Cell X2 and place that value in Sheet 1 Cell E8. Value in E8 would then read "1-Oct-2012".

    I have googled and searched here but have not been able to get it right yet! LOL

    Training Stats Upgrade .xlsx

  2. #2
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Help with Indirect reference

    Try this in E3 and drag down :

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Hope this helps
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Help with Indirect reference

    hi Avenger291, it seems a little confusing. you said Sheet 1 column C8 is "X"2", but it's "X1". assuming it's "X2", then to read the value in Sheet 2 X2, try:
    =INDIRECT("Training!"&C8)
    format to the date format you need

    is that what you need? what you could do is show a bunch of desired results & tell us how you got it.

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

    Re: Help with Indirect reference

    Or

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    11-28-2012
    Location
    Dayton, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Help with Indirect reference

    @ dredwolfe

    Seems like that worked I will try it with larger data set.
    Is it possible to use this with an if statement?

    ie IF((C3<>"D*"),(=TEXT(INDIRECT("Training!"&C3),"d-mmm-yyyy")),(=Sum(D3+720)))

    soooo...if C3 is not equal to D1, D2, D3 etc perform the indirect lookup, if C3 does equal D1...etc then add 703 to date in D3.

    this formula doesn't work but I think it is because I am not performing wild card in String correctly: IF((C3<>"D*") where * is wild card ie 1,
    2, 3...etc

    Am I on right path???

  6. #6
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Help with Indirect reference

    what you are looking for, I think, is this:

    = IF(C3<>"D*",TEXT(INDIRECT("Training!"&C3),"d-mmm-yyyy")),Sum(D3+720))

    Hope that helps

  7. #7
    Registered User
    Join Date
    11-28-2012
    Location
    Dayton, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Help with Indirect reference

    @ Sixthsense,

    I feel like a real newb after reading that formula....not really sure what is going on in that one....LOL

    I get the error checking part but after that I start getting dazed and confused...must be time for a nap! ;-)

  8. #8
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Help with Indirect reference

    or maybe :
    =IF(LEFT(C3,1)="D",Sum(D3+720),TEXT(INDIRECT("Training!"&C3),"d-mmm-yyyy"))

  9. #9
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

    Re: Help with Indirect reference

    @ Avenger291,

    The method is something same when comparing the above posts but I just added a condition to ensure that the formula should automatically pick the sheet name in case you rename the sheet name from Training to someother new name.

    REPLACE(CELL("filename",Training!A1),1,FIND("]",CELL("filename",Training!A1))

    Note: The above formula will work with saved workbook only.

  10. #10
    Registered User
    Join Date
    11-28-2012
    Location
    Dayton, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Help with Indirect reference

    @ dredwolfe,

    I got Formula contains error message but found an extra ) and removed works now.

    I am not getting date I wanted though....see Sheet 1 Cells E5 and E6 of attached.

    Cells F5 and F6 are contain the expected outcomes.

    Training Stats Upgrade .xlsx

  11. #11
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Help with Indirect reference

    Sorry about the extra ")"...almost getting to be a habit

    Try this instead, as I'm not really sure the "*" wildcard works for a straightforward = comparison:

    =IF(LEFT(C3,1)="D",TEXT(SUM(D3+730),"d-mmm-yyyy"),TEXT(INDIRECT("Sheet2!"&C3),"d-mmm-yyyy"))

    this will also format the resulting sum to the date format, otherwise you would get a number instead

    Hope this helps

  12. #12
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Help with Indirect reference

    how about this in E3:
    =IF(LEFT(C3,1)<>"D",INDIRECT("Sheet2!"&C3),D3+730)

    i wouldnt recommend using TEXT because dates are numbers & are easier to deal with formulas when they remain as numbers.

    format cells as "d-mmm-yyy" instead
    Last edited by benishiryo; 12-12-2012 at 01:42 AM. Reason: grammer

  13. #13
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Help with Indirect reference

    or get rid of the sum altogether :

    =IF(LEFT(C3,1)="D",TEXT(D3+730,"d-mmm-yyyy"),TEXT(INDIRECT("Sheet2!"&C3),"d-mmm-yyyy"))

    thought of it after I hit submit on last post

  14. #14
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Help with Indirect reference

    I tested benishiryo's concern in a blank cell using =1*E3 and the proper number is still returned, so in THIS case it seems not to be an issue, however, it is still a valid point, and something to be aware of , thanks for pointing it out benishiryo

  15. #15
    Registered User
    Join Date
    11-28-2012
    Location
    Dayton, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Help with Indirect reference

    @ dredwolf

    I noticed the number output versus the date with previous formula.
    I just applied Date formatting to the column and voila! displayed as dates.

    @ benishiryo
    Thanks for the input I might use this to keep dates as numbers versus text as you mentioned.

    I am really new to advanced excel manipulations but trying to learn something new every day...LOL

    Thanks for all the input, I am calling it a night and will apply what I learned here tonight and see if I can call this solved.

  16. #16
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Help with Indirect reference

    @dredwolf: i might be wrong since the all kinds of dates formula seem to work fine in my Excel 2007/2010 like DAY, MONTH, YEAR, etc. not sure if it works in older versions, but it is just bugging to know that it's a text haha. using 1*E3 is like using VALUE(E3). it will work if it's supposed to be a number. for eg. if you have numbers 1, 2 & 3 in A1:A3 as text. summing them up wont work. but if you multiply them by 1 in column B & sum them up, it'll work. so it's this kind of scenarios that makes me a little edgy

  17. #17
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Help with Indirect reference

    @benishiryo
    I used several formulas...
    -e1,e1+0,e1/1,etc to test, all returned with the proper number, then tried on the other rows, and all returned proper numbers for the formula, so, in this particular case, it seems to be okay..
    Doesn't mean it always is though, so was a perfectly valid point to bring up

    Edit-
    also seems that if you sum the dates in E column WITHOUT another mathematical operation, it will return as a date..not a number, so this could cause confusion for newer user's, again pointing to the validity of your point !

    Edit 2

    Also, Excel seems to sometimes switch the formatting for a cell when dealing with dates, causing further confusion...lol
    Last edited by dredwolf; 12-12-2012 at 02:29 AM.

  18. #18
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Help with Indirect reference

    After further testing, benishiryo proves right, as individual cell references used in a formula, they seem to work, but as soon as you use them as a range, the results are completely unreliable for formulas unless you use a "*1,--", or something similar on each cell, so if you plan to use the NUMBER values of the dates in other formulas, you are definitely better avoiding the Text(...) call..use to formatting to get the display you need...

    @ benishiryo
    All my apologies for arguing with you, I am very sorry

  19. #19
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Help with Indirect reference

    getting a little bit too serious, dredwolf! haha. we are just sharing the the knowledge we have with each other. as i mentioned, no apologies needed for that.

  20. #20
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Help with Indirect reference

    all good, I stated my opinion in the thread, and will admit my mistake here as well
    but thanks for saying so

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1